deroby (11/12/2013)

---

Hi there,

Thx for the article about what often turns out to be a brain-breaker for a lot of people starting out with SQL.. but... (there's always a but =)

Although the LEFT OUTER JOIN ... WHERE <somefield> IS NULL gets the job done I tend to discourage its use for three reasons

1. It isn't very readable for people not into SQL

2. I've seen it 'fail' when people use a <somefield> that turns out to be NULL-able

3. Not applicable in your example, but the JOIN might cause doubles/triples/etc in the results depending on the data-structure... which they then 'fix' again by using DISTINCT etc... ( "ahh, the horror" =)

The (IMHO) much easier to understand (and code) method is by using WHERE NOT EXISTS() which pretty much circumvents all three issues. Ironically it usually ends up with the same query plan anyway. Or, (as is the case here!) sometimes even comes up with a better one. But most of all, I think it is a lot easier to read which (on the long run) makes it a more favourable option to me.

/*List of employees and courses they have not completed*/

SELECT Employee.EmployeeId

,Employee.FirstName

,Employee.LastName

,Course.CourseId

,Course.CourseName

FROM #Employee AS Employee

CROSS JOIN #Course AS Course

WHERE NOT EXISTS ( SELECT *

FROM #CourseCompleted AS CourseCompleted

WHERE CourseCompleted.EmployeeId = Employee.EmployeeId

AND CourseCompleted.CourseId = Course.CourseId )

ORDER BY Employee.EmployeeId

My 2 cents.

Roby