JOINs on three or more tables

In Module 3 you joined two tables. In the real world data is almost always scattered across three or more tables linked by foreign keys: to answer a business question you have to traverse the chain of relationships. That's exactly what a multi-table JOIN does.

From this module on we'll use the ecommerce dataset (customers, categories, products, orders, order items, reviews). You can explore it from the Playground by selecting ecommerce from the dataset picker.

The syntax: a chain of JOINs

SELECT colonne
FROM   tabella_a
JOIN   tabella_b ON tabella_a.x = tabella_b.y
JOIN   tabella_c ON tabella_b.z = tabella_c.w
[JOIN  tabella_d ON …]
…;

Each JOIN adds a "link" to the chain: the engine takes the intermediate result of the previous JOINs and attaches the next table to it.

-- Per ogni riga d'ordine: cliente, prodotto e quantità.
SELECT c.email,
       p.name AS prodotto,
       oi.quantity
FROM   orders      AS o
JOIN   customers   AS c  ON o.customer_id = c.id
JOIN   order_items AS oi ON oi.order_id   = o.id
JOIN   products    AS p  ON oi.product_id = p.id;

Three JOINs, four tables. The result has one row per order_items — each product in each order — enriched with customer email and product name.

The logical order

The engine joins tables from left to right. A JOIN condition can reference any table already introduced above. For readability:

• put the "main table" of the question first (here orders);
• add the others in the order in which you visit them by following the relationships.

Aggregates over JOIN chains

Multi JOINs combine with GROUP BY to produce reports:

-- Spesa totale per cliente (solo ordini consegnati):
SELECT c.email,
       SUM(oi.quantity * oi.unit_price) AS total
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.email
ORDER BY total DESC;

You don't need JOIN products here: everything you need (quantity, unit_price) is already in order_items. Rule of thumb: don't add tables you don't use — they hurt performance and readability.

Try it yourself

Review exercise