A NOT IN subquery is returning 0 rows even though you expect results. Why?

If the subquery returns ANY NULL value, NOT IN returns no rows. SQL evaluates 'col NOT IN (1, 2, NULL)' as 'col != 1 AND col != 2 AND col != NULL'. The last condition evaluates to UNKNOWN (NULL), which fails the WHERE for every row. Debug: SELECT * FROM employees WHERE dept_id IS NULL — if any rows exist, this is the cause. Fix option 1: NOT IN (SELECT dept_id FROM depts WHERE dept_id IS NOT NULL)