NULL and NOT IN

Question

NULL and NOT IN

Accepted Answer

This is one of SQL's most dangerous NULL traps. NOT IN with a NULL in the list returns no rows at all. The reason: NOT IN is equivalent to id != 1 AND id != 2 AND id != NULL. The last condition id != NULL evaluates to NULL (unknown), and unknown AND anything = unknown, so the WHERE clause never matches. Always filter NULLs out of subquery results used with NOT IN, or use NOT EXISTS instead.