I think this is a question with a very wrong answer. Even wronger than Hugo suggests.
Hugo Kornelis (1/11/2013)
I don't really agree with the answer marked as correct.
NOT IN can only be used in limited cases. The comparison condition must be single-column, and there must not be any null values in the comparison column in the subquery. Because of those limitations, I never use NOT IN for this kind of questions - I prefer to use the same method in all cases.
I agree with that much - that's one of the errors.
NOT EXISTS and the outer join with IS NULL test can both be used under all circumstances, but I far prefer the NOT EXISTS method, because it is far easier to write, and (even more important!) to understand when you have to revisit your code later. In most cases, the execution plan will be the same, so there is no performance benefit of one over the other (this was not always true in older versions of SQL Server, but that has long been changed).
Unfortunately, that outer join with IS NULL test can be used everywhere NOT EXISTS will work depends on the existence of columns which are not nullable; that is of course guaranteed in the relational model, but not in SQL: a a table every one of whose columns is nullable is permissable. The IS NULL test can't distinguish the case where there is no corresponding row from the case where there is a row in whch every column is NULL. The NOT EXISTS method can make that distinction.
So the technically correct answer to the question would be that only NOT EXISTS and outer join with IS NULL test (a so called anti-semi-join) can always be used. And I would add that I recommend sticking to the NOT EXISTS method for maintainability, unless you happen to run into a situation where the execution plans are not equal, and the plan with the anti-semi-join performs better - and even in that case, I would still prefer the NOT EXISTS version, unless the performance difference prevents me from achieving the required response times.
No, the technically correct answer is that only the NOT EXISTS method is correct, unless you are lucky enough to be in an environment a littlemore like the relational model that SQL is.