Possible Duplicate:
INNER JOIN ON vs WHERE clause
I have the following two codes:
SELECT * FROM people,people_emails,members
WHERE people_emails_person=people_id
AND members_person=people_id
SELECT * FROM people
INNER JOIN people_emails ON people_id = people_emails_person
INNER JOIN members ON people_id = members_person
They both produce the same result, but which is the correct/better query to use?
As it stands these two queries are exactly the same. The first uses the "old style" of joins.
It is very much "out of style" to use the first style. I think that style is not as clear which becomes clear when that style is used to express outer and right joins.
Some people have always done it in the first style and I'm sure they would say such a style is clearer.
The second is the better of the two, but both leave room for improvement. By selecting * you are automatically selecting more fields than you need. In your case, since people_emails_person, people.people_id, and members_person.people_id will always have the same value, you only need one of them.
Back to your question, the reason the second one is better is that it will probably run faster.
They are both technically equivalent, but if you're joining more than a couple tables (especially if they are large tables) and you're experiencing the query taking a long time to finish, it sometimes helps to control the plan order, which can be done with explicit joins (the second one, using INNER JOINs) and some tweaking of settings.
If you're using PostgreSQL, for example, you can set join_collapse_limit=1 to ensure that the query joins in the exact order you specify. This will only work when using explicit joins. Sometimes the plan does some weird, unexpected things that makes the query very slow, but if you know that your way would be faster and want to tell the planner to do it your way, you could specify that. Read this article about explicit joins in postgres for more information. Of course, this varies depending on what SQL you are using.
Are both correct, but the first query should be better because the WHERE syntax is more relational model oriented. Anyway, the implicit JOIN ANSI syntax is older, less obvious and not recommended.
