OUTER JOIN with join condition causes unexpected results
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.