Lesson 30 / 37 · Composing queries

NOT IN with a NULL in the list keeps nothing

Research has nobody, yet the query returns no rows. Dennis has no department, so the subquery list contains a NULL, and 4 NOT IN (1, 2, 3, NULL, 9) is unknown: 4 might equal that NULL. Every row shows ∅ or ✗.

The query

SELECT name
FROM departments
WHERE id NOT IN (SELECT dept_id FROM employees)

Try WHERE dept_id IS NOT NULL in the subquery: the NULL leaves the list, and Research passes. NOT EXISTS avoids the trap as well.

Next: UNION stacks two results · All lessons