MySQL Performance: JOIN ON vs WHERE [duplicate]

Question

Possible Duplicates:
MySQL: Inner join vs Where
Explicit vs implicit SQL joins

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 <field names> FROM <table1> JOIN <table2> ON <join_condition> AND JOIN <table3> ON <join_condition>  AND JOIN <table4> ON <join_condition>  ....  

Using WHERE

SELECT <field names>  FROM <table names list separated by comma>  WHERE <join_condition>  AND <join_condition>  AND <join_condition>  ....  

So not sure whether usage of JOIN ON or WHERE clause would make a difference. Please assist.

Answer 1

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.

Answer 2

I suspect that using "JOIN" is doing a "LEFT JOIN". Try using an "INNER JOIN" and see if the explain statement is the same.

SELECT <field names> FROM <table1> INNER JOIN <table2> ON <join_condition> INNER JOIN <table3> ON <join_condition>  INNER JOIN <table4> ON <join_condition>  ... 

When using an inner join, I would not expect there to be a performance difference between your two queries. I would worry more about having the proper indexes. Make sure the join conditions can make a match using an indexed column.