I hope you're joking! :unsure: When using the Left Outer Join technique to find "missing" records, YOU CAN'T USE THE WHERE CLAUSE TO REFERENCE ANY COLUMNS IN THE "MISSING RECORD" TABLE except for the column that you specify IS NULL. The second criteria in the code snippet above won't filter anything, because ALL COLUMNS returned from CourseCompleted will be NULL so the ISNULL function will always return '12/31/2012' which is always greater than '1/1/2013'.

Think of it this way:

The Left Join tells SQL Server to return all rows from the Left regardless of matches found on the Right and to only include data from the Right when the join matches. The "<joined right table column> IS NULL" criteria in the WHERE clause tells SQL Server to not return any result records that DO have a match on the Right. All that is left for it to return is rows from the Left that are missing matches on the Right with the Right "columns" containing nothing but NULL placeholders.

If this seems confusing it's because it is confusing and that's why many people feel NOT EXISTS is more intuitive.

Anyone who is still unsure might benefit from doing a "Select *" on the original article's solution and see what happens with and without the where clause.