Joining three or more tables

Following the keys

The question "how much has each customer spent on each category?" touches four tables. The customer's name is in customers, the order status in orders, the quantity and price paid in order_items, and the category in products. Before writing SQL, draw the path between them using the foreign keys.

customers.id  <--  orders.customer_id
orders.id     <--  order_items.order_id
products.id   <--  order_items.product_id

A query with many joins is just this path written down. Each JOIN adds one table and one ON rule that connects it to a table already in the query.

Build it step by step

Do not write a five-table query in one go. Add one table, run it, look at the rows, and only then add the next. If the row count jumps unexpectedly, you know which join caused it.

-- Step 1: orders with customer names (8 rows)
SELECT o.id, c.name
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;

-- Step 2: add the order lines (12 rows: one per line now)
SELECT o.id, c.name, oi.product_id, oi.qty, oi.price
FROM orders AS o
JOIN customers AS c    ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id;

-- Step 3: add product names and categories (still 12 rows)
SELECT o.id, c.name, p.name AS product, p.category, oi.qty * oi.price AS line_total
FROM orders AS o
JOIN customers AS c    ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p     ON p.id = oi.product_id;

The grain of a result means "what one row stands for". After step 1 one row is one order; after step 2 it is one order line. Always know the grain, because it decides what SUM and COUNT will mean.

Revenue per customer

Now add WHERE and GROUP BY exactly as in module 4. The joins run first (they are part of FROM), then WHERE filters the joined rows, then GROUP BY makes one row per group.

SELECT c.name, SUM(oi.qty * oi.price) AS revenue
FROM customers AS c
JOIN orders AS o       ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
WHERE o.status = 'Delivered'
GROUP BY c.id, c.name
ORDER BY revenue DESC;
-- Ravi Kumar | 6497
-- Asha Verma | 4028

Revenue per category

SELECT p.category, SUM(oi.qty * oi.price) AS revenue
FROM order_items AS oi
JOIN orders AS o   ON o.id = oi.order_id
JOIN products AS p ON p.id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY p.category
ORDER BY revenue DESC;
-- Fashion     | 5196
-- Electronics | 3998
-- Grocery     | 2750
-- Kitchen     | 1900

Does the order of JOINs matter?

For inner joins, no: the database's query planner (the part that decides how to run a query) reorders them for speed, and the result is the same. With LEFT JOINs the order does matter, because "keep every row of the left side" depends on what is on the left. A common trap: customers LEFT JOIN orders, then INNER JOIN order_items. The inner join drops the NULL rows of customers without orders, so Arjun disappears again. Once you start a LEFT JOIN chain, keep the following joins LEFT too.

Common mistakes

• Skipping a table on the path, such as joining customers straight to order_items. There is no shared key; you must go through orders.
• Losing track of the grain and counting order lines when you meant orders. Use COUNT(DISTINCT o.id) when the grain is lines.
• Mixing a LEFT JOIN with a later INNER JOIN and losing the unmatched rows. Make the later joins LEFT JOINs too.
• Writing all the joins at once and not knowing which one is wrong. Build step by step and check the row count.