OUTER JOIN with join condition causes unexpected results SQL Server 2008 R2, but likely behaviour is found on all other versions of SQL Server as well.

This may seem obvious, but to me it seems like a bug.

The following query gives unexpected results, here is the setup:

CREATE TABLE #Base (
Key1 int,
RefDate date
)


CREATE TABLE #JoinTable (
    Key1 int,
    RefDate date
    )

INSERT INTO #Base
SELECT 1, '2012-05-05'
UNION
SELECT 2, '2013-06-06'
UNION
SELECT 3, '2014-07-07'
UNION
SELECT 4, '2015-08-08'
UNION
SELECT 5, '2016-09-09'


INSERT INTO #JoinTable
SELECT 4, '2012-05-05'
UNION
SELECT 5, '2013-06-06'
UNION
SELECT 6, '2014-07-07'
UNION
SELECT 7, '2015-08-08'
UNION
SELECT 8, '2016-09-09'


The following query executes as I would expect, returning the 3 rows that only occur in the base table:

SELECT * 
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1
WHERE j.Key1 IS NULL


Now I would like to restrict the rows to those before 1st of Jan 2014. As I understand it, I can take the two approaches below, both should be perfectly viable:

SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 AND b.RefDate &lt; '2014-01-01'
WHERE j.Key1 IS NULL


SELECT b.* 
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 
WHERE j.Key1 IS NULL AND b.RefDate &lt; '2014-01-01'


However, only the 2nd query returns the data that I want. The 1st appears to ignore the added join condition after the AND in the join line.

Why?

(sql fiddle link here)
It is not a bug, it is how LEFT JOIN works.

b LEFT JOIN j ON &lt;SomeCondition&gt;


would return all rows from b regardless of the join condition. Join condition defines/limits only what rows would be returned from j.



SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 AND b.RefDate &lt; '2014-01-01'
WHERE j.Key1 IS NULL


You can think of it as follows:


Go through all rows of #Base. 
For each row in #Base find all rows from #JoinTable that satisfy the join criteria (b.Key1 = j.Key1 AND b.RefDate &lt; '2014-01-01'). There could be no such rows in #JoinTable, in this case return NULLs. Note, that join criteria doesn't filter #Base, it filters only #JoinTable.


When you put b.RefDate &lt; '2014-01-01' into WHERE, then you are filtering #Base.