SQL FORM multiple tables - 'left join' vs ','

Question

what is better? and why?

function a:

DELETE a FROM a LEFT JOIN b 
ON a.CHROM=b.CHROM AND a.POS=b.POS

function b:

DELETE a FROM a, b 
WHERE a.CHROM=b.CHROM AND a.POS=b.POS

First learn what joins do http://stackoverflow.com/questions/448023/what-is-the-difference-between-left-right-outer-and-inner-joins/448080#448080 obviously left join better than cross join — Shaiful Islam, Jan 04 '15 at 17:52
Really depends on what you are trying to acheive. See:http://www.w3schools.com/sql/sql_join.asp for left and full join — Ori Price, Jan 04 '15 at 17:53
possible duplicate of [Explicit vs implicit SQL joins](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) — Zanon, Jan 04 '15 at 17:53
The explicit `LEFT JOIN` provides readability oppose to the latter approach. It is also clearer as to what conditions the tables are being joined on. Also, consistency is very important as mixing up explicit `JOIN` statements with the `,` approach can lead to undesired behaviors. — Anthony Forloney, Jan 04 '15 at 17:53
Left join is not the same of a cartesian product. You can get different results if `table_a left join table_b` if there are results in A without a match in B. — Zanon, Jan 04 '15 at 17:56

score 1 · Accepted Answer · edited May 23 '17 at 12:28

It is better to join in the FROM clause because it gets executed first. There are cases where you should join in the where but not here. So I would say function a is your best bet. On a side note, I don't think you want to do a left join in function a because you will be joining more records than you will be deleting. It may be better and more efficient to use INNER JOIN in function a. A good practice is to select before you delete or update. If you run these selects do you get the same results? You should get more results in the first query because you are using left join and the second query is similar to an inner join.

SELECT a FROM a LEFT JOIN b 
ON a.CHROM=b.CHROM AND a.POS=b.POS 

SELECT a FROM a, b 
WHERE a.CHROM=b.CHROM AND a.POS=b.POS

check out this answer:

INNER JOIN ON vs WHERE clause

If need a better explanation on joins this was really helpful to me:

SQL JOIN and different types of JOINs

SQL FORM multiple tables - 'left join' vs ','

1 Answers1