NOT IN subquery with NULLs

Question

Accepted Answer

NOT IN expands to: id != 1 AND id != 2 AND id != NULL. The last condition is UNKNOWN for every row because nothing can be compared to NULL with !=. AND UNKNOWN evaluates to UNKNOWN, and WHERE only keeps TRUE rows - so zero rows are returned. The fix: add WHERE emp_id IS NOT NULL to the subquery, or use NOT EXISTS instead, which handles NULLs safely.