Where is the customer’s name?
Ops wants a simple report: every order, who placed it, and how much it was for. Harry opens the orders table to pull it together.
Where is the customer’s name? All I see is an ID.
Because it’s not in this table. orders only knows customer_id — a number pointing at a row somewhere else.
Okay, so why not just add a name column to orders and be done with it?
You could. But now imagine Alice places 4 orders, and her name is copied into all 4 rows. Then she gets married and updates her name — do you catch every copy? What if one order has "Alice Sharma" and another has a typo? Now the same customer looks like two different people depending on which row you read.
So instead you just... store the ID, and look the name up when you need it.
Exactly. One customer, one row, one true spelling of their name — in customers. orders just remembers which customer it belongs to.
customers.id is a primary key: a column that uniquely identifies one row in that table, and never repeats. No two customers share an id, and every customer has exactly one.
orders.customer_id is a foreign key: a column that stores the primary key of a row in a different table. It’s how orders says “this row belongs to that customer” without having to store anything else about them.
This is exactly why NOVA’s data lives in separate tables in the first place: each fact is stored once, in the table it actually belongs to, and tables reference each other by key instead of duplicating each other’s columns.
Which rows belong together across tables?
Here’s what Harry actually has to work with — just the raw orders rows, no names in sight:
SELECT id, customer_id, product_id, amount FROM orders
ORDER BY id
LIMIT 3;| id | customer_id | product_id | amount |
|---|---|---|---|
| 1 | 1 | 1 | 79.99 |
| 2 | 1 | 6 | 49.98 |
| 3 | 1 | 8 | 89 |
A JOIN lines up rows from two tables using a condition you specify with ON. Here, “these rows belong together” means the foreign key on one side matches the primary key on the other:
SELECT orders.id, customers.name, orders.amount
FROM orders
JOIN customers ON orders.customer_id = customers.id
ORDER BY orders.id
LIMIT 3;| id | name | amount |
|---|---|---|
| 1 | Alice Sharma | 79.99 |
| 2 | Alice Sharma | 49.98 |
| 3 | Alice Sharma | 89 |
Read the ON clause as a question, asked for every possible pairing of an order row and a customer row: “does this order’s customer_id equal this customer’s id?” Only the pairs that answer “yes” make it into the result — that’s why this is called an INNER JOIN (the default kind, so the word INNER is optional and usually left out).
Running…
22 rows — one per order, each one now carrying its customer’s actual name. Notice orders and customers both have a column named id. Writing orders.id instead of just id is how you tell SQL which table you mean whenever a column name could be ambiguous.
Try it: What happens if you leave out the ON condition entirely — say, FROM orders, customers?
Run your query to see results here.
It runs — and comes back with 264 rows. That’s 22 orders times 12 customers: every single order paired with every single customer, whether they have anything to do with each other or not. This is called a cross join. Without an ON condition, SQL has no idea which rows “belong together” — so it gives you all of them, matched or not. The ON clause isn’t decoration; it’s the entire point of the join.
Once a query has two or three tables in it, typing the full table name in front of every column gets tedious. You can give a table a short alias right in the FROM clause and use that instead:
SELECT o.id, c.name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.id
ORDER BY o.id;Same query, same 22 rows — just less typing.
Ops has a follow-up: they only care about orders that actually completed — no point chasing customers about cancelled or refunded ones. Get the customer’s name, the order id, the amount, and the status, for every completed order, with the biggest orders first.
Hint 1
Start from the join you already have — orders joined to customers on the customer_id / id pair.
Hint 2
You still have WHERE from chapter 2. It runs after the join, so you can filter on orders.status just like any other column.
Hint 3
SELECT customers.name, orders.id, orders.amount, orders.status FROM orders JOIN customers ON orders.customer_id = customers.id WHERE orders.status = 'completed' ORDER BY orders.amount DESC;
Solution
Running…
Everything after FROM orders JOIN customers ON ... works exactly like it always has — WHERE still filters rows, ORDER BY still sorts them. The join just gives each row more columns to filter and sort by, pulled in from a table it doesn’t actually live in.
A primary key uniquely identifies a row in its own table. A foreign key stores another table’s primary key, to point at the row it belongs to. JOIN ... ON reunites them: it pairs rows across tables wherever the foreign key on one side matches the primary key on the other.