Just to provide some additional explanation to billinkc's answer.
If null is a trump card you might be wondering why doesn't
WHERE 2 IN (2,3, NULL) exhibit the same behavior?
That one works as expected because it evaluates to
(2=2) OR (2=3) OR (2=NULL).
Under the rules of three valued logic for
Or-ed conditions if any of them evaluate to
true the expression is
true. Otherwise if any of them evaluate to
unknown the expression is
unknown. The only other possibility is that all are
false in which case the expression evaluates to
In order for a row to be returned in SQL the
WHERE clause must evaluate to
true rather than
unknown. The above does that.
1 NOT IN (2,3, NULL) evaluates to
(1 <> 2) AND (1 <> 3) AND (1 <> NULL). When conditions are
AND-ed all of them must evaluate to
true in order for the expression to evaluate to
The presence of the
NULL in the list guarantees that there will be at least one
UNKNOWN and that this will never be the case. Hence the reason for the " NULL pooches it all" behaviour in this context.
To give an analogy here as to why the
NOT IN behaviour makes sense.
Three friends Tom, Dick, and Harry are sitting in a railway carriage
with a complete stranger whose name is unknown to them.
If Tom is asked "Is your name different from everyone else's in this
carriage?" then it is impossible for him to answer with any certainty.
Even though he knows that
Tom <> Dick and
Tom <> Harry (so the
statement might be true) the veracity of the statement overall hinges on
the stranger's name and this is not known.
This is analogous to the SQL
'Tom' NOT IN ('Dick', 'Harry', Null)