Anti-joins and existence checks
HR is planning a reorg and asks Harry for a headcount: “Give me everyone at NOVA who isn’t currently managing anyone — the individual contributors.”
Isn't this the same shape as 'customers who never ordered'? Or is it the EXISTS lesson — 'customers who placed at least one order'? I genuinely don't know which one applies here.
Good question, because the honest answer is: all of them apply. 'Which rows don't have a match' has three common ways to write it in SQL, and they don't all behave the same. Let's find out the hard way first.
An employee is a manager exactly when their id shows up as someone else’s manager_id. So “not a manager” should just be “id not in that list”:
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);Running…
Zero rows. But that can’t be right — look at the table, at least five people clearly have direct reports. The query ran without an error and still lied to you. That’s worse than a crash, because nothing flags it.
The subquery SELECT manager_id FROM employees returns every manager_id in the table — including the NULLs, from every employee who reports to no one. And NOT IN against a list containing even one NULL can never come back TRUE. Comparing anything to an unknown value gives an unknown answer — the same rule from the WHERE chapter, where = NULL was never true either. Here, one unknown in the list poisons the entire check, for every single row.
NOT IN against a list with even one NULL in it is never true — for any row, not just the ones connected to that NULL.
Fix 1 — filter the NULLs out of the list first. The simplest patch: make sure the subquery never produces an unknown to begin with.
SELECT name FROM employees
WHERE id NOT IN (
SELECT manager_id FROM employees WHERE manager_id IS NOT NULL
);Fix 2 — NOT EXISTS. Remember “find customers who have placed at least one order”? That was EXISTS: for each customer, ask “is there at least one matching order row?” NOT EXISTS is the same question flipped — “is there no matching row?” — and because it’s a row-by-row correlated check rather than list membership, a stray NULL elsewhere in the table can’t contaminate it.
SELECT e.name FROM employees e
WHERE NOT EXISTS (
SELECT 1 FROM employees m WHERE m.manager_id = e.id
);Fix 3 — LEFT JOIN ... IS NULL. The anti-join from “which days had no orders”, applied to employees managing themselves’ table: join every employee to anyone who reports to them, and keep the ones with no match.
SELECT e.name FROM employees e
LEFT JOIN employees m ON m.manager_id = e.id
WHERE m.id IS NULL;All three return the same eleven names: Sam, Priya, Tom, Carlos, Yuki, Grace, Noah, Ravi, Jamal, Sara, and Ken — everyone except the five people who show up as someone’s manager (Meera, Lena, Omar, Ana, Ines). Three different sentences for the same underlying question, and only the first, unfiltered attempt was actually broken.
Running…
Try it: Go back to the very first, broken NOT IN query and run it again now, to actually watch it collapse to zero rows.
Run your query to see results here.
Still empty, and now you know exactly why: the subquery hands back five real manager ids and five NULLs, and that handful of NULLs is enough to silently disqualify all sixteen rows. NOT IN isn’t wrong to use — it’s wrong to use without knowing whether the subquery’s column can ever be NULL. When you’re not sure, NOT EXISTS or a LEFT JOIN anti-join don’t have this failure mode at all.
The catalog team wants to retire anything nobody buys: which products have never appeared in a single order? Return their names. (It’s fine if the correct answer turns out to be an empty list — that’s a real result, not a sign you did something wrong.)
Hint 1
This is the exact same shape as 'employees who manage no one,' just pointed at products and orders instead of employees and themselves.
Hint 2
NOT EXISTS (correlated subquery checking orders.product_id) or LEFT JOIN orders ... WHERE the joined id IS NULL both work well here.
Hint 3
SELECT p.name FROM products p WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.product_id = p.id);
Solution
Running…
Empty, and correctly so — every single product in NOVA’s catalog has been ordered at least once. That’s a genuine, useful answer for the catalog team (nothing to retire on that basis), and it closes out this chapter the same way it opened: an empty result isn’t a broken query, it’s just a “no” you can trust — as long as the query asking the question was built correctly in the first place.
“Which rows don’t have a match” can be written three ways — NOT IN, NOT EXISTS, or LEFT JOIN ... IS NULL. They usually agree, but NOT IN silently breaks the moment the subquery’s column can contain NULL, disqualifying every row instead of just the ones connected to it. When in doubt, reach for NOT EXISTS or a LEFT JOIN anti-join instead.