5 is clearly not 1 or 2 - so why no rows?

Question

Accepted Answer

NOT IN (1, 2, NULL) expands to n != 1 AND n != 2 AND n != NULL. The last condition, n != NULL, is UNKNOWN for every row regardless of what n contains - 5, 10, and 3 all become UNKNOWN. AND with UNKNOWN is UNKNOWN, so the entire predicate is UNKNOWN for every row, and WHERE keeps nothing. Even though 5 is clearly not 1 or 2, the NULL in the list silently kills all results - always filter NULLs from NOT IN lists.