Lessons · SQL · LEFT JOIN and NULL
Keep everyone, spot the gaps
LEFT JOIN keeps every row from the left table; where the right has no match, its columns are NULL. WHERE right.id IS NULL then finds the unmatched.
Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.
What it is for
Customers who never ordered, products never sold, users never notified: the anti-join is a daily report.
How to think about it
Which side might be missing? LEFT JOIN the thing that might be missing, then filter for its key being NULL. Or write NOT EXISTS, which says it plainly.
Worked example
SELECT c.name FROM customers cOnly names needed.
LEFT JOIN orders o ON o.customer_id = c.idEveryone kept.
WHERE o.id IS NULL;Only those with no order.
Your turn
Films with no reviews.
FROM films f LEFT JOIN reviews r ON r.film_id = f.id WHERE r.id NULL
Run a query against real tables
The trap
A condition on the right table in WHERE (WHERE r.stars > 3) discards the NULL rows and makes it an inner join. Put it in ON.
Practise LEFT JOIN and NULL on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.