Why NOT IN With a NULL in the List Returns No Rows at All
If the list or subquery on the right of NOT IN contains a single NULL, the whole condition can never be true, so the query returns an empty result even though rows obviously qualify. The reason is three-valued logic: NOT IN asks is this value different from every item, and comparing with NULL gives unknown, which is not true. The safe forms are NOT EXISTS or a LEFT JOIN with an IS NULL check, and both are usually faster as well.
Questions this Concept answers
Why does a single `NULL` in the list empty the result?
J
jeremy
Text
The NOT IN Pitfall: NULL Values in PostgreSQL
This article explains a trap in SQL: if the list inside a NOT IN contains even one NULL, the query returns no rows, no matter what the data holds. The reason is three-valued logic, where comparing a …