Join statement order of operation

Question

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?

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

Comments

[Alwyn]

Answers to the point and is very comprehensive in the explanation. Thank you for the help.

[Muhammad Babar]

Nice and easy explanation i'm wondering does the same order of operation applies to sqlite?

[Martin Smith]

@MuhammadBabar - I haven't used SQLLite but if it adheres to standard (ANSI/ISO) SQL it will.

[Muhammad Babar]

Hoping it adheres. Ok so what will happen if i modify the above query as select t1.* from t1 inner join t2 on t1.fk = t2.pk left join t3 on **t1.fk** = t3.pk

[Martin Smith]

@MuhammadBabar The result of the inner join on t1 and t2 forms a virtual table. Call this vt1. Then that virtual table is joined onto t3. Any rows in the virtual table vt1 that didn't match anything in t3 are preserved in the result with NULL for the t3 columns.

[Muhammad Babar]

Martin did you noticed the left join i have changed t2.fk = t3.pk to t1.fk = t3.pk!

[Martin Smith]

@MuhammadBabar - Yes. The virtual table vt1 contains all columns from both t1 and t2.

[Muhammad Babar]

So what i understand that the if we have lets say 2 joins. The second join will be applied to the virtual table being obtained from first join? Also t2.fk = t3.pk to t1.fk = t3.pk does both are equal?

[Martin Smith]

@MuhammadBabar - Yes to the first part. The second join will be applied to the virtual table being obtained from first join. No to the second part as there is nothing that guarantees that the values of t2.fk and t1.fk are the same so the query may return different results but this doesn't change the logical description.

[Muhammad Babar]

Actually what i mean to ask is that whether we compare the base table column or the virtual table column in second left join the results will be same? Assuming as in example comparing only pk's and fk's.