Joins: asking across tables

The join, in one sentence

A join walks two tables and pairs rows whose keys agree: FROM orders o JOIN customers c ON c.id = o.customer_id. The ON is the matching rule, and the aliases — o and c — are there because two tables bring two columns with the same name, and the reader needs to know which one is meant. JOIN on its own means an inner join.

Only matched pairs survive an inner join. A customer with no orders does not appear, and an order whose customer vanished cannot exist in the first place if a foreign key guards it — which is one of the quiet benefits of chapter six.

Left join: keep everyone, matched or not

LEFT JOIN keeps every row of the left table and fills the right side with NULL when nothing matched — so customers c LEFT JOIN orders o ON o.customer_id = c.id lists every customer, with the order columns blank for those who have none. Turning it into counts is the everyday use: count(o.id) counts the orders that exist, and zero comes out as zero rather than as no row at all.

Reverse the two words and you have a right join, which is a left join written from the other end. Most people pick the left join and order their tables so the star of the query sits on the left; the database plans both the same way.

Finding what is not there

The anti-join is a left join with a condition that selects the misses: LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL returns the customers with no orders. The pattern reads as "nothing matched on the right side", and it is the readable alternative to a subquery with NOT EXISTS — the query planner treats them similarly, so the choice is about the reader.

A join chain continues past two tables — orders to order_items to products, each adding a column from the next — and the result is a wide, flat table that every later clause, including the aggregates of the next chapter, works on. When two tables name the matching column identically, USING (column) says the same thing as an ON equality, once.

How the database matches rows

A join is a comparison, and the planner has three broad ways to do it. A nested loop takes each row on one side and looks for matches on the other — cheap when the inner side is indexed. A hash join builds a table of keys from one side and probes it with the other. A merge join sorts both sides and walks them together.

You do not choose the algorithm in SQL. You write the relationship, and the planner picks the shape from its row estimates and the indexes available — which is why the same query can change plan as a table grows, and why EXPLAIN beats intuition.

One match can become many rows

A join multiplies rows: if one order has three lines and four payments, joining both children to the order produces twelve rows, and summing either child's amount over that result counts every value four or three times. This is fan-out, and it is the most common way a report becomes quietly wrong while every individual number still looks plausible.

The fix is to keep each side at its own grain. Aggregate the lines in a subquery or CTE, aggregate the payments separately, then join the two summaries to the order. count(DISTINCT ...) is a patch for "how many different things"; matching the grain is the cure.

Try these next

• Move an ON condition into WHERE and compare the row count before and after.
• Join a customer to two child tables at once and count the rows to see the fan-out for yourself.
• Rewrite the anti-join as NOT EXISTS and check that the two answers match.

Inner, left, and the anti-join

-- Inner: only the customers who ordered.
SELECT c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY c.name;

-- Left: every customer, with or without a line.
SELECT c.name, count(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY orders DESC, c.name;

-- Anti-join: the customers with nothing at all.
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Three tables: what was actually bought.
SELECT o.id AS order_id, p.name AS product, i.quantity
FROM order_items i
JOIN products p ON p.id = i.product_id
JOIN orders o ON o.id = i.order_id
ORDER BY o.id, p.name;