Solving tips
- `NOT EXISTS` is the safe way to express anti-joins: it evaluates to true or false per row and is never derailed by NULLs in the subquery.
- `NOT IN` against a subquery that can produce a NULL is a classic trap — a single NULL makes the whole `NOT IN` predicate return unknown for every row, so you get zero results.
- For the positive case (customers who did order), `IN` and `EXISTS` are logically equivalent and both correct; the difference only bites with the negated forms and NULLs.
Return the customers who have never placed an order. The orders table intentionally contains a row with a NULL customer, which is what makes the naive NOT IN approach fail.
Schema
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer, -- nullable: an order can be unattributed
amount integer NOT NULL
);
Sample data — customers:
Sample data — orders:
| id | customer_id | amount |
|---|
| 101 | 1 | 250 |
| 102 | 1 | 90 |
| 103 | 3 | 400 |
| 104 | NULL | 150 |
Task
Return the name of every customer who has no matching row in orders. Order by name ascending. Expected column: name.
Expected output
Ana (id 1) and Cy (id 3) have orders; Ben and Dee do not. The NULL customer on order 104 belongs to nobody and must not suppress the answer.
Approach
This is an anti-join: keep customers with no matching order. NOT EXISTS expresses it safely because the correlated subquery only ever yields “there is a matching row” (true) or “there is not” (false) — NULLs inside orders never turn the predicate into unknown. That safety is exactly what a NOT IN subquery lacks here.
Query
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
)
ORDER BY c.name;
Walkthrough
For each customer, the subquery searches orders for a row whose customer_id equals that customer’s id. Ana (id 1) matches orders 101 and 102, so NOT EXISTS is false and she is excluded; Cy (id 3) matches order 103 and is likewise excluded. Ben (id 2) and Dee (id 4) have no matching order, so NOT EXISTS is true and they are kept. Order 104’s NULL customer never equals any c.id, so it simply never matches anyone and does no harm. Sorting by name gives Ben, then Dee.
Complexity & notes
The equivalent positive query — customers who did order — can be written with either IN or EXISTS, and both are correct and usually planned as a semi-join:
-- both return Ana and Cy
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders);
SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
The trap is the negated NOT IN form:
-- returns ZERO rows, not Ben/Dee, because of the NULL customer_id
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
Because the subquery list contains a NULL, id NOT IN (1, 3, NULL) evaluates to unknown (never true) for every customer, so the query silently returns nothing. Fixes are to use NOT EXISTS (preferred), to filter the NULL out with WHERE customer_id IS NOT NULL inside the subquery, or to use a LEFT JOIN ... WHERE o.id IS NULL anti-join. Performance-wise, an index on orders(customer_id) makes the NOT EXISTS lookup efficient.