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
x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)x <> 1 AND x <> 2 AND x <> NULLtrue AND true AND unknown **unknown **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
x <> NULL as resolving to FALSE, you would expect NOT (x <> NULL) to evaluate to TRUE, and it doesn't. Both evaluate to UNKNOWN. The trick is that a row is selected only if the WHERE clause (if present) evaluates to TRUE -- a row is omitted if the clause evaluates to either FALSE or UNKNOWN. This behavior (in general, and for the NOT IN predicate in particular) is mandated by the SQL standard.
NULL NOT IN (some_subquery) should not return the outer row except if some_subquery doesn't return any rows. Which is why the execution plan when both columns are Null-able can be considerably more expensive. SQL Server Example
Commented
Jun 18, 2012 at 7:08