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

  1. Start from customers, because every customer must be considered — including ones with no orders.
  2. Look for a delivered order belonging to each customer.
  3. 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.
  4. Check: Omar and the second Tom never ordered; Zoe only has a pending order.

Edge cases to mention

Common mistakes

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.