Joining three or more tables

Step 1 Understand it

Why you need it. "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.

Watch it happen

Picture it

flowchart LR
  C["customers"] ---|"customer_id"| O["orders"]
  O ---|"order_id"| OI["order_items"]
  OI ---|"product_id"| P["products"]

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).

The shape of it

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;

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

SELECT o.order_id, c.name AS customer, p.product_name, oi.quantity
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
WHERE o.order_date < '2024-01-22'
ORDER BY o.order_id, p.product_name;

SELECT p.category, SUM(oi.quantity * p.price) AS revenue
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

SELECT DISTINCT c.name
FROM customers c
JOIN orders o       ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE p.product_name = 'Headphones'
ORDER BY c.name;

Must-know 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).

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  CS50's Introduction to Databases with SQL — Lecture 1: Relating

  Harvard, free

• Read

  PostgreSQL docs — Joins Between Tables

  official documentation, free

• Practice

  SQLZoo — More JOIN operations

  free exercises

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain what happens to the number of rows after each join in the first example.

   Show answer

   Count the rows after each step:

   1. orders: 10 rows.
   2. JOIN customers: still 10. Each order has exactly one customer, so nothing is added or lost.
   3. JOIN order_items: 14. Each order is repeated once per item line (order 102 has two lines, so it now appears twice), and order 106, which has no items, is dropped.
   4. JOIN products: still 14. Each item line has exactly one product.
   5. WHERE order_date < '2024-01-22': 6 rows, the item lines of orders 101 to 104.

   The rule: joining to the "one" side keeps the count; joining to the "many" side multiplies it; an INNER JOIN with no match removes rows.

   sqlCopy

   SELECT (SELECT COUNT(*) FROM orders) AS orders_only,
          (SELECT COUNT(*) FROM orders o
             JOIN order_items oi ON oi.order_id = o.order_id) AS with_items,
          (SELECT COUNT(*) FROM orders o
             JOIN order_items oi ON oi.order_id = o.order_id
             WHERE o.order_date < '2024-01-22') AS after_where;

   Try it

   1 row

   orders_only	with_items	after_where
   10	14	6

2. Writefrom a blank page, then run it

   Write a query for the total quantity of each product bought by Priya, with product names, sorted by quantity.

   Show answer

   Walk the keys from customers to orders to order_items to products, keep Priya, then group by product and add up the quantities. Mug, Notebook and Pen tie on 5, so the name breaks the tie.

   sqlCopy

   SELECT p.product_name, SUM(oi.quantity) AS total_quantity
   FROM customers c
   JOIN orders o       ON o.customer_id = c.customer_id
   JOIN order_items oi ON oi.order_id = o.order_id
   JOIN products p     ON p.product_id = oi.product_id
   WHERE c.name = 'Priya'
   GROUP BY p.product_id, p.product_name
   ORDER BY total_quantity DESC, p.product_name;

   Try it

   6 rows

   product_name	total_quantity
   Headphones	6
   Mug	5
   Notebook	5
   Pen	5
   Backpack	2
   Desk lamp	2

3. Predictsay it before you run it

   How many rows do you get from joining orders to order_items (all orders, INNER JOIN)? Which order is missing?

   Show answer

   14 rows: one per item line, not one per order. They cover 9 of the 10 orders. Order 106 is missing, because it has no items, so the INNER JOIN finds nothing to pair it with.

   sqlCopy

   SELECT COUNT(*) AS item_rows, COUNT(DISTINCT o.order_id) AS orders
   FROM orders o
   JOIN order_items oi ON oi.order_id = o.order_id;

   Try it

   1 row

   item_rows	orders
   14	9

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Rewrite the first example (without the date filter) so order 106, which has no items, still appears.

   Show answer

   Make the join to order_items a LEFT JOIN, and the join after it (products) a LEFT JOIN too. If products stayed an INNER JOIN, order 106's NULL product_id would match no product, and the row would be dropped again. Once a chain goes LEFT, every join after it must stay LEFT.

   The full query returns 15 rows (14 item lines plus 106). The WHERE below only keeps the result short enough to read; delete it to see all 15.

   sqlCopy

   SELECT o.order_id, c.name AS customer, p.product_name, oi.quantity
   FROM orders o
   JOIN customers c         ON c.customer_id = o.customer_id
   LEFT JOIN order_items oi ON oi.order_id = o.order_id
   LEFT JOIN products p     ON p.product_id = oi.product_id
   WHERE o.order_date >= '2024-02-01'
   ORDER BY o.order_id, p.product_name;

   Try it

   9 rows

   order_id	customer	product_name	quantity
   105	Priya	Backpack	1
   105	Priya	Headphones	3
   106	Lena	NULL	NULL
   107	Lena	Backpack	2
   108	Tom	Desk lamp	3
   109	Priya	Notebook	4
   109	Priya	Pen	5
   110	Zoe	Desk lamp	1
   110	Zoe	Mug	1

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

For multi-table queries I follow the foreign keys one hop at a time, adding a JOIN with its own ON condition per table, and 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.

Follow-up questions

Tick each check when you can do it without notes