Find customers who never placed an order
Tables Customers(id, name) and Orders(id, customer_id). Return customers who have no order at all.
Requirements:
nullable column can silently return nothing)
- this is an anti-join — customers with zero matching orders
- the result must be safe against
NULLs in the orders side (a naiveNOT INover a
SELECT c.id, c.name
FROM Customers c
-- your query hereWrite the query.
LEFT JOIN Orders onto Customers on the customer id, then filter WHERE o.customer_id IS NULL — the anti-join keeps customers with no matching order. NOT EXISTS is equivalent; NOT IN works too but returns nothing if the subquery has a NULL, so prefer LEFT JOIN ... IS NULL or NOT EXISTS.
- ✗Using
INNER JOINwithIS NULL, which can never match - ✗Trusting
NOT INwhen the subquery may containNULL - ✗Forgetting the
IS NULLtest must be on the joined (right-side) column
- →Why does
NOT INreturn no rows when the subquery yields aNULL? - →How do
LEFT JOIN ... IS NULLandNOT EXISTScompare on performance?
Task
Find customers who never placed an order.
SELECT c.id, c.name
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;How it works
The anti-join pattern: "rows in A that have no match in B".
The LEFT JOIN keeps every customer; those with orders have the Orders columns filled, while those without have the right-side columns as NULL. The WHERE o.customer_id IS NULL filter keeps exactly the latter.
Alternatives:
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.id)— equivalent and often efficient.WHERE id NOT IN (SELECT customer_id FROM Orders)— works, but if the subquery returns even oneNULL, the wholeNOT INyields an empty result (three-valued logic).
⚠️ Use LEFT JOIN ... IS NULL or NOT EXISTS; beware NOT IN with NULL.
