CODEWITHSUNDEEP
sql
george9170
george9170

Reputation:

left join turns into inner join

SELECT
a.foo
b.bar
c.foobar
FROM tableOne AS a
INNER JOIN tableTwo AS b ON a.pk = b.fk
LEFT JOIN tableThree AS c ON b.pk = c.fk
WHERE a.foo = 'something'
AND c.foobar = 'somethingelse'

Having the and clause after the where clause seems to turn the left join into an inner join. The behavior i am seeing is if there isnt 'somethingelse' in tableThree there will be 0 rows returned.

If i move c.foobar = 'somethingelse' into the join clause the stored join will act like a left join. 

    SELECT
    a.foo
    b.bar
    c.foobar
    FROM tableOne AS a
    INNER JOIN tableTwo AS b ON a.pk = b.fk
    LEFT JOIN tableThree AS c ON b.pk = c.fk
    AND c.foobar = 'somethingelse'
    WHERE a.foo = 'something'

Can someone point me at some documentation describing why this happens? THank you very much

Upvotes: 37

Views: 34443

Answers (8)

Dave Markle
Dave Markle

Reputation: 97691

It's because of your WHERE clause.

Whenever you specify a value from the right side of a left join in a WHERE clause (which is NOT NULL), you necessarily eliminate all of the NULL values and it essentially becomes an INNER JOIN.

If you write, AND (c.foobar = 'somethingelse' OR c.foobar IS NULL) that will solve the problem.

You can also move the c.foobar portion into your join predicate, and that too will solve the issue.

Upvotes: 82

Si Downes
Si Downes

Reputation: 51

It doesn't turn a LEFT JOIN into an INNER JOIN, thought the effect may appear to be the same. When you set the WHERE condition

AND c.foobar = 'somethingelse'

you're getting rid of all the cases that allow a LEFT JOIN TO act as it does. In this case, some of the values for c.foobar will be NULL. Setting this on the JOIN condition still allows non-matching LEFT JOIN results, only restricting what is returned for C results.

Upvotes: 0

BeachBlocker
BeachBlocker

Reputation: 246

The 'where' clause is performed after the join. This doesn't matter for inner joins but matters for outer joins.

Shorten Example

SELECT b.bar,c.foobar FROM tableTwo AS b LEFT JOIN tableThree AS c ON b.pk=c.fk WHERE c.foobar='somethingelse'

Raw data                  Outer Join Result        Select Result
b.pk c.fk c.foorbar       b.pk c.fk c.foorbar      c.foorbar
1    1    something       1    1    something      <not in result set>
1    1    somethingelse   1    1    somethingelse  somethingelse


SELECT b.bar,c.foobar FROM tableTwo AS b LEFT JOIN tableThree AS c ON b.pk=c.fk AND c.foobar='somethingelse'

Raw data                  Outer Join Result        Select Result
b.pk c.fk c.foorbar       b.pk c.fk c.foorbar      c.foorbar
1    1    something       1    null null           null
1    1    somethingelse   1    1    somethingelse  somethingelse

Upvotes: 5

mdma
mdma

Reputation: 57707

The LEFT JOIN produces NULLs where there are no matching rows. In this case, c.foobar will be NULL for the non-matching rows. But your WHERE clause is looking for a specific value: 'somethingelse', and so will filter out all the NULL values. Since an INNER JOIN also produces no NULL values on the right side, the two look the same. You can add ' OR c.foobar IS NULL' to allow the null values back in.

When you move the condition to the ON clause, it becomes part of the JOIN row matching, rather than the final filter. The join match may fail, and the outer join then returns NULLs on cases where 'c.foobar' is NULL or not 'somethingelse'.

See

Upvotes: 1

Gabriele Petrioli
Gabriele Petrioli

Reputation: 195992

1st case (where the c.foobar is in the where clause) the filtering by the c.foobar happens after the joins have occured (they occur correctly), so you effectively filter-out all resulting records that do not have a 'somethingelse' in there..

2nd case the c.foobar is part of the join, so it evaluates at joining time and just controls the output of the join, and not the final recordset (returnsnull where the join fails)..

Upvotes: 0

Thomas
Thomas

Reputation: 64645

A Left Join returns everything from the left table (tableTwo in your example) and any matching rows from the table on the right (tableThree in your example). When you filter on something on the right side of the left join (i.e. tableThree) and you do not account for non-matching values you are effectively requiring that a value exist and that the value be 'something' which is the equivalent of an inner join. If what you are trying to do is to find all tableTwo rows which do not have a row in tableThree with a foobar value of 'something', you can move the filtering into the on clause:

Select a.foo, b.bar, c.foobar
From tableOne As a
    Inner Join tableTwo as b
        On b.fk = a.pk
    Left Join tableThree as c
        On c.fk = b.pk
            And c.foobar = 'something'
Where a.foo = 'something'
    And c.pk Is Null

The final addition, c.pk Is Null filters for values that do not have a tableThree value with a foobar value of 'something'.If just want to see tableThree values when they have a foobar value of 'something' (and nulls otherwise), then remove the additional filter I added of c.pk Is Null.

Upvotes: 0

DaveWilliamson
DaveWilliamson

Reputation: 370

The joins are doing their work, then the where is removing the records where c.foobar <> 'somethingelse'.

The effect looks like an inner join but actually isn't.

Upvotes: 0

Martin B
Martin B

Reputation: 24140

The reason you're seeing this is because the left join sets all columns of c to NULL for those rows that don't exist in c (i.e. that can't be joined). This implies that the comparison c.foobar = 'somethingelse' is not true, which is why those rows are not being returned.

In the case where you move the c.foobar = 'somethingelse' into the join condition, that join is still returning those rows (albeit with NULL values) when the condition is not true.

Upvotes: 6

Related Questions