Lessons · SQL · an inner join loses rows

An inner join drops the unmatched

INNER JOIN keeps only rows with a partner on both sides. An order whose customer_id matches no customer is not in the result, and nothing says so.

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

Seven orders in, six rows out is the classic quiet data-loss. Reports built on inner joins undercount whenever a key is missing or wrong.

How to think about it

Count both sides before and after. If the joined count is smaller than the table you started from, rows were dropped: find them with a LEFT JOIN and WHERE right.id IS NULL.

Worked example

SELECT COUNT(*) FROM orders;

How many orders exist.

SELECT COUNT(*) FROM orders o JOIN customers c ON c.id = o.customer_id;

How many have a matching customer. A smaller number means orphans.

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

The orphans themselves.

Your turn

Find reviews whose film no longer exists.

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

The trap

A NULL foreign key never matches anything, so an inner join drops those rows too. NULL is not a value that equals NULL.

Practise an inner join loses rows on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.