LEFT JOIN and the Art of Missing Data

Lora wants to send a "we miss you" email, and the list she needs is: which customers have never booked anything? Try to answer it with INNER JOIN and you hit a wall that no amount of cleverness climbs — a never-booked customer has no matching bookings row, so there is no matched pair for them to appear in. The tool from the last page structurally cannot see them.

LEFT JOIN is built for exactly this. It keeps every row from the left table, matched or not, and where no match exists it fills the borrowed columns with NULL. Suddenly absence is something a query can see, filter, and count — and "who hasn't" becomes as answerable as "who has".

The Question INNER Can't Ask

Watch the wall first. Here is an inner join of customers and bookings, this time starting from the customers' side because the question is about them:

SELECT c.name, b.seat
FROM customers c
JOIN bookings b ON b.customer_id = c.customer_id;

The Marquee has four customers, and the answer shows three. Marco, who joined last month and hasn't booked yet, is simply not there — and nothing in the result even hints that someone is missing. His absence left no row to stand on. That is the structural limit of inner: it can only show you evidence, never the lack of it.

LEFT JOIN Keeps the Left Side

Now change one word. Left means, literally, the table written to the left of the JOIN keyword — here, customers. A LEFT JOIN promises that every row of that table appears in the answer: with its match where one exists, and with NULL in the borrowed columns where none does.

SELECT c.name, b.seat
FROM customers c
LEFT JOIN bookings b ON b.customer_id = c.customer_id;

There is Marco, with NULL where a seat would be. You met NULL in Chapter 2 as "no value here", and this is its third appearance in the book: the NULL is not an error and not an empty string — it is the query saying no bookings row matched this customer. The gaps are not noise in the answer. The gaps are the answer.

An everyday picture, if it helps it stick: a class register. The teacher reads every name on the list, present or not, and silence after a name is information — that's who's absent today. An inner join only writes down the students who answered; a left join reads the whole register and records the silences too. That is the entire difference, so let's return to the real terms.

Filtering for the Gaps

Once absence shows up as NULL, Lora's question becomes one WHERE clause. Keep only the rows where the match failed — where the booking columns are NULL — and you have exactly the customers who never booked:

SELECT c.name, c.email
FROM customers c
LEFT JOIN bookings b ON b.customer_id = c.customer_id
WHERE b.booking_id IS NULL;

Two details are worth pinning. First, the test is IS NULL, never = NULL — Chapter 3's lesson holds: NULL is unknown, and unknown never equals anything, so the special form is the honest question. Second, the column tested is b.booking_id, the joined table's primary key, because a primary key is never NULL in a real row — if it shows up as NULL here, that can only be the padding of a failed match.

This LEFT-JOIN-plus-IS-NULL move is a named, reusable pattern, and it is worth recognizing on sight: customers who never returned, films that were never scheduled, products never sold, students who missed every session. Any "which of these has none of those?" question has this exact shape.

RIGHT and FULL Exist

For completeness, the family has two more members. RIGHT JOIN keeps every row of the right-hand table instead — the same idea, mirrored. FULL JOIN keeps unmatched rows from both sides, padding whichever half is missing. Both are honest tools, and you will rarely see either: professionals overwhelmingly write LEFT and simply put the table they want kept on the left, and you may do the same with a clear conscience.