6 min read

Joins

A normalized database stores customers in one table and orders in another, which is exactly right until you need to see them together. A join puts them back side by side, matching rows on a column they share. It is the single most used idea in SQL, the first thing most SQL interviews test, and the source of a few bugs that pass every code review.

Your answer

You LEFT JOIN customers to orders and add WHERE orders.amount > 500. Customers with no orders disappear from the result. Why, when a LEFT JOIN is supposed to keep them?

The four joins, on real rows#

Four customers, four orders. Ravi and Kabir have never ordered, and order 104 points at a customer id that does not exist. Switch between join types and watch which rows survive and where the NULLs come from.

Join two tables

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

customers

id	name	city
1	Asha	Pune
2	Ravi	Delhi
3	Meera	Kochi
4	Kabir	Pune

orders

id	customer_id	amount
101	1	450
102	1	1200
103	3	300
104	7	800

Result: 3 rows

name	order_id	amount
Asha	101	450
Asha	102	1200
Meera	103	300

Only pairs that match. Ravi and Kabir have no orders, so they vanish. Order 104 points at customer 7, who does not exist, so it vanishes too. Asha appears twice because she has two orders: a join can multiply rows, which is why a COUNT after a join is so often wrong.

The mental model that works: an inner join keeps only pairs that match. An outer join also keeps the unmatched rows from one side (LEFT or RIGHT) or both (FULL), and fills the columns from the other side with NULL. Most production code uses only INNER and LEFT, and writes "right" joins as LEFT joins with the tables swapped, because reading left to right is easier.

The bug in the question#

sql

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 500;

The LEFT JOIN does keep Ravi and Kabir, with o.amount as NULL. Then WHERE runs on the joined rows, and NULL > 500 is not true, so they are filtered out. The query quietly became an inner join. If you meant "every customer, plus their big orders if any", the condition belongs in the join itself:

sql

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 500;

ON decides which rows match. WHERE decides which rows survive. For inner joins the two give the same result. For outer joins they are different queries.

Joins multiply rows#

Asha has two orders, so she appears twice in the joined result. That is correct, and it is also how totals go wrong: join customers to orders and then to a customer_addresses table where Asha has three addresses, and her two orders become six rows. SUM(amount) now counts each order three times.

When a number looks too big after a join, check whether you joined two different "many" tables to the same row. The fix is usually to aggregate each one first, in a subquery or CTE, and join the totals.

How the database actually joins#

You write what to join. The planner picks how. There are three algorithms, and the plan from EXPLAIN will name one.

Join algorithm

For each row on one side, look up matches on the other side, ideally through an index. Superb when one side is small, such as one customer's orders. Without an index on the inner side it compares every pair, which on two large tables is catastrophic.

Predict the row count

customers (4 rows) INNER JOIN orders (4 rows), as in the playground

Same tables, LEFT JOIN

Same tables, FULL OUTER JOIN

Same tables, CROSS JOIN

NOT IN and NULL

WHERE id NOT IN (SELECT customer_id FROM orders) returns nothing at all if any customer_id in orders is NULL, because comparing with NULL is never true. Use NOT EXISTS, or a LEFT JOIN with IS NULL, which handle NULLs the way you expect.

Checkpoint#

Checkpoint

1. Why does WHERE o.amount > 500 turn a LEFT JOIN into an inner join?

2. Your revenue total doubled after you joined a second table. What is the most likely cause?

3. Which join algorithm suits one customer joined to their orders, with an index on orders.customer_id?

Worth memorising

INNER keeps matches. LEFT keeps every left row, with NULLs where nothing matched.
ON decides matching, WHERE decides survival. Different for outer joins.
Joins multiply rows. Aggregate before joining two "many" sides.
Nested loop for small sides with an index, hash join for big unsorted sides, merge join for sorted ones.

Say this in 60 seconds

A join combines rows from two tables that match on a column. An inner join keeps only matching pairs, a left join keeps every row from the left table and fills unmatched columns with NULL, and a full outer join keeps unmatched rows from both sides. The common bugs are a WHERE condition on the right table turning a left join into an inner join, so that condition belongs in ON, row multiplication when joining two one to many tables, which I fix by aggregating first, and NOT IN with NULLs, where I use NOT EXISTS. Under the hood the planner picks a nested loop when one side is small and indexed, a hash join for large unsorted inputs, and a merge join when both sides are already sorted.

Next: SQL interview questions Back: Normalization