Hey deroby, don't feel bad. I'm betting many readers would've made the same comment if you hadn't beat them to it! You're also right about the tendency for people to look no further once they've found something that works.

Yes, this is a beginner-level article describing Outer Join as promised by its title. The introduction however, sets it up as a report request that needs a solution rather than as a tutorial on joins. Given the introduction, I think it's fair to expect that other solutions might be provided and possibly contrasted and I wouldn't classify the inclusion of NOT EXISTS as "optimization" that would have made it more confusing. I think NOT EXISTS is less confusing (regardless of performance) and I hope beginners are learning it early on.

Also, as a beginner-level tutorial on Left Outer joins, I think the article would be improved if it pointed out that you can't have any other criteria referencing the table you're left joining to in the WHERE clause. For example, say you wanted a list of employees who have not completed required company courses in 2013. At first glance you might be tempted to add criteria to the WHERE clause (instead of the join where it belongs):

WHERECourseCompleted.CourseId IS NULL

AND CourseCompleted.DateComplete >= '1/1/2013' --Failed attempt to list all courses missed this year

and then be surprised when an empty set is returned.

This common mistake got me when I was learning and I think it is yet another good reason for teaching NOT EXISTS. With NOT EXISTS, it's more intuitive to put the additional criteria in the correct place, the sub-query's WHERE clause:

WHERE NOT EXISTS

(

SELECT 1

FROM CourseCompleted

WHERE CourseCompleted.EmployeeId = Employee.EmployeeId

AND CourseCompleted.CourseId = Course.CourseId

AND CourseCompleted.DateComplete >= '1/1/2013' --List all courses missed this year

)

I know I'm being an armchair quarterback, but I don't mean to pick on the author. I appreciate how hard it must be to decide how much detail to put in an article like this. Hopefully my comments will help someone avoid pitfalls that got me.