Join statement order of operation
join, left-join, sql, sql-server, t-sql
Solution
The placement of the `ON` clauses controls the logical order of evaluation.
So first the `t1 LEFT JOIN t2 ON t1.fk = t2.pk` happens. The result of this join is a virtual table containing all the matching rows from `t1, t2` and (because it is a left outer join) any non matched `t1` rows are also preserved with null values for the `t2` columns.
This virtual table then participates in the next join. `JOIN t3 ON t2.fk = t3.pk`
Any `t2` records that do not match rows in `t1` are not part of the virtual table output from the first stage so won't appear in the final result. Additionally this inner join on `t2.fk = t3.pk` will lose any `NULL` values of `t2.fk` effectively turning your whole thing back into inner joins.
Logical Query Processing is explained well by Itzik Ben Gan here
Problem
Given the following 3 way join ``` select t1.* from t1 left join t2 on t1.fk = t2.pk join t3 on t2.fk = t3.pk ``` If the join between t2 and t3 failed, would the row from the successful join between t1 and t2 be returned? If the order of operation goes from left to right, I assume not, but if it's evaluated from right to left (t3 is joined to t2 first) then t1 will still be returned even when the former failed. How does it work?