◀ Course contents Part 3 · Module 3-01

INNER JOIN

Answers that live in two tables at once

Everything so far has come from a single table. Real questions rarely do: orders knows a user id and nothing about the person, and the name is one table away. A join lines two tables up on the value they share — and, done carelessly, quietly multiplies or deletes rows while doing it.

Ready?

ON Says Which Row Goes With Which

Databases split data across tables on purpose. A user's name is stored once, in users, and every order refers to it by user_id rather than repeating it. That is what keeps a name change a one-row edit instead of a hunt.

The cost is that answering "who placed this order" needs both tables. A join is how you put them back together:

SELECT o.order_id, u.name, o.amount
FROM orders o
JOIN users u ON u.user_id = o.user_id;

Three moving parts. The second table, the word ON, and a condition saying which row over there belongs with which row over here. o and u are table aliases: short names that let every column say where it came from.

Qualifying is not optional when a name is ambiguous — both tables have a user_id, and a bare one gets you ambiguous column name. But qualify everything anyway. It costs two characters and it means a reader never needs the schema in their head to follow the query.

JOIN, INNER JOIN, and the comma

JOIN on its own means INNER JOIN; the word is optional and usually left out. The older comma form — FROM users, orders WHERE ... — does the same thing with the condition in the WHERE clause. Prefer the explicit JOIN ... ON: it keeps how the tables relate separate from which rows you want, and it makes a forgotten condition obvious.

Quick check

Why does the database complain about SELECT user_id FROM orders o JOIN users u ON u.user_id = o.user_id?

What a Join Actually Does

The mental model that makes every join behaviour predictable is this: a join is a cross product that has been filtered.

Pair every row of the first table with every row of the second — 12 users against 24 orders is 288 pairs — then keep only the pairs where the ON condition is true. Since each order has exactly one matching user, 24 of the 288 survive.

The condition is the only thing standing between you and every pair. A missing ON is not a syntax error — it is a query that runs and returns the cross product, which on two tables of any size is how a laptop stops responding.

WHERE then works on the joined rows exactly as it always has, and can test a column from either table:

WHERE o.status = 'paid'
  AND u.plan   = 'team'

So does GROUP BY. Join first, group second — that is what lets you total a column from one table while grouping by a column from the other, which is most of what a reporting query ever does.

Quick check

A join between a 1,000-row table and a 5,000-row table is written with no ON clause. What happens?

Fan-Out, and Why Totals Come Out Too Big

A join can change the number of rows in both directions. Upwards first.

Join on a column that is unique in the table you are joining to — a primary key — and each row finds exactly one partner, so the row count is unchanged. Every order matches one product, so joining orders to products keeps 24 rows.

Join on a column that is not unique and each row finds several partners, so it is duplicated once per match. That is fan-out.

-- One product, three orders: the product row appears three times
SELECT p.name, p.price, o.amount
FROM products p
JOIN orders o ON o.product_id = p.product_id;

On its own that is harmless — you asked for one row per order and got one. It becomes a bug the moment an aggregate is put on top: SUM(p.price) over that result adds the product's price once per sale, which is not the catalogue value of anything.

Safe

Joining to a primary key

One match at most, so the row count cannot rise. ON p.product_id = o.product_id where product_id is unique in products.

Multiplies

Joining to a repeated column

Several matches per row. Every aggregate downstream is counting the same value more than once, and the result looks entirely normal.

The habit that catches it costs nothing: count the rows before and after. If the number went up and you did not expect it to, stop before writing the aggregate.

Revenue that was 40% too high

A revenue query joined orders to a discounts table to pick up a campaign name. Most orders had one discount row; some had two, from overlapping promotions. Those orders were counted twice in the SUM, and the monthly figure ran about 40% high for a quarter. Nothing was broken and no row was wrong — there were simply more rows than orders, and the query never checked.

Quick check

1,000 orders joined to a shipments table where some orders shipped in two parts. The result has 1,150 rows. What is SUM(o.amount) over it?

An Inner Join Is Also a Filter

Now downwards. An inner join silently removes any row with no match on the other side.

Twelve users, ten subscriptions. Join them and you get ten rows — the two users with no subscription are not marked, not NULL, not flagged. They are gone, and nothing in the result mentions that they ever existed.

SELECT u.user_id, u.name
FROM users u
JOIN subscriptions s ON s.user_id = u.user_id;   -- 10 rows, not 12

That is often exactly right. "Orders and who placed them" should not include people who never ordered.

It is catastrophic when the missing rows are the question. "Every customer and what they have spent" is a report about the whole customer base, and an inner join hands you only the ones who spent something — removing precisely the people the report was commissioned to find.

Inner join is right

"Orders, with the buyer's name"

Every order has a buyer. Nothing can be dropped, because nothing is unmatched.

Inner join is wrong

"Every user, and what they spent"

The users who spent nothing vanish. The report says the average customer spends more than they do, and looks fine.

Keeping those rows — with NULLs where the other side had nothing — is what an outer join is for, and it is the whole of the next module. Before that, one question worth asking of every join you write: can either side have rows with no partner, and if so, do I want them?

Quick check

A dashboard shows "average revenue per customer" from an inner join of customers to orders. 500 customers exist; 300 have ordered. What is the average actually of?

0 of 9 completed

Loading the tables…

To do

orders knows a user_id and nothing else about the person. The name lives in users. A join lines the two tables up on the value they share.

SELECT o.order_id, u.name
FROM orders o
JOIN users u ON u.user_id = o.user_id;

Three parts: the second table, the word ON, and the condition that says which row over there belongs with which row over here. o and u are table aliases — short names so every column can say where it came from.

Your task: return order_id, the buyer's name, and amount for every order, ordered by order_id.

query.sql

SELECT
FROM orders o
JOIN users u ON
ORDER BY o.order_id;

PostgreSQL

Hint

SELECT o.order_id, u.name, o.amount FROM orders o JOIN users u ON u.user_id = o.user_id ORDER BY o.order_id;

Output

To do

Both tables have a user_id. Write it bare in a join and the database cannot tell which one you mean — ambiguous column name.

The habit worth forming is to qualify every column, not only the ambiguous ones. It costs two characters and it means a reader never has to hold the schema in their head to follow the query.

Your task: return o.order_id, u.name, u.country and o.status for every order — every column qualified with its table — ordered by order_id.

query.sql

SELECT
FROM orders o
JOIN users u ON u.user_id = o.user_id
ORDER BY o.order_id;

PostgreSQL

Hint

Prefix each one: o.order_id, u.name, u.country, o.status.

Output

To do

Nothing about a join cares which table you started from. The same orders table joins to products on product_id exactly as it joined to users.

Your task: return o.order_id, the product's name as product, and o.amount for every order, ordered by order_id.

query.sql

SELECT
FROM orders o
JOIN products p ON
ORDER BY o.order_id;

PostgreSQL

Hint

JOIN products p ON p.product_id = o.product_id — and alias the name column: p.name AS product.

Output

To do

A join is a cross product that has been filtered. The database pairs every row of one table with every row of the other, then keeps the pairs where the ON condition is true.

Leave the condition out and nothing is filtered. 12 users against 24 orders is 288 rows, every one of them a person paired with somebody else's order. It is not an error. It runs, and on real tables it runs for a very long time.

Your task: see it. Return COUNT(*) as pairs from users and orders with no join condition at all — using the comma form, FROM users, orders.

query.sql

-- No ON clause. Every user against every order.
SELECT
FROM users, orders;

PostgreSQL

Hint

SELECT COUNT(*) AS pairs FROM users, orders; — 12 times 24.

Output

To do

A join produces rows, so everything from Part 1 applies to them. The WHERE clause can test a column from either table.

Keep the two clauses honest: ON is how the tables relate, WHERE is which of the joined rows you want. Both happen to work for an inner join, and the difference becomes critical in the next module.

Your task: return o.order_id, u.name and o.amount for paid orders placed by users on the team plan, ordered by order_id.

query.sql

SELECT
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE
ORDER BY o.order_id;

PostgreSQL

Hint

WHERE o.status = 'paid' AND u.plan = 'team' — one condition from each table, which is perfectly normal.

Output

To do

Part 2 applies too. Join first, then group — the aggregate runs over the joined rows, so you can total a column from one table while grouping by a column from the other.

Your task: return each product category, the number of paid orders in it as n, and the total amount as revenue, biggest revenue first.

query.sql

SELECT
FROM orders o
JOIN products p ON p.product_id = o.product_id
WHERE
GROUP BY
ORDER BY

PostgreSQL

Hint

SELECT p.category, COUNT(*) AS n, SUM(o.amount) AS revenue ... WHERE o.status = 'paid' GROUP BY p.category ORDER BY revenue DESC;

Output

To do

A join on a key — a column that is unique in the table it points at — gives you back the same number of rows you started with. A join on anything else can give you more.

Every order matches exactly one product, so joining orders to products keeps 24 rows. Go the other way and one product matches several orders, so the products side is duplicated once per sale. That is fan-out, and it is the reason an aggregate over a join is sometimes several times too big.

Your task: prove the multiplication is not there when you join on a key. Return COUNT(*) as n from orders joined to products on product_id.

query.sql

SELECT
FROM orders o
JOIN products p ON p.product_id = o.product_id;

PostgreSQL

Hint

SELECT COUNT(*) AS n FROM orders o JOIN products p ON p.product_id = o.product_id; — still 24, because product_id is unique in products.

Output

To do

Because fan-out is invisible in the output, the habit that catches it is cheap: count the rows before and after a join. If the number went up and you did not expect it to, the aggregate you are about to write is already wrong.

Your task: join users to subscriptions on user_id and return COUNT(*) as n. Ten of the twelve users have a subscription and none has two, so predict the answer before you run it.

query.sql

SELECT
FROM users u
JOIN subscriptions s ON

PostgreSQL

Hint

SELECT COUNT(*) AS n FROM users u JOIN subscriptions s ON s.user_id = u.user_id; — an inner join keeps only the users that matched.

Output

To do

That last count is worth sitting with. An inner join silently removes any row with no match on the other side. Two users have no subscription, so they are gone — not marked, not NULL, gone.

This is right when you want "orders and who placed them". It is catastrophic when you want "every user and what they have spent", because the users who spent nothing are exactly the ones the report is about, and they vanish.

Your task: see which rows survive. Return u.user_id and u.name for users who do have a subscription, ordered by user_id, and note which two ids are missing.

query.sql

SELECT
FROM users u
JOIN subscriptions s ON s.user_id = u.user_id
ORDER BY u.user_id;

PostgreSQL

Hint

SELECT u.user_id, u.name FROM users u JOIN subscriptions s ON s.user_id = u.user_id ORDER BY u.user_id;

Output

The order report

To do

Support wants a readable list of recent purchases: who bought, what they bought, and for how much. At the moment they have three tables and a lot of id numbers.

Your task: join orders to both users and products, over paid orders only, and return exactly these five columns in this order:

order_id
buyer — the user's name
country — the user's country
product — the product's name
amount

Largest amount first, and order_id ascending where two amounts are equal. Qualify every column with its table.

There should be 21 rows. If you get more, a join condition is missing; if you get fewer, the status filter is doing more than it should.

query.sql

-- Three tables. Paid only. Dearest first, ties broken by order_id.
SELECT
FROM orders o
JOIN
JOIN
WHERE
ORDER BY

PostgreSQL

Hint

JOIN users u ON u.user_id = o.user_id, then JOIN products p ON p.product_id = o.product_id, then WHERE o.status = 'paid', then ORDER BY o.amount DESC, o.order_id.

Output

Sign in to open this module

INNER JOIN

ON Says Which Row Goes With Which

JOIN, INNER JOIN, and the comma

Quick check

What a Join Actually Does

Quick check

Fan-Out, and Why Totals Come Out Too Big

Joining to a primary key

Joining to a repeated column

Revenue that was 40% too high

Quick check

An Inner Join Is Also a Filter

"Orders, with the buyer's name"

"Every user, and what they spent"

Quick check

Two tables, one key

Say which table you mean

Join the other way

What happens without an ON

WHERE still works

Group what the join produced

When a join multiplies rows

Read the row count before you trust the number

An inner join is also a filter

The order report

Notification