When to use a JOIN for MySQL (SQL)

Question

I cannot effectively understand the difference between these two queries.

select *
from person p, mytlc m
where p.personid = m.personid and p.personid = 3635

select *
from person p
    join mytlc m on p.personid = m.personid
where p.personid=3635

In this case, I don't think either will be a greater performing query;but, what if the query was more complex handling much more data.

Thanks!

Answer 1

If you want to find more information on this topic, you can try googling 'join vs where'. Here are a couple of other questions that address the same thing:

1. Inner join vs Where
2. MySQL: Inner join vs Where
3. http://forums.devx.com/showthread.php?t=19242

A quote from the third one is interesting (regarding SQL Server, but they are probably similar in behavior):

If you measure this, you will most likely discover that the two versions use the exact same access plan. SQL Server tries very hard to optimize a query, and in that process, a where clause which equates columns from two tables will be converted to an inner join.

These seems to indicate that technically the join is correct and more efficient than a where, but it doesn't matter in practice because optimization will likely correct the where into a join. However, for cases where it won't optimize, it is better to be explicit, and as indicated by others, join is the right way to do it.

Answer 2

They are just two different ways of saying the same query. The former is the "old" way of doing joins and is (in my experience) less desirable. The latter is the "new" way of doing joins and is more explicit.

They are functionally equivalent.