IN vs NOT IN symmetry with NULLs

Question

Accepted Answer

IN expands to `id = 1 OR id = 2 OR id = NULL`. The last comparison is UNKNOWN; FALSE OR FALSE OR UNKNOWN = UNKNOWN, so id = 5 is excluded from IN. NOT IN expands to `id != 1 AND id != 2 AND id != NULL`; the last comparison is UNKNOWN, and TRUE AND TRUE AND UNKNOWN = UNKNOWN, so id = 5 is also excluded from NOT IN. A NULL in the list makes both IN and NOT IN unreliable - neither can produce a definitive result for values outside the list.