Solving tips
- An anti-join returns rows on one side that have no match on the other; NOT EXISTS with a correlated subquery is the safe idiom.
- Avoid NOT IN when the subquery column can contain NULL, because a single NULL makes the whole predicate return no rows.
- NOT EXISTS, LEFT JOIN ... IS NULL, and (NULL-free) NOT IN are the three interchangeable ways to write an anti-join.
Find the products that have never been ordered. Note that some order rows have a NULL product_id, which is exactly what breaks a naive NOT IN.
Schema
CREATE TABLE products (
id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL
);
CREATE TABLE orders (
id INT PRIMARY KEY,
product_id INT REFERENCES products(id)
);
products
| id | product_name |
|---|
| 100 | Keyboard |
| 200 | Mouse |
| 300 | Monitor |
| 400 | Webcam |
orders
| id | product_id |
|---|
| 1 | 100 |
| 2 | 100 |
| 3 | 300 |
| 4 | NULL |
Task
Return the products that appear in no order, with columns product_id (the product’s id) and product_name. Order by product_id ascending.
Expected output
| product_id | product_name |
|---|
| 200 | Mouse |
| 400 | Webcam |
Approach
This is an anti-join: keep each product only when no order references it. NOT EXISTS with a correlated subquery expresses that directly and, crucially, stays correct even though orders.product_id contains a NULL. The subquery’s row-existence test never gets confused by NULLs the way NOT IN does.
Query
SELECT p.id AS product_id,
p.product_name
FROM products AS p
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.product_id = p.id
)
ORDER BY p.id;
Walkthrough
- For Keyboard (100) the subquery finds orders 1 and 2, so
NOT EXISTS is false and it is excluded.
- For Monitor (300) the subquery finds order 3, so it is excluded.
- For Mouse (200) and Webcam (400) the subquery finds no matching order, so
NOT EXISTS is true and both are kept.
- The order row with
product_id = NULL never satisfies o.product_id = p.id for any product, so it simply contributes nothing and does no harm.
ORDER BY p.id returns 200 (Mouse) then 400 (Webcam).
Complexity & notes
- The NOT IN trap:
WHERE p.id NOT IN (SELECT product_id FROM orders) returns an empty result here. Because the list contains a NULL, p.id NOT IN (100, 100, 300, NULL) evaluates to UNKNOWN for every product (SQL cannot prove the id is not equal to the NULL), so no row qualifies. That is a correctness bug, not just a performance one.
- Safe alternatives to NOT EXISTS:
LEFT JOIN orders o ON o.product_id = p.id WHERE o.product_id IS NULL, or NOT IN only after guarding the subquery with WHERE product_id IS NOT NULL.
- Performance: an index on
orders.product_id lets the correlated existence check stop at the first match per product; planners often execute NOT EXISTS and the LEFT JOIN ... IS NULL form as the same anti-join physical operator.