Inner join

Combine two tables into one, keeping only the rows that match on both sides.

6 min read

Overview

An inner join combines two tables into one, keeping only the rows that find a match on both sides. You reach for it whenever the answer needs columns that live in separate tables. Here are two: customers lists who they are, and orders records what they bought. Each order stores the id of the customer who placed it.

customers

id	name
1	Ana
2	Ben
3	Cara

orders

id	customer_id	total
101	1	40
102	1	25
103	2	60

Trace each customer_id into customers. Predict the result row count and name the customer who contributes no row.

How it works

Name the two tables, then match a column on one to a column on the other. Each order’s customer_id looks up a customer’s id, and the two rows become one. The aliases o and c let you say which table each column comes from.

select c.name, o.id, o.total
from orders o
join customers c on o.customer_id = c.id
order by o.id;

Run the demo and compare its rows with your prediction.

Follow the keyPress play, or step through it yourself.

orders o

id	customer_id	total
101	1	40
102	1	25
103	2	60

customers c

id	name
1	Ana
2	Ben
3	Cara

result

nameidtotal

o.customer_id = c.id

The result has three rows. Ana appears twice because two orders match her, while Ben appears once. Cara disappears because no order creates a matching pair for her. That is what inner means here: a row survives only as part of a match.

Patterns

The same move, following a key from one table to another, covers most of what you will write.

1
One customer, many orders
The match is one-to-many here, so Ana appears twice in the result. That repetition is correct, yet it also inflates a careless aggregate. Summing a customer column after this join counts it once per order instead of once per customer. Pitfalls covers the consequences.

Chain more than two tables

Joins compose. To bring in a third table, add another join with its own condition. Each join follows one more key.

select c.name, o.id, r.name as region
from orders o
join customers c on o.customer_id = c.id
join regions r   on c.region_id = r.id;

3
Join, then aggregate
Once the rows are together, group them. To total each customer’s spend, join orders to customers and sum by customer with group by c.name and sum(o.total). The join brings the rows together and the group-by rolls them up.

Trade-offs

An inner join answers “show me the rows that go together.” Sometimes the rows that do not go together are the answer. Think of every customer including the ones who never ordered. An inner join cannot give you Cara because she has no match. You will meet outer joins next. They keep the unmatched rows and fill the gaps with null. Use an inner join for the matches, and a left join when a missing match is itself the point.

Pitfalls

Fan-out double counts

Joining a customer to their orders repeats the customer once per order. Ana’s two orders make two Ana rows. Sum or count a customer column after that and you inflate it, because you are now counting orders rather than customers. When you aggregate across a join, be sure of what one row now means.

A missing condition is a cross join. Drop the on clause and every order pairs with every customer. Three and three become nine, and the numbers look plausible.
The drop is silent. An inner join omits unmatched rows without any signal. A customer with no order disappears, an order with a bad key disappears, and the result still looks correct.
Match on the right key. Join on the column that actually names the relationship, because a join on the wrong column still returns plausible-looking rows and the mistake does not announce itself.
Keys can be more than one column. When two columns define the relationship, join on both with on o.customer_id = c.id and o.region = c.region. Matching only half of a composite key is the same wrong-key mistake in a subtler form, and the incomplete match fans out.

Performance

A selective equality key can be matched efficiently, while a missing or partial key can approach n times m comparisons and fan out the result. Check the full relationship and intended result grain before tuning.

Practice

EasyJoin orders to customers through the foreign-key relationship and return one row per matched order with its customer name. The row count should equal matched orders rather than total customers.MediumCompute customer spend totals after joining on the customer key. Reconcile the output to the orders so each contributes once and customers with none are absent.HardBuild an order-to-customer-to-region query one relationship at a time. Each output order must have exactly one region, and the row count must not fan out.

Recap

Reach for an inner join when the answer needs columns from matching rows and unmatched rows do not belong.
Predict the output grain before running it, because one-to-many matches repeat the one side.
Check every column in the relationship key. A partial key can create plausible fan-out.
Check aggregates after the join against the intended grain so repeated rows do not inflate them.
Move to a left join when the missing match is part of the answer.