Why does NOT IN with a set containing NULL always return FALSE/NULL?

I had a query (for Postgres and Informix) with a NOT IN clause containing a subquery that in some cases returned NULL values, causing that clause (and the entire query) to fail to return anything.

What's the best way to understand this? I thought of NULL as something without a value, and therefore wasn't expecting the query to fail, but obviously that's not the correct way to think of 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 <> 1 AND x <> 2 AND x <> 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

StackExchange Database Administrators Q#17407, answer score: 36