Why does NOT IN with a set containing NULL always return FALSE/NULL?
Boolean logic - or Three valued logic


IN is shorthand for a series of OR conditions
x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)
... is the same as x &lt;&gt; 1 AND x &lt;&gt; 2 AND x &lt;&gt; NULL
... is the same as true AND true AND unknown **
... = unknown **
... which is almost the same as false in this case as it will not pass the WHERE condition **


Now, this is why folk use EXISTS + NOT EXISTS rather than IN + NOT IN. Also see The use of NOT logic in relation to indexes for more 

** Note: unknown is the same as false at the end of an expression in a WHERE condition.
While the expression is being evaluated, then it is unknown
See @kgrittn's comment below for why