Module 4: Joins · Lesson 4.2 · about 25 min
INNER JOIN
An inner join glues each row of one table to its matching rows in another, and drops rows with no match. Watch it pair orders with customers row by row, then join three tables on Northbay.
Module 4 · about 25 minutes · read first: 4.1. You’ll be able to write an inner join, predict which rows survive it, and join three or four tables to answer a real business question.
In everyday terms: matching coats to cloakroom tickets. Each ticket number finds its coat; a ticket with no coat, or a coat with no ticket, is left out of the pile you hand back.
1. Why this matters
Almost no real question lives in one table. “Revenue by store” needs orders and stores; “revenue by department” needs order lines, products and categories. The join is the tool that answers them, and the inner join is the one you will write most often. It is also where quiet mistakes start, so it pays to know exactly which rows it keeps.
2. The idea: pair rows whose keys match
An inner join takes two tables and, for every pair of rows where the join condition is true, makes one combined row. Rows with no partner are dropped. The join condition goes after ON and almost always says “this foreign key equals that primary key”.
Press Run, then Show how it runs and Play. Watch each order look for its customer.
-- try
SELECT o.order_id, o.item, c.name, c.city
FROM mini_orders o
JOIN mini_customers c ON o.customer_id = c.id
Things to notice:
- Asha (id 1) has two orders, so she appears twice. A join repeats a row once for every match.
- Order 106 points at customer 9. There is no customer 9, so the order disappears.
- Dina and Eli have no orders, so they disappear too.
JOIN on its own means INNER JOIN; both spellings work everywhere. The letters o and c are aliases: short nicknames so you can write o.order_id instead of mini_orders.order_id. Once a query has two tables, prefix every column with its alias, even when the name is unique. Readers (and you, next month) will thank you.
- JOIN ... ON a.k = b.k
- Pair rows whose keys are equal and put their columns side by side.
- INNER JOIN
- The same as JOIN: keep only pairs that match.
- Join condition
- The test after ON, usually "this foreign key equals that primary key".
- Alias
- A short nickname for a table, such as o for mini_orders, used as o.customer_id.
- USING (k)
- Short for ON a.k = b.k when both columns have the same name.
- Physical meaning
- Each left row is copied once for every matching right row. Rows with no match on either side disappear without a warning, so the result can have fewer rows than you started with.
“A join just adds columns, so the row count stays the same.” An inner join drops every row without a partner and repeats a row once per match. Order 106 vanishes because customer 9 doesn’t exist; Northbay’s 1,059 online orders vanish when you join to regions. Count rows before and after every join.
Watch the join above run, one step at a time (the live box’s Show how it runs button animates the real query):
| order_id | customer_id | item |
|---|---|---|
| 101 | 1 | Kettle |
| 102 | 1 | Bagels |
| 103 | 2 | Puzzle |
| 104 | 3 | Duvet |
| 105 | 3 | Kettle |
| 106 | 9 | Speaker |
| id | name |
|---|---|
| 1 | Asha |
| 2 | Ben |
| 3 | Chen |
| 4 | Dina |
| 5 | Eli |
| order_id | item | name |
|---|---|---|
| 101 | Kettle | Asha |
| 102 | Bagels | Asha |
| 103 | Puzzle | Ben |
| 104 | Duvet | Chen |
| 105 | Kettle | Chen |
3. When the columns share a name: USING
If both tables call the key column the same thing, USING is a shorter way to say it. In Northbay, orders.store_id and stores.store_id match:
-- try
SELECT s.store_name, COUNT(*) AS orders
FROM orders o
JOIN stores s USING (store_id)
GROUP BY s.store_name
ORDER BY orders DESC
USING (store_id) means ON o.store_id = s.store_id. Northbay Online is far ahead with 1,059 orders; the busiest shop, Northbay Bytown in Ottawa, has 310. SQL Server does not support USING, so write the ON version there.
4. Joining three or more tables
Each extra JOIN adds one more table to the rows built so far. To get revenue by department you walk the pointers: order line → product → category.
-- try
SELECT c.department,
ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
GROUP BY c.department
ORDER BY revenue DESC
Press Show how it runs to see the second join add the department to rows that already have the product. Grocery leads with $49,790.69.
A habit worth building: before you add a join, ask “how many rows on the right will match each row on the left?” If the answer is “exactly one” (many lines to one product), the row count stays the same. If it could be more than one, rows will multiply; that is Lesson 4.5.
Maths and principle
Principle: a join is a filtered pairing. Take every possible pair of rows, one from each table, and keep the pairs where the condition is true.
Equation: A ⋈_θ B = σ_θ(A × B). The Cartesian product A × B has |A| · |B| pairs; the selection σ_θ keeps those that satisfy θ (here o.customer_id = c.id). For mini_orders and mini_customers that is 6 × 5 = 30 pairs, of which 5 survive.
Analogy: a school dance. Every student from class A could stand with every student from class B, but only pairs holding matching ticket numbers get onto the floor. Anyone whose number nobody else holds sits out.
Try it: if every order matched exactly one customer, how many rows would the join have? (Exactly as many as there are orders: each order pairs once.) Databases never really build all 30 pairs; they use the condition to jump straight to matches, but the answer is the same.
5. Worked example: orders by region
Question: how many orders did each sales region take?
Regions live in regions, and orders point at stores, which point at regions. So the path is orders → stores → regions:
-- try
SELECT r.region_name, COUNT(*) AS orders
FROM orders o
JOIN stores s ON s.store_id = o.store_id
JOIN regions r ON r.region_id = s.region_id
GROUP BY r.region_name
ORDER BY orders DESC
Ontario South has 1,364, Prairies 835, Ontario North 529 and West Coast 277. Add them up: 3,005. But Northbay has 4,064 orders. Where did 1,059 go? They are the online orders: store 12 has no region (region_id is NULL), so the inner join dropped every one of them, with no warning. The numbers look fine; a quarter of the business is missing. Lesson 4.3 shows how to keep them.
6. Practice
-- exercise: List every mini order with the customer's name and city, but only for customers in Toronto.
-- hint: Join mini_orders to mini_customers on customer_id = id, then add WHERE c.city = 'Toronto'.
SELECT o.order_id, o.item
FROM mini_orders o
-- answer:
SELECT o.order_id, o.item, c.name, c.city
FROM mini_orders o
JOIN mini_customers c ON o.customer_id = c.id
WHERE c.city = 'Toronto'
-- exercise: Count completed orders per province. Join orders to stores and group by the store's province.
-- hint: JOIN stores s ON s.store_id = o.store_id, WHERE o.status = 'completed', GROUP BY s.province.
SELECT store_id, COUNT(*) AS orders
FROM orders
GROUP BY store_id
-- answer:
SELECT s.province, COUNT(*) AS orders
FROM orders o
JOIN stores s ON s.store_id = o.store_id
WHERE o.status = 'completed'
GROUP BY s.province
-- exercise: Show revenue per product category name, biggest first, rounded to 2 decimal places.
-- hint: order_items → products → categories. Revenue is quantity × unit_price × (1 − discount).
SELECT oi.product_id, SUM(oi.quantity) AS units
FROM order_items oi
GROUP BY oi.product_id
-- answer:
SELECT c.category_name,
ROUND(SUM(oi.quantity * oi.unit_price * (1 - oi.discount)), 2) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
JOIN categories c ON c.category_id = p.category_id
GROUP BY c.category_name
ORDER BY revenue DESC
Try it on your own data: two CSV files with a shared id column? Join them in [[sql-playground]], or line them up without SQL in [[merge-csv]].
- JOIN (INNER JOIN) keeps only pairs of rows whose join condition is true.
- A row is repeated once per match and dropped if it has none, on either side.
- Give tables aliases and prefix every column with one.
- USING (k) is a short form of ON when both key columns share a name (not in SQL Server).
- Before each join, ask how many right rows match each left row, and count rows before and after.
New words: inner join, join condition, alias, USING, Cartesian product.
Check yourself
Sources
- SQLite documentation, SELECT: The FROM clause and joins https://www.sqlite.org/lang_select.html#fromclause
- PostgreSQL documentation, Joins Between Tables https://www.postgresql.org/docs/current/tutorial-join.html
- Microsoft Learn, Joins (SQL Server) https://learn.microsoft.com/en-us/sql/relational-databases/performance/joins
- Beaulieu, A. (2020), Learning SQL, 3rd edition, O'Reilly, chapter 5 (Querying Multiple Tables)
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.