SQL Server : query with multiple LEFT OUTER JOIN and WHERE conditions

Question

I run SQL Server 2008 and I'm trying to execute following SQL query:

Query 1:

SELECT *
FROM tableA
LEFT OUTER JOIN tableB AS tabX ON tabX.some_id = tableA.some_id
LEFT OUTER JOIN tableB AS tabY ON tabY.some_id = tableA.some_id
WHERE tabX.some_attribute = 'X'
AND tabY.some_attribute = 'Y'

I already know that conditions after WHERE statement do mess up LEFT OUTER JOIN and generally make it behave like a classic INNER JOIN. What's interesting, it's not the case on SQL Server 2005.

To fix it I can do this:

Query 2:

SELECT *
FROM tableA
LEFT OUTER JOIN tableB AS tabX ON tabX.some_id = tableA.some_id AND tabX.some_attribute = 'X'
LEFT OUTER JOIN tableB AS tabY ON tabY.some_id = tableA.some_id AND tabY.some_attribute = 'Y'

Basically I have to include WHERE conditions within ON statements and the query will execute the way it was intended.

My first question is: Why doesn't SQL Server interpret both queries same way (as older SQL Server versions or Oracle DBServer do)?

I ask because I'm confused about how and why conditions (after WHERE statement) from first query affect the main logical repertory (by that I mean "main results"). Especially as both conditions specifically refer to aliases tabX and tabY

My second question is: Can I somehow change this behaviour? (e.g. in Server Configuration ?)

Best regards, Piotr

Answer 1

Left Outer Join - ON clause vs WHERE clause

For a left outer join, for the same condition, there is a big difference depending on where it is placed - the join clause or the where clause.

Using MS SQL-Server:

DECLARE @t1 TABLE ( id INT )
INSERT INTO @t1 VALUES ( 1 ),( 2 ),( 3 ),( 4 ),( 5 );

DECLARE @t2 TABLE ( id INT )
INSERT INTO @t2 VALUES ( 2 ),( 3 ),( 10 ),( 11 ),( 12 );

SELECT * FROM @t1 t1
LEFT OUTER JOIN @t2 t2 ON t2.id = t1.id

This gives:

1   NULL
2   2
3   3
4   NULL
5   NULL

As expected, all rows from driving table with NULLs for no match from slave table.

Condition in ON clause

For the condition in the ON clause:

SELECT * FROM @t1 t1
LEFT OUTER JOIN @t2 t2 ON t2.id = t1.id AND t1.id = 2

This gives:

1   NULL
2   2
3   NULL
4   NULL
5   NULL

Here, the columns of the driving table is preserved but the join clause NULLs the non-matched row(s) in the slave table.

Condition in WHERE clause

For the condition in the WHERE clause:

SELECT * FROM @t1 t1
LEFT OUTER JOIN @t2 t2 ON t2.id = t1.id
WHERE t1.id = 2

This gives:

2   2

This gives only the matched row(s) because the where clause filters out all rows that do not match the conditions which are applied after the join clause on a row by row basis.

Answer 2

SELECT *
FROM tableA
LEFT OUTER JOIN (SELECT * FROM tableB WHERE some_attribute = 'X') AS tabX ON tabX.some_id = tableA.some_id 
AND 
LEFT OUTER JOIN (SELECT * FROM tableB WHERE some_attribute = 'Y') AS tabY ON tabY.some_id = tableA.some_id

Try taking the conditions out of the WHERE clause and include them into the JOIN table. This way the conditions are applied before the OUTER JOINs, allowing a correct filter of the resulting rows.

Answer 3

When putting conditions in the where-statement, only rows matching those conditions will be returned, so it could be pointless using outer joins in this case. That is by design, and works in the same way in SQL2005, 2008, 2012.

Regarding ANSI NULL: In SQL Server, what does "SET ANSI_NULLS ON" mean?

Answer 4

First question: No DB engine would interpret the queries the same unless you have some kind of ANSI NULLS switch confusing the issue in your 2005 DB. They are different and expressions on columns in outer joined tables are meaningless outside of the ON clause (whether you understand it or agree with it or not). The confusion lies with you, not SQL Server. What you are questioning is as expected.

Second: I hope not! Fix your query!