Customers who have never had an order delivered

Step 1 Solve it yourself first

Tables you will use

customers orders

What it really 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.

Picture it

flowchart LR
  C["every customer"] --> Q{"any delivered order<br/>for this customer?"}
  Q -->|"yes: Priya, Tom, Lena"| X["dropped"]
  Q -->|"no: Omar, Zoe, Tom 6"| K["kept"]

The approach, step by step

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.

SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'delivered'
)
ORDER BY c.customer_id;

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'delivered'
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Practice

  LeetCode — Top SQL 50 study plan

  free account

• Practice

  DataLemur — anti-join questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why the status condition must go in ON, not WHERE, in the LEFT JOIN version.

   Show answer

   In the LEFT JOIN version, the ON decides what counts as a match: "an order of this customer that was delivered". A customer with no delivered order finds no match, so they get one row with NULLs, and WHERE o.order_id IS NULL keeps exactly those customers.

   Move the status test into the WHERE and it runs after the join, on every row. A customer whose orders are all pending gets matched to those orders (not NULL, so IS NULL drops them), and a customer with no orders has a NULL status, which fails o.status = 'delivered'. Every row fails one test or the other, so nothing comes back.

2. Writefrom a blank page, then run it

   List products that have never been part of a delivered order.

   Show answer

   For each product, look for any item line in a delivered order. Only the phone case has none: it has never been sold at all. Every other product appears in at least one delivered order.

   sqlCopy

   SELECT p.product_name
   FROM products p
   WHERE NOT EXISTS (
     SELECT 1
     FROM order_items oi
     JOIN orders o ON o.order_id = oi.order_id
     WHERE oi.product_id = p.product_id
       AND o.status = 'delivered'
   )
   ORDER BY p.product_name;

   Try it

   1 row

   product_name
   Phone case

3. Predictsay it before you run it

   What does the LEFT JOIN version return if you move the status test into WHERE?

   Show answer

   No rows at all. After the LEFT JOIN, every row either has an order (so order_id is not NULL) or has no order (so status is NULL, and NULL = 'delivered' isn't true). A row would need a delivered status and a NULL order_id at once, which is impossible. A filter on the right-hand table in WHERE quietly cancels the LEFT JOIN.

   sqlCopy

   SELECT c.customer_id, c.name
   FROM customers c
   LEFT JOIN orders o ON o.customer_id = c.customer_id
   WHERE o.status = 'delivered' AND o.order_id IS NULL
   ORDER BY c.customer_id;

   Try it

   No rows

   customer_id	name

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   What should happen to a customer whose only order has a NULL status? Does your query do that?

   Show answer

   They have never had an order delivered, so they should be listed. (In this database status is NOT NULL, so it can't happen, but it is worth checking.)

   The NOT EXISTS solution gets it right: inside the subquery, NULL = 'delivered' is not true, so no delivered order is found, NOT EXISTS is true, and the customer is listed. The LEFT JOIN version also lists them, because the ON finds no match.

   The version that gets it wrong flips the logic: "customers who have an order WHERE status <> 'delivered'". NULL <> 'delivered' is not true either, so that customer would be missed. Whenever you write a negative test, ask what it does with NULL.

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

This is an anti-join. I drive it from customers so customers with no orders are included, and keep those for which NOT EXISTS finds a delivered order. Equivalently, a LEFT JOIN with the status filter inside the ON clause and WHERE the order id IS NULL. The trap is putting the status filter in WHERE, which turns the left join into an inner join.

Follow-up questions

Tick each check when you can do it without notes