Is there any difference performance wise when we run join queries using "JOIN ON" and "WHERE" clause? (regardless of the number of tables or the number of entries in the tables)
Not sure whether this topic has already been discussed over. Even if so, I wish to know whether with the latest versions of mySQL(5.1 and above), things have changed.
An Explain statement clearly shows a lot of difference in the number of rows taken for consideration.
The Syntax I am using is:
Using JOIN ON
SELECT
FROM
JOIN ON
AND JOIN ON
AND JOIN ON
....
Using WHERE
SELECT
FROM
WHERE
AND
AND
....
So not sure whether usage of JOIN ON or WHERE clause would make a difference. Please assist.
解决方案
Here is a good analysis of these two approaches: SQL left join vs multiple tables on FROM line?
This explanation is general, I'm not too sure what MySQL does in this matter; but either way the point is that a JOIN is always more explicit and clear and can be moved from one engine to another with no major changes in the logic of the query.