Problem
Task
Return customers who do not have any matching rows in the orders table.
Schema
Table Schema
customers(id, name, email)
orders(id, customer_id, total_amount)Input
Sample Data
customers
| id | name | |
|---|---|---|
| 1 | Alice | [email protected] |
| 2 | Bob | [email protected] |
| 3 | Chloe | [email protected] |
orders
| id | customer_id | total_amount |
|---|---|---|
| 501 | 1 | 70 |
| 502 | 3 | 95 |
Output
Expected Output
| id | name | |
|---|---|---|
| 2 | Bob | [email protected] |
Answer
Check Your Solution
Show Answer and Explanation
Correct Answer
SELECT c.id, c.name, c.email
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
WHERE o.id IS NULL;Explanation
LEFT JOIN keeps every customer. Customers without matching orders have NULL values on the orders side, so o.id IS NULL identifies them.
Common Mistakes
- Using INNER JOIN, which removes customers with no orders.
- Checking c.id IS NULL even though customer rows are preserved.
- Putting o.id IS NULL in the JOIN condition instead of the WHERE filter.
Concepts
Related Concepts
LEFT JOIN
NULL Handling
Anti Join
IS NULL
Next practice