The INNER JOIN

Everything up to chapter 9 of the SQL for Beginners series has read one table at a time, but the interesting questions live between them: which customer placed which order, which items were in it, what did that cost. Chapter 10 supplies the answer with the INNER JOIN, which pairs rows from two tables wherever a condition matches. You will build it from the ground up: the cartesian product it avoids, the ON condition that drives it, joins across three and four tables, and joins meeting GROUP BY.

The problem joins solve

Write two table names in one FROM clause and SQLite happily produces every possible pairing — a cartesian product. Six customers with six products make thirty-six rows, none of which you asked for:

SELECT COUNT(*) AS pairs FROM customers, products;

Expected output:

pairs
-----
36

The comma join is a legitimate tool when you want it (and the CROSS JOIN keyword says so explicitly), but it is almost never what a beginner means. What you mean is “pair each customer with their own orders”, and that needs a condition saying which pairings count.

INNER JOIN ... ON

The condition lives in ON, written between the table and the join that follows it. Here each customer is matched to the orders whose customer_id equals that customer’s id:

SELECT c.first_name, c.last_name, o.id AS order_id, o.order_date, o.status
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY o.id;

Expected output:

first_name  last_name  order_id  order_date  status
----------  ---------  --------  ----------  ---------
Aarav       Sharma     1         2026-01-12  paid
Aarav       Sharma     2         2026-01-20  shipped
Priya       Nair       3         2026-02-03  cancelled
Lena        Fischer    4         2026-02-11  shipped
Wei         Ling       5         2026-02-25  paid
Amara       Ochieng    6         2026-03-02  paid

Six rows, and three things are worth saying out loud. INNER is optional — JOIN alone means inner. The table names AS c and AS o are aliases, the subject of chapter 12, and they let c.first_name say exactly which first_name is meant. And look for Marc: he has no orders, so he has no pairing, so he is absent from the result. An inner join keeps only matched rows on both sides — that is its definition and its most common surprise. Chapter 11 fixes it.

WHERE joins in filtering

Once rows are paired, the WHERE you already know filters the pairs exactly as it filters a single table. Chaining conditions with AND mixes join logic and filters safely:

SELECT c.first_name, o.id AS order_id, o.status
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped'
ORDER BY o.id;

Expected output:

first_name  order_id  status
----------  --------  -------
Aarav       2         shipped
Lena        4         shipped

For an inner join, moving the condition between ON and WHERE gives the same rows, so pick whichever reads better — conditions about the join belong in ON, conditions about what you want to report belong in WHERE. That freedom quietly disappears for outer joins, where the position of a condition decides whether whole rows survive, and the difference is the heart of chapter 11.

The equivalence is worth proving once rather than assuming: move o.status = 'shipped' up into the ON clause, run both versions, and confirm the result set is identical. With an inner join it always will be, because a pair that fails the condition is dropped either way.

Joining three or more tables

Add a table by adding a join, each with its own ON. The chain follows the foreign keys: items belong to orders, orders to customers, items to products — so one cancelled order becomes a sentence a human can read:

SELECT c.first_name, p.name, oi.quantity, oi.unit_price
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'cancelled';

Expected output:

first_name  name                 quantity  unit_price
----------  -------------------  --------  ----------
Priya       Python Crash Course  1         749

Three joins, three conditions, one row — because order 3 holds exactly one item. Read the FROM clause from top to bottom: start with the finest-grained table (each row is one item), then attach the tables that give those rows meaning. Nothing requires foreign keys to be declared for a join to work, but when they are, the column names tell you where each ON should point.

Join order is more flexible than it looks: reorder those three JOIN lines and the same rows come back, so pick the sequence in which every ON condition reads like a sentence. What must not be missing is a condition somewhere — a chain in which one link is left unattached reintroduces the cartesian product from the start of this chapter, and the row count doubles without any error to warn you.

Joins with GROUP BY

Join first, aggregate second. Once every item row carries its order id, the aggregate functions from chapter 6 work straight down the joined rows to total each order:

SELECT o.id AS order_id, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id
ORDER BY o.id;

Expected output:

order_id  total
--------  -----
1         997
2         899
3         749
4         1347
5         649
6         1798

Order 1 is 1 × 499 + 2 × 249 = 997, and the pattern generalises to any report you will ever write: join the tables, group by the thing you are reporting on, aggregate the number you care about. One caution before you reuse that query — a one-to-many join gives an order one row per item, so COUNT(*) would count items, not orders. Chapter 11 shows the pair of counters that tells them apart.

What an inner join drops

Because an inner join keeps only matched pairs, it is worth knowing exactly what disappeared. Two counts settle it:

SELECT COUNT(*) AS joined_rows FROM customers c JOIN orders o ON o.customer_id = c.id;
SELECT COUNT(*) AS orders FROM orders;

Expected output:

joined_rows
-----------
6
orders
------
6

Every one of the six orders survived the join, so no order was lost — but the join produced six rows against six customers, and the missing customer is Marc, who has never ordered. The same logic cuts the other way: an order whose customer_id pointed at a nonexistent customer would vanish too, which is exactly why chapter 9’s audit query had to reach for a different kind of join. Whenever an inner join returns fewer rows than you expected, count the two sides separately before you blame your ON condition.

Common mistakes

• Forgetting the ON condition — FROM customers JOIN orders is a cartesian product of every pairing; the count is 36, not 6.
• Comparing columns of the same name by habit — o.customer_id = c.id, not c.id = o.id; matching names are a convention, not a rule.
• Filtering with the wrong table’s column — WHERE status = 'shipped' with a typoed or ambiguous column either errors or filters the wrong side.
• Expecting unmatched customers to appear — inner joins drop them by definition; Marc’s absence is correct behaviour, not a bug (chapter 11 keeps him).
• Counting joined rows as if they were orders — one-to-many joins multiply rows; use COUNT(DISTINCT ...) when you mean “how many orders”.
• Joining a table to itself by repeating its name — the second copy needs its own alias, which chapter 12 demonstrates.

Practice checklist

1. Write the comma join above with SELECT c.first_name, p.name FROM customers, products and LIMIT 5, then rewrite it as a join on a real condition.
2. Join customers to orders and print Marc’s name — watch it fail to appear, then explain why in one sentence.
3. List every paid order with the customer’s full name, ordered by order_date.
4. Total each customer’s spending by joining all four tables and grouping by customer.
5. Reproduce the order totals above and check the arithmetic for order 4 by hand.

What's next?

Inner joins pair rows that match, which is exactly the wrong tool when you also need the rows that do not. Chapter 11 keeps the unmatched ones in Outer Joins and Join Pitfalls.