JOIN, LEFT JOIN and self-joins

Game: chapters V, VI, IX and X

A good database keeps each fact in one place, so most real questions need two or more tables at once. Joins put them back together.

Keys: how tables point at each other

In the bakery database (see the bakery database page), an order does not repeat the customer's name and town. It stores customer_id, the id of a row in customer. A column like customer.id, which identifies each row, is a primary key; a column like orders.customer_id, which holds the primary key of another table, is a foreign key. A join follows that pointer.

The inner JOIN

FROM a JOIN b ON condition pairs every row of a with every row of b for which the condition is true. The condition is almost always "this foreign key equals that primary key". Give each table a short alias (o, c) and prefix the columns with it, so that both you and the database know which id you mean.

SELECT o.id, o.day, c.name, c.town
FROM orders AS o
JOIN customer AS c ON c.id = o.customer_id
WHERE o.day = '2026-03-02' AND o.time < '12:00'
ORDER BY o.id;

An inner join keeps only the pairs that match. An order whose customer is missing would vanish from the result, and so would a customer who never ordered.

Several joins in a row

Joins chain. Each new JOIN adds one table and one condition linking it to a table already in the query. To know how much money each customer of Hillcrest spent, go from the customer to their orders, from the orders to their lines, and from the lines to the products: three joins, then a GROUP BY with SUM; HAVING keeps the customers who spent more than 40. What they paid is each line's unit_price, the price on the day; the last column prices the same pieces at today's product.price instead, and comes out higher for most of them, because three products rose on Monday 16 March.

SELECT c.name,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS spent,
       ROUND(SUM(oi.quantity * p.price), 2) AS at_todays_prices
FROM customer AS c
JOIN orders AS o      ON o.customer_id = c.id
JOIN order_item AS oi ON oi.order_id = o.id
JOIN product AS p     ON p.id = oi.product_id
WHERE c.town = 'Hillcrest'
GROUP BY c.id, c.name
HAVING SUM(oi.quantity * oi.unit_price) > 40
ORDER BY spent DESC, c.name;

Four customers of Hillcrest spent less and are dropped by HAVING. Hugo Silva, Greta Rossi and Marco Costa, who never ordered, are missing for another reason: an inner join keeps only the rows that match, so they never reach the grouping (see LEFT JOIN below).

When you build a query like this, add one join at a time and look at the rows before you group them. If the count of rows jumps unexpectedly, a condition is missing or wrong, and every sum after it will be inflated.

Keeping the unmatched rows: LEFT JOIN

LEFT JOIN keeps every row of the table on its left, even when nothing on the right matches. For those rows, the columns of the right-hand table are filled with NULL. That makes it the tool for questions about absence: which customers have never ordered, which products nobody bought. Join, then keep the rows where the right-hand side is NULL. Here it finds the one product in the catalogue that was never sold.

SELECT p.name, p.category
FROM product AS p
LEFT JOIN order_item AS oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL
ORDER BY p.id;

The same pattern on staff and orders finds Sam Ito, the baker, who never served at the counter. Where you write a condition matters with a left join. A condition on the right-hand table placed in ON limits what may match, and still keeps every left row. The same condition placed in WHERE runs after the join and throws away the rows whose right side is NULL, which turns the left join back into an inner join.

A table joined to itself

Sometimes the pointer leads back to the same table. In the bakery, customer.referred_by holds the id of the customer who recommended the shop. To show each customer next to the customer who referred them, join customer to itself under two different aliases, one playing the newcomer and one the referrer. A left join keeps the customers who came on their own. The first eight customers are enough to see both kinds; Alice Turner brought Chloe Martin, who brought Emma Novak, who brought Farid Haddad, a chain that CTEs follow to its end.

SELECT newcomer.name AS customer, referrer.name AS referred_by
FROM customer AS newcomer
LEFT JOIN customer AS referrer ON referrer.id = newcomer.referred_by
WHERE newcomer.joined < '2024-08-01'
ORDER BY newcomer.id;

Self-joins also compare rows of the same table with each other: two orders by the same customer on the same day, or two customers with the same name. In that case add a condition such as a.id < b.id, so that a row is not paired with itself and each pair appears once rather than twice.

SELECT a.id, b.id AS other_id, a.name, a.town, b.town AS other_town
FROM customer AS a
JOIN customer AS b ON b.name = a.name AND a.id < b.id
ORDER BY a.id, b.id;

Common mistakes

Forgetting the ON condition, or listing two tables separated by a comma with no condition: every row is paired with every other row, and a few thousand rows become millions. Joining on the wrong columns, such as o.id = c.id, which runs without error and returns nonsense. Leaving a column name unprefixed when both tables have it. Summing after a join that has duplicated rows. Filtering a left join's right-hand table in WHERE and losing the unmatched rows you wanted to keep.

Try it: Orders by town, Takings per day, Referred from afar, The price rise and The staff room, on the exercises page.

In the game: Chapter V, The Boarding House, introduces JOIN; Chapter VI, Follow the Francs, chains three; Chapter IX, The Trunk, joins inside a CTE; Chapter X, Never Seen Together, practises the self-join.