How does (My)SQL evaluate chained LEFT/INNER JOINs?
inner-join, join, left-join, mysql, sql
Solution
Left join is something like "OR" and INNER JOIN something like "AND". So there is precdence like this
t1 left join (t2 inner join t3)
This is significiant different with
(t1 left join t2) inner join t2
Problem
We have 3 Tables given: ``` T1: T2: T3: id | t3_id id | name id | t2_id ---------- --------- ---------- 1 | 2 1 | abc 1 | 1 2 | NULL 2 | 123 2 | 1 3 | 1 3 | ab1 3 | 3 4 | 4 4 | 32b 4 | 2 ``` Now we had the following JOINs tested: 1.: ``` SELECT * FROM T1 INNER JOIN T3 ON T1.t3_id = T3.id INNER JOIN T2 ON T3.t2_id = T2.id WHERE T2.name = '%' ``` This case is clear. Results: ``` T1.id | T1.t3_id | T3.id | T3.t2_id | T2.id | T2.name ----------------------------------------------------- 1 | 2 | 2 | 1 | 1 | abc 3 | 1 | 1 | 1 | 1 | abc 4 | 4 | 4 | 2 | 2 | 123 ``` 2.: ``` SELECT * FROM T1 LEFT JOIN T3 ON T1.t3_id = T3.id LEFT JOIN T2 ON T3.t2_id = T2.id WHERE T2.name = '%' ``` This one is also clear: ``` T1.id | T1.t3_id | T3.id | T3.t2_id | T2.id | T2.name ----------------------------------------------------- 1 | 2 | 2 | 1 | 1 | abc 2 | NULL | NULL | NULL | NULL | NULL 3 | 1 | 1 | 1 | 1 | abc 4 | 4 | 4 | 2 | 2 | 123 ``` 3.: ``` SELECT * FROM T1 LEFT JOIN T3 ON T1.t3_id = T3.id INNER JOIN T2 ON T3.t2_id = T2.id WHERE T2.name = '%' ``` This one is a bit strange. Result (same as the first one): ``` T1.id | T1.t3_id | T3.id | T3.t2_id | T2.id | T2.name ----------------------------------------------------- 1 | 2 | 2 | 1 | 1 | abc 3 | 1 | 1 | 1 | 1 | abc 4 | 4 | 4 | 2 | 2 | 123 ``` I don't understand how MySQL evaluates this expression. Why it does ignore the LEFT JOIN and seems to prefer the INNER JOIN. If I read this query it's like: - Take data from T1 - Take data from T2 via LEFT JOIN (means: only if possible, else take NULL data) - Take data from T3 via INNER JOIN (means: remove all T2 data that cannot be joined via this INNER JOIN) But it seem's like I have to read the query backwards?! Could someone please explain this scenario to me?