Unmatched rows
- SQL course
- Intermediate
- Lesson 9/115
- 4 min
Find rows in one table but not the other.
Unmatched rows
In a LEFT JOIN you catch unmatched rows with WHERE right.id IS NULL. This is an anti-join.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;This query returns customers who never ordered.
c.name is selected from customers c. orders o is joined on o.customer_id = c.id, keeping every row of the left table. WHERE keeps only the rows where o.id IS NULL holds.
Unmatched rows — details
An anti-join finds 'missing' records. Unmatched NULL rows are flagged and filtered.
For example, writing WHERE o.id IS NULL after a LEFT JOIN finds customers who have never placed an order.
Practice Unmatched rows
Find customers with no orders using LEFT JOIN and IS NULL.
Starter code
-- no orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.idSample solution
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Expected output
name Fatma Ali
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Unmatched rows: match the terms
| Term | Meaning |
|---|---|
anti-join |
missing |
IS NULL |
empty |
LEFT JOIN |
all left |
WHERE |
filter |
2 Add IS NULL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
____ o.id IS NULL;SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;Explanation: LEFT JOIN fills the right side with NULL for unmatched rows; WHERE o.id IS NULL finds customers with no orders.
3 How is an anti-join written?
- UNION
- CROSS JOIN
- INNER JOIN
- LEFT JOIN + IS NULL Correct answer
Explanation: LEFT JOIN plus IS NULL gives an anti-join.