Join statement order of operation

sql sql-server tsql join left-join
Alwyn picture Alwyn · Sep 29, 2012 · Viewed 15.6k times · Source

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?

Answer

Martin Smith picture Martin Smith · Sep 29, 2012

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

Related questions

SQL Left Join first match only

I have a query against a large number of big tables (rows and columns) with a number of joins, however one of tables has some duplicate rows of data causing issues for my query. Since this is a read only …

Read More
T-SQL Subquery Max(Date) and Joins

I'm trying to join multiple tables, but one of the tables has multiple records for a partid with different dates. I want to get the record with the most recent date. Here are some example tables: Table: MyParts Partid Partnumber …

Read More
SQL join against date ranges?

Consider two tables: Transactions, with amounts in a foreign currency: Date Amount ========= ======= 1/2/2009 1500 2/4/2009 2300 3/15/2009 300 4/17/2009 2200 etc. ExchangeRates, with the value of the primary currency (let's say dollars) in the foreign currency: Date Rate ========= ======= 2/1/2009 40.1 3/1/2009 41.0 4/1/2009 38.5 5/1/2009 42.7 etc. Exchange rates can be entered for arbitrary dates - …

Read More