Joining Multiple Tables

Real questions often span three, four or more tables: which customers bought which products, how much each category earned per country. Joins chain naturally — each JOIN adds one more table to the result. This lesson also covers the most common multi-join mistake: rows multiplying unexpectedly.

Chaining joins

SQL 
SELECT c.name AS customer, o.id AS order_id, p.name AS product, oi.quantity
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
JOIN products AS p ON p.id = oi.product_id
WHERE c.name = 'Ana Pereira'
ORDER BY o.id;
Result 6 rows
customerorder_idproductquantity
Ana Pereira1The Patient Gardener1
Ana Pereira1Ceramic Mug2
Ana Pereira3Learning SQL Step by Step1
Ana Pereira3USB-C Charger2
Ana Pereira11Desk Lamp1
Ana Pereira11Ceramic Mug1

Follow the keys: customer → orders → order items → products. Each JOIN connects the new table to one already in the query.

Summaries across many tables

SQL 
SELECT c.country, p.category,
       round(SUM(oi.quantity * oi.unit_price), 2) 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
JOIN products AS p ON p.id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY c.country, p.category
HAVING revenue > 80
ORDER BY revenue DESC;
Result 5 rows
countrycategoryrevenue
UKElectronics168.98
PortugalBooks154.9
FranceHome133
CzechiaElectronics119.98
UKHome98

Mixing inner and outer joins

SQL 
SELECT c.name, COUNT(DISTINCT o.id) AS orders, COALESCE(SUM(oi.quantity), 0) AS units
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
LEFT JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.id, c.name
ORDER BY units DESC, c.name;
Result 10 rows
nameordersunits
Ana Pereira38
Chloé Martin26
Ben Carter23
David Müller23
Elena García13
Farah Haddad13
George Brown23
Isaac Levi13
Hana Novak12
Julia Santos00

Once a LEFT JOIN introduces possible NULLs, the following joins usually need to be LEFT JOINs too. An inner join on oi here would remove Julia again, because her NULL order can't match any item.

The row multiplication trap

Suppose you want each customer's number of orders and total units:

SQL 
SELECT c.name, COUNT(o.id) AS orders_wrong
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 c.id = 1
GROUP BY c.name;
Result 1 row
nameorders_wrong
Ana Pereira6

Ana has three orders, not six. After joining order_items, each order appears once per item, so COUNT(o.id) counts item rows. Fixes include COUNT(DISTINCT o.id), or aggregating each table separately before joining:

SQL 
WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS orders FROM orders GROUP BY customer_id
), unit_counts AS (
  SELECT o.customer_id, SUM(oi.quantity) AS units
  FROM orders AS o JOIN order_items AS oi ON oi.order_id = o.id
  GROUP BY o.customer_id
)
SELECT c.name, oc.orders, uc.units
FROM customers AS c
JOIN order_counts AS oc ON oc.customer_id = c.id
JOIN unit_counts AS uc ON uc.customer_id = c.id
WHERE c.id = 1;
Result 1 row
nameordersunits
Ana Pereira38
Sanity check: before aggregating a multi-join query, run it without GROUP BY and look at the rows. If a value you're summing appears more than once, your totals will be inflated.

Join order and readability

For inner joins the order you write tables in doesn't change the result — the database's query planner picks the most efficient order anyway. Write joins in the order that tells the story: start from the main subject of the question and follow the relationships outward.

Try it yourself

Which employees work in a department located in the same city as at least one customer?

Try it yourself
Result
PreviousSelf Join