Join Order

Posted by Paul Anderton on April 11, 2013
Posted in: Joins, SQL Development. Tagged: , , , , , , .

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:-

JO1

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:-

JO2

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:-

JO3

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:-

JO4

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:-

JO5

Does the order of different types of joins matter?

Yes

Outer Joins – Where v’s On

Posted by Paul Anderton on March 13, 2013
Posted in: Joins, SQL Development. Tagged: , , , , .

Does filter placement matter in Outer Joins?

Left or Right Outer Joins by definition return all rows from either the left or the right table/view and any matching rows from the opposite table/view and NULL’s for any rows that don’t match.

For example a Left Join (Left Outer Join) would contain every row from the left table and any matching rows from the right table and a NULL for all none matching rows.

The example below creates 2 temporary tables and populates with some test data:-

CREATE TABLE #tmp1 ( id INT )
CREATE TABLE #tmp2 ( id INT )

INSERT  INTO #tmp1
VALUES (1),(2),(3),(4),(5)
INSERT  INTO #tmp2
VALUES (1),(2)

If we join these 2 tables with a Left Join

SELECT  tmp1.id AS LeftID ,
        tmp2.id AS RightID
FROM    #tmp1 tmp1
        LEFT JOIN #tmp2 tmp2 
		ON tmp1.id = tmp2.id

We get our expected result set of returning every row from the left table and a matching row from the right table with NULL’s for all none matching rows.

LJ1

If we introduce a Where clause we can reduce the rows returned.

SELECT  tmp1.id AS LeftID ,
        tmp2.id AS RightID
FROM    #tmp1 tmp1
        LEFT JOIN #tmp2 tmp2 
		ON tmp1.id = tmp2.id
WHERE   tmp1.id IN ( 2, 3 )

We get our expected result set, ie the result above is filtered down to only return id 2 or 3 from the left table and any corresponding rows from the join.

LJ2

What happens if we move the Where clause in to the joins On clause

SELECT  tmp1.id AS LeftID ,
        tmp2.id AS RightID
FROM    #tmp1 tmp1
        LEFT JOIN #tmp2 tmp2 
		ON tmp1.id = tmp2.id
			AND tmp1.id IN ( 2, 3 )

We get this result set

LJ3

Hmm that’s not what I was expecting! Did you expect that?

The order the query engine processes the join is the important factor here, in all joins there is either a 1, 2 or 3 step process, in this instance (Outer Join) there is a 3 step process, step 1 creates a Cartesian result from the 2 tables (think Cross Join) , step 2 applies the On filter (think Inner Join) then step 3 adds all records that don’t match from either the left or right tables (Outer Join), after all this then the Where clause is applied.

In the first example the left join creates a result set (including NULLs for none matching rows) then applies the filter in the Where clause that reduces the result set that is output.

The second example the filter is applied in the join (step 2), so the join is only matching records 2 or 3 then every none matching record from the left table is added to the result set with a NULL from the opposite table.

Do you know what your left joins are doing?

Incidentally this doesn’t happen with Inner Joins as step 3 is not applied.