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

  1. FROM orders o: one row per order.
  2. JOIN customers c: each order gains its customer's columns. Still one row per order.
  3. JOIN order_items oi: an order with 2 items becomes 2 rows. The grain is now one row per item.
  4. JOIN products p: each item row gains the product name and price.
  5. Order 106 has no items, so the INNER JOIN to order_items drops it. Use LEFT JOINs if it must stay.

Key points

Common mistakes

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.