NOT IN with NULL in the list

Question

Accepted Answer

NOT IN ('Sales', NULL) expands to dept != 'Sales' AND dept != NULL. dept != NULL is UNKNOWN for every row because nothing can be compared to NULL with = or !=. WHERE only keeps rows evaluating to TRUE - UNKNOWN rows are dropped. Fix this with WHERE dept IS NOT NULL or use NOT EXISTS.