MySQL Join clause vs WHERE clause

Question

What's the difference in a clause done the two following ways?

SELECT * FROM table1 INNER JOIN table2 ON (     table2.col1 = table1.col2 AND     table2.member_id = 4 ) 

I've compared them both with basic queries and EXPLAIN EXTENDED and don't see a difference. I'm wondering if someone here has discovered a difference in a more complex/processing intensive envornment.

SELECT * FROM table1 INNER JOIN table2 ON (     table2.col1 = table1.col2 ) WHERE table2.member_id = 4 

Answer 1

With an INNER join the two approaches give identical results and should produce the same query plan.

However there is a semantic difference between a JOIN (which describes a relationship between two tables) and a WHERE clause (which removes rows from the result set). This semantic difference should tell you which one to use. While it makes no difference to the result or to the performance, choosing the right syntax will help other readers of your code understand it more quickly.

Note that there can be a difference if you use an outer join instead of an inner join. For example, if you change INNER to LEFT and the join condition fails you would still get a row if you used the first method but it would be filtered away if you used the second method (because NULL is not equal to 4).