when to use join or simple 2 table condition? [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

SQL left join vs mult开发者_运维百科iple 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

---

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.

---

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.