Schema and sample data
| customer_id | name |
|---|---|
| 1 | Alice Chen |
| 2 | Bruno Diaz |
| 3 | Chloe Park |
| 4 | Dev Patel |
| 5 | Elena Ruiz |
| order_id | customer_id |
|---|---|
| 101 | 1 |
| 102 | 1 |
| 103 | 3 |
| 104 | 5 |
Show setup SQL (copy to run)
CREATE TABLE Customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL
);
INSERT INTO Customers (customer_id, name) VALUES
(1, 'Alice Chen'),
(2, 'Bruno Diaz'),
(3, 'Chloe Park'),
(4, 'Dev Patel'),
(5, 'Elena Ruiz');
INSERT INTO Orders (order_id, customer_id) VALUES
(101, 1),
(102, 1),
(103, 3),
(104, 5);You have a Customers table and an Orders table. Return the name of every customer who has never placed an order, that is, a customer with no matching row in Orders. Sort the result by name in ascending order.
Expected result
| name |
|---|
| Bruno Diaz |
| Dev Patel |
Show hint
Think about an anti join: keep every customer, attach their orders with a LEFT JOIN, then keep only the rows where the order side came back empty. Which column tells you that no order matched?
Try to write the query yourself before you open this.
Show solution and explanation
SELECT c.name
FROM Customers AS c
LEFT JOIN Orders AS o
ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.name;The LEFT JOIN keeps every customer even when no order matches. For customers 1, 3 and 5 the join finds rows in Orders, so o.order_id holds a real value. For customers 2 (Bruno Diaz) and 4 (Dev Patel) there is no match, so the join fills the Orders columns with NULL. The filter WHERE o.order_id IS NULL then keeps exactly those unmatched rows. This shape is called an anti join. See how the join types work for the full picture.
A cleaner and NULL safe alternative is NOT EXISTS, which many people prefer because it says what you mean and stops at the first matching order: SELECT name FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id) ORDER BY name;. It returns the same two customers and reads as a direct question: keep the customer when no order exists for them.
Do not reach for NOT IN with a subquery here. WHERE customer_id NOT IN (SELECT customer_id FROM Orders) looks equivalent, but if the Orders.customer_id list ever contains a NULL, the whole NOT IN evaluates to UNKNOWN and the query returns zero rows. That is the classic NOT IN NULL trap. Prefer NOT EXISTS or the LEFT JOIN ... IS NULL anti join, both of which handle NULLs safely. For a walkthrough of this exact scenario, see the customers with no orders interview question.