Joining three or more tables
You join more than two tables by adding one JOIN … ON for each table, following the keys: orders to customers, orders to order_items, order_items to products. Each result row can then hold columns from all of them.
Why it matters
"What did each customer buy?" needs the customer's name (customers), the order (orders), the line items (order_items) and the product names (products). That is four tables. Many-to-many relationships always need at least three.
The idea
Each JOIN adds one table to the rows you have so far, so a chain of joins follows the arrows in the database diagram. Start from the table whose grain you want in the result — here, one row per item bought, so order_items — or from the table you must keep in full.
Each new JOIN needs its own ON condition linking it to a table already in the query. GROUP BY afterwards sums across all of them, for example revenue per category = SUM(quantity × price).
Following a paper trail. A receipt line says "product 6, quantity 3, order 101". You look up order 101 to find the customer, then customer 1 to find the name, then product 6 to find "Headphones". Each lookup is one join.
On paper you choose which lookups to do. SQL does every join for every row, and an INNER JOIN anywhere in the chain drops rows that fail to match at that step — like order 106, which has no items.
Syntax
SELECT ...
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id;How it works
- FROM orders o: one row per order.
- JOIN customers c: each order gains its customer's columns. Still one row per order.
- JOIN order_items oi: an order with 2 items becomes 2 rows. The grain is now one row per item.
- JOIN products p: each item row gains the product name and price.
- Order 106 has no items, so the INNER JOIN to order_items drops it. Use LEFT JOINs if it must stay.
Key points
- One JOIN … ON per extra table; each ON links the new table to one already joined.
- Follow the keys in the diagram. Many-to-many goes through the link table.
- Know the grain after every join: joining a "many" table multiplies rows.
- One INNER JOIN in a chain can drop rows; switch to LEFT JOIN where rows must survive.
- Use clear aliases (c, o, oi, p) and line the ON conditions up so the chain is easy to read.
Common mistakes
- Joining two tables on the wrong pair of columns (
oi.product_id = o.order_id) — it runs but returns nonsense. - Mixing LEFT and INNER joins so that a later INNER JOIN removes the rows the LEFT JOIN kept.
- Summing an order-level column (o.amount) after joining item rows, which counts it once per item (see Join traps).
Not true: A join on the wrong pair of columns always stops with an error.
In fact: If the types fit, it runs and returns nonsense: oi.product_id = o.order_id happily pairs unrelated rows. Check each ON against the keys in the diagram.
In the interview
For multi-table queries I follow the foreign keys one hop at a time, adding a JOIN with its own ON condition for each table. I keep track of the grain after each join: joining a child table like order_items turns one row per order into one row per item. For many-to-many I go through the junction table, and I use LEFT JOINs wherever rows without a match must survive.
Part of Unit 3: Joins: combining tables in the free SQL course.