Menu
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?
666
The join order is the order in which the tables are joined together in a multi-table SQL statement. Ideally, a plan should start with the join that eliminates the most data to minimize the amount of data carried into the subsequent joins.
The order doesn't matter for INNER joins. As long as you change your selects from SELECT * to SELECT a. *, b. *, c.
The order of the conditions in the ON clause doesn't matter.
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
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
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
