Unmatched rows

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.

Exercise Unmatched rows

Find customers with no orders using LEFT JOIN and IS NULL.

Show example solution

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Test yourself

Unmatched rows: match the terms

anti-join

missing
IS NULL

empty
LEFT JOIN

all left
WHERE

filter

Add IS NULL

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
____ o.id IS NULL;

Show answer

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

How is an anti-join written?
- UNION
- CROSS JOIN
- INNER JOIN
- LEFT JOIN + IS NULL
Show answer

LEFT JOIN + IS NULL — LEFT JOIN plus IS NULL gives an anti-join.

Next lesson: JOINs with NULL

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies