Lessons · SQL · keeping the rows with no match

Keep every row on the left

LEFT JOIN keeps every row of the left table; where the right table has no match, its columns come back NULL.

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 with their orders, including the customers who have none: an INNER JOIN silently drops them, and 'who never ordered' is often the question.

How to think about it

Put the table whose rows must all survive on the left. After the join, a NULL in a right-hand column means 'no match', which is itself an answer: WHERE o.id IS NULL finds the customers with no orders.

Worked example

SELECT c.name, o.amount

One column from each side.

FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

Every customer appears; a customer with no orders shows amount NULL.

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

The customers who never ordered: the rows where the right side was empty.

Your turn

Every film, with its reviews where any exist.

FROM films f  JOIN reviews r ON r.film_id = f.id;

The trap

A WHERE condition on a right-hand column (WHERE o.status = 'paid') turns the LEFT JOIN back into an inner one, because NULL fails the test. Put that condition in the ON clause.

Practise keeping the rows with no match on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.