Skip to content

Joins · Easy

How would you find customers who have never placed an order?

Short answer

Use an anti-join: LEFT JOIN orders onto customers and keep rows where the order key IS NULL, or use NOT EXISTS with a correlated subquery. Both return customers with no matching order. NOT EXISTS is usually the clearest and is safe with NULLs, whereas NOT IN breaks if the subquery returns any NULL value.

Detailed answer

This is the most common "anti-join" question. Two good answers:

-- 1) LEFT JOIN … IS NULL
SELECT c.customer_id, c.full_name, c.signup_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.signup_date;
-- 2) NOT EXISTS
SELECT c.customer_id, c.full_name, c.signup_date
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
)
ORDER BY c.signup_date;

Why the IS NULL test must be on the join key (or the primary key): after a LEFT JOIN, unmatched rows have NULL in every right-hand column. Testing a nullable column such as o.coupon_code IS NULL would also return customers who did order but without a coupon.

Follow-up worth raising: "never ordered" and "never had a successful order" are different. To include customers whose only orders were cancelled, add AND o.status <> 'cancelled' inside the subquery.