AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 1
SQL ยท Lesson 96 of 126

The NOT IN Trap

NULL breaks NOT IN.

โฑ 2 min readโ–ถ Video at 2:16:08๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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

The NOT IN Trap
From the video: The NOT IN Trap
  • 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?

query.sqlsql
SELECT COUNT(*) AS not_managers
FROM staff
WHERE staff_id NOT IN (SELECT manager_id FROM staff);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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 list1, 1, 2, 2, 3, 3, 2, NULL
x <> NULLunknown, never true
NOT INneeds all true
Resultzero rows, no error

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.

query.sqlsql
SELECT COUNT(*) AS not_managers
FROM staff
WHERE staff_id NOT IN (SELECT manager_id
                       FROM staff
                       WHERE manager_id IS NOT NULL);
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Interview Questions, Part 2 โ€” window function classics