Customers who have never had an order delivered
List every customer who has never had an order delivered — including customers who have never ordered at all.
What it tests
Anti-joins, and the classic trap of putting a condition on the right-hand table in WHERE after a LEFT JOIN, which silently removes the customers you are looking for.
The pattern
Rows with no match (anti-join). "Never", "no", "without", "haven't" — you are looking for something that isn't there.
How to approach it
- Start from customers, because every customer must be considered — including ones with no orders.
- Look for a delivered order belonging to each customer.
- Keep the customers for whom none exists: NOT EXISTS, or a LEFT JOIN with the status test inside ON and
WHERE o.order_id IS NULL. - Check: Omar and the second Tom never ordered; Zoe only has a pending order.
Edge cases to mention
- Customers with no orders at all: must be included — that is why we start from customers.
- Customers whose only orders are cancelled or pending (Zoe): included.
- Orders with a NULL status: they are not delivered, so they don't count — NOT EXISTS handles this correctly.
Common mistakes
LEFT JOIN … WHERE o.status <> 'delivered': drops customers with no orders and keeps customers who also have delivered orders.WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE status = 'delivered')is fine here only because customer_id is NOT NULL in orders — say that out loud.- Starting from orders, which can never return customers who have no orders.
Not true: Starting from orders: the query still finds customers who have no orders at all.
In fact: A customer with no orders has no row in orders, so a query that starts there can never return them. Start from customers.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.