Does the order of different types of joins matter?
Let’s see:-
The example below creates 3 temporary tables and populates with some test data:-
CREATE TABLE #Table1 ( id INT )
CREATE TABLE #Table2 ( id INT )
CREATE TABLE #Table3 ( id INT )
INSERT INTO #Table1
VALUES (1),(2),(3),(4),(5)
INSERT INTO #Table2
VALUES (1),(2),(3)
INSERT INTO #Table3
VALUES (1),(2)
If we join Table 1 and Table 2 with a left join:-
SELECT Table1.id AS Table1
,Table2.id AS Table2
FROM #Table1 Table1
LEFT JOIN #Table2 Table2
ON Table1.id = Table2.id
We get the expected result set below:-
If we then add table 3 as an extra join, this time an inner join:-
SELECT Table1.id AS Table1
,Table2.id AS Table2
,Table3.id AS Table3
FROM #Table1 Table1
LEFT JOIN #Table2 Table2
ON Table1.id = Table2.id
INNER JOIN #Table3 Table3
ON Table2.id = Table3.id
We get the following results:-
I’m guessing you were expecting some of the Table 1 ID’s to match to NULL’s because of the left join weren’t you?
What happens is the result of the Table 1/ Table 2 left join becomes the left input of the inner join therefore all rows returned have to match the rows in table 3.
This is something I do see quite a lot of.
How can we get the results we want?
We could change the second inner join to a left join:-
SELECT Table1.id AS Table1
,Table2.id AS Table2
,Table3.id AS Table3
FROM #Table1 Table1
LEFT JOIN #Table2 Table2
ON Table1.id = Table2.id
LEFT JOIN #Table3 Table3
ON Table2.id = Table3.id
Now we return all records from Table 1, all records from Table 2 and all records from Table 3:-
Now what if you were using the inner join to filter out ID 3 from Table 2, this isn’t going to give you the result you’re after.
We could change the order of the joins and use a right join instead of a left join:-
SELECT Table1.id AS Table1
,Table2.id AS Table2
,Table3.id AS Table3
FROM #Table2 Table2
INNER JOIN #Table3 Table3
ON Table2.id = Table3.id
RIGHT JOIN #Table1 Table1
ON Table3.id = Table1.id
This gives us every row from Table 1 and allows us to inner join Table 2 and Table 3:-
Writing the query this way might make it more difficult to read.
However there is another way to get the same result:-
SELECT Table1.id AS Table1
,Table2.id AS Table2
,Table3.id AS Table3
FROM #Table1 Table1
LEFT JOIN (
#Table2 Table2
INNER JOIN #Table3 Table3
ON Table2.id = Table3.id
)
ON Table1.id = Table2.id
By moving the ON clause for the left join to the end, the inner join is evaluated first, then the left join is applied, the parentheses can be added for readability. This returns the following results:-
Does the order of different types of joins matter?
Yes
