What is an anti-join, how do you implement one in SQL, and which implementation is most reliable?
An anti-join returns rows from the left table that have no matching row in the right table — the inverse of a semi-join. The three implementations are NOT EXISTS, NOT IN, and a LEFT JOIN with a NULL filter; NOT EXISTS is the most reliable because it is NULL-safe and communicates intent clearly.
How to think about it
The everyday version of this question is “find customers who have never placed an order” — and the anti-join is the pattern for any “find rows with no counterpart” problem. The interviewer wants to see you write it and, just as much, know which form is safe.
A worked example
NOT EXISTS is the one to reach for: it reads like English, stops at the first match, and handles NULLs correctly because the correlation is a WHERE condition, not an equality against a possibly-NULL list.
-- customers: 1 Aarav, 2 Bea, 3 Chen, 4 Dara
-- orders for: customer 1 (x2), customer 3 → 2 and 4 never ordered
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);| customer_id | name |
|---|---|
| 2 | Bea |
| 4 | Dara |
Bea and Dara are exactly the customers with no row in orders. The SELECT 1 is pure convention — only the existence of a matching row is tested, never its value, so any expression in there does the same job.
The other two forms
A LEFT JOIN ... IS NULL produces the same result (and usually the same plan), and it’s handy when you want to see what’s missing or pull other right-table columns for debugging:
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;NOT IN also expresses the anti-join, but it’s safe only when the subquery column is NOT NULL — a single NULL in the result silently empties the whole output:
-- fragile if orders.customer_id can be NULL
SELECT customer_id, name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);