when to use join or simple 2 table condition?

Question

Possible Duplicate:
SQL left join vs multiple tables on FROM line?

I think this is much clearer:

"SELECT * FROM t1,t2 WHERE t2.foreignID = t1.id "

than a query with a JOIN.

are there any specs on when to use one or another?

Thanks

With only exception that it is `LEFT JOIN` discussion there. There is a difference between list-of-tables-separated-by-comma and `left join`, but no differences with `inner join` — zerkms, Apr 12 '11 at 01:14

score 2 · Answer 1 · edited Feb 13 '16 at 10:46

Personally, I prefer the explicitness of

SELECT t1.*, t2.*
FROM t1 
JOIN t2 ON t1.id = t2.foreignID

I like to see exactly what my JOIN conditions are, and the I use WHERE to further filter the results (current year, only a certain user, etc). It shouldn't matter in a simple query like this, but will definitely help with longer, more complex queries.

It doesn't make sense to me to have one style for shorter queries and a different for longer ones. Usually the simple ones turn into complex queries soon enough.

score 0 · Answer 2 · answered Apr 12 '11 at 02:15

This is a cartesian join which is actually very, very slightly different from an inner join. More than 99.99% of the time, you will get identical results. But there are edge cases that will run much slower, or possibly give extra rows. But you are more likely to run into parsing problems when you add left joins. The MySQL documentation gives a little bit of explanation, but not much. The differences lie in how the query optimiser chooses how to scan the tables.

This format also gets messy when you start adding other conditions in your WHERE clause.

when to use join or simple 2 table condition?

2 Answers2