easyJoinsSQL
Customers Who Never Ordered
Write a SQL query that lists the customers who have never placed an order: the classic anti-join asked in SQL and MySQL interviews.
SQL
You have two tables. customers(id, name) holds every customer and orders(id, customer_id, total) holds their orders, where customer_id points to customers.id.
Write a query that returns the name of every customer who has never placed an order. Return one column, name, in any order.
Examples
Example 1
Input: customers: (1, Asha), (2, Ben), (3, Chen) orders: (10, 1, 250), (11, 3, 90) Output: name Ben Why: Asha and Chen each have an order; Ben has none.
Constraints
- Customer names are not unique: two different customers can share a name.
- A customer can have any number of orders, including zero.
- Return each customer without orders once.
Hints
Hint 1
Which join keeps rows from the left table even when nothing matches on the right?
Hint 2
After a LEFT JOIN, the order columns are NULL for customers with no orders.
Hint 3
NOT EXISTS is another clean way to say "has no matching order".
Solution
Show the solution (try it yourself first)
Approach: an anti-join
Join every customer to their orders with a LEFT JOIN. Customers with no orders still appear, with NULL in every order column, so filtering on o.id IS NULL keeps exactly them. NOT EXISTS expresses the same idea and is just as fast in MySQL 8, which turns both into an anti-join.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Avoid WHERE c.id NOT IN (SELECT customer_id FROM orders): if any customer_id is NULL, NOT IN returns no rows at all.
Complexity
With an index on orders.customer_id this is one pass over customers and one index lookup each: about O(n log m) for n customers and m orders. Without that index, MySQL may hash-join (8.0.18+) or scan orders repeatedly, so index foreign-key columns.
Found a mistake in this problem, its tests or the solution?