When to use a JOIN for MySQL (SQL)

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, wh开发者_如何学JAVAat if the query was more complex handling much more data.

Thanks!

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.

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.

The first is more likely to have an error if you have many joins (the accidental cross join). The first is also likely to create problems when you need to change to an outer join as combining implicit and explicit joins can definintely create incorrect results. Therefore the first is harder to maintain over time. The first is also unclear when you intended a cross join or if it was accidental. This is a real problem in maintenance as you might fix a "bug" that was intentional or think something was intentional that was a genuine bug. I have also found that many people (not everyone) who use the first form don't correctly understand joins and thus often do not understand if they have gotten the correct resultset.

There is literally no reason whatsoever to ever use the first form (there are no gains in using this form and several minuses). I would rewrite it anytime I saw it and never write new code using it.

There is another way too:

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

The first approach and the above approach will have same performance just an old and new way of writing queries.Writing joins with ON is an ANSI style. Thats it...

Now you can join extra conditions either to where clause or to the same ON clause after the join condition.Here comes performance into picture and depending upon your query results, the decision is made.