The NOT IN Trap
NULL breaks NOT IN.
Here's a question that looks easy. Which staff members are not managers? But it hides the most famous trap in SQL.

- Who isn't a manager?
- Looks easy
- A famous trap
Zero?!
The obvious answer. Keep staff whose ID is not in the list of manager IDs. Run it. Zero. That can't be right. Five people manage nobody. So what happened?
SELECT COUNT(*) AS not_managers
FROM staff
WHERE staff_id NOT IN (SELECT manager_id FROM staff);โโโโโโโโโโโโโโโโ โ not_managers โ โโโโโโโโโโโโโโโโค โ 0 โ โโโโโโโโโโโโโโโโ
Why NULL breaks NOT IN
Helen has no manager, so the list of manager IDs contains a null. Not in has to check that your value is different from every item in the list. But comparing anything with null gives unknown, not true. So the whole test is never true, and every row disappears. No error. Just a wrong answer.
The fix
The fix is simple. Remove the nulls from the subquery. Run it. Five, the right answer. Even safer, use not exists, or a left join with is null, which don't have this problem at all.
SELECT COUNT(*) AS not_managers
FROM staff
WHERE staff_id NOT IN (SELECT manager_id
FROM staff
WHERE manager_id IS NOT NULL);โโโโโโโโโโโโโโโโ โ not_managers โ โโโโโโโโโโโโโโโโค โ 5 โ โโโโโโโโโโโโโโโโ
Summary
If a not in subquery can return null, you'll get zero rows. Filter out the nulls, or use not exists. Knowing this will impress any interviewer.
Key takeaways- NOT IN + NULL = nothing
- Filter NULLs out
- Or use NOT EXISTS