Mysql Puzzle : Conditions in ON vs. WHERE

The following two queries do not return the same result. Why ?

Note : I found this question as a Mysql puzzle, I don't have more data on this question?

SELECT table1.*
FROM table1 LEFT JOIN table2
ON tabl开发者_运维问答e2.table1_id = table1.id 
AND table2.val < 5


SELECT table1.*
FROM table1 LEFT JOIN table2
ON table2.table1_id = table1.id 
WHERE table2.val < 5

The left outer join will return rows (with null columns from table2 if they were selected) when the condition isn't met, whereas the WHERE filters them out.

The first query will return all rows from table 1. A LEFT JOIN always returns all rows from the left table regardless of what you write in the join condition (it can however duplicate rows if there are multiple matches, but since you are joining on a field called id, it is most likely a unique key, so there will be no duplicates).

The second query will only return those rows from table 1 where the corresponding row in table2 has val < 5. You could in fact have written INNER JOIN here instead of LEFT JOIN because the rows where the join fails will not be in the result set anyway due to the WHERE clause.

First query only joins if table2.val < 5. Second filters even table1 rows. Identical result should be given if you use INNER JOIN instead.

Trying to think as a "compiler" look the parenthesis...

SELECT     table1.*
FROM       table1 
LEFT JOIN  table2 ON (
             table2.table1_id = table1.id 
             AND table2.val < 5
)

This example does the LEFT JOIN of 2 tables when the condition table2.table1_id = table1.id AND table2.val < 5 is true.

SELECT     table1.*
FROM       table1
LEFT JOIN  table2 ON ( table2.table1_id = table1.id )
WHERE      (table2.val < 5)

This example do the LEFT JOIN when the condition table2.table1_id = table1.idapplies and then get the rows of the result of table1 LEFT JOIN table2 ON ( table2.table1_id = table1.id ) WHERE the condition table2.val < 5 is true