Chapter 5 · Combining Tables
Joining Many Tables, and the Duplicate-Row Trap
- Page 18 of 22
- 15 min read
Real questions rarely live in two tables. "Revenue per category" needs order items (quantities and prices), products (which category) and categories (the name), and usually orders too (to leave out cancelled ones). You answer it by chaining joins: each new JOIN adds one more table to the rows you already have.
This is also where joins start to give wrong numbers silently. Join an order to its items and to its payments in the same query and every total can double. Feature tables for machine learning are built exactly this way — one row per customer from five tables — so a fan-out bug here becomes a bug in the model's training data, with no error anywhere.
What you will learn
- Joining three, four and five tables by following the foreign keys
- Join conditions with more than one test
- Joins with
GROUP BY: revenue per category, products per customer - Many-to-many through
order_items, including "bought together" pairs - The fan-out trap that double-counts order 6, and how to fix it
Follow the keys
Before writing a multi-table join, find the path between the tables you need. In BitByte Shop it looks like this (─< means "one to many"):
customers ─< orders ─< order_items >─ products >─ categories
│
└─< paymentsTo get from a customer to a category name you walk customers → orders → order_items → products → categories, one join per arrow, each joining a foreign key to the primary key it references.
Three or more tables
Each JOIN takes the rows built so far and joins one more table onto them:
SELECT o.order_id, c.name AS customer, p.name AS product, oi.quantity, oi.unit_price
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_id IN (1, 4, 9)
ORDER BY o.order_id, p.product_id;+----------+---------------+---------------------------+----------+------------+
| order_id | customer | product | quantity | unit_price |
+----------+---------------+---------------------------+----------+------------+
| 1 | Nadia Rahman | Python Crash Course | 1 | 1200 |
| 1 | Nadia Rahman | Wireless Mouse | 1 | 900 |
| 4 | Farhana Akter | Hands-On Machine Learning | 1 | 2500 |
| 4 | Farhana Akter | Laptop Stand | 1 | 1500 |
| 9 | Imran Hossain | Mechanical Keyboard | 1 | 4050 |
| 9 | Imran Hossain | Wireless Mouse | 1 | 900 |
+----------+---------------+---------------------------+----------+------------+- The grain of this result — what one row stands for — is one order item. Orders repeat once per item, and the customer repeats with them. Always know the grain of your join before you aggregate it.
- Every
ONmay use any table joined before it:p.product_id = oi.product_idworks becauseoiis already there. - For inner joins the order you write them in does not change the result, and the database's planner picks its own order anyway. Write them in the order of the key path so a reader can follow.
Joins with aggregates: revenue per category
Revenue is quantity × unit_price from order_items (the price actually charged, not the list price). Four tables, one GROUP BY:
SELECT cat.name AS category,
SUM(oi.quantity) AS units,
SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY cat.category_id, cat.name
ORDER BY revenue DESC;+-------------+-------+---------+
| category | units | revenue |
+-------------+-------+---------+
| Electronics | 8 | 31550 |
| Courses | 3 | 21000 |
| Accessories | 5 | 8900 |
| Books | 5 | 8600 |
+-------------+-------+---------+The grain before grouping is one order item, and each item belongs to exactly one product and one category, so nothing is counted twice. The cancelled order 5 and the pending order 13 are filtered out by the status test on orders — that is why orders is in the query even though none of its columns are selected.
More than one join condition
ON can hold any condition, joined with AND. Which items were sold below the product's list price?
SELECT oi.order_id, p.name, p.price AS list_price, oi.unit_price
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id AND oi.unit_price < p.price
ORDER BY oi.order_id;+----------+---------------------+------------+------------+
| order_id | name | list_price | unit_price |
+----------+---------------------+------------+------------+
| 9 | Mechanical Keyboard | 4500 | 4050 |
+----------+---------------------+------------+------------+Only order 9 got a discount. For an inner join, the extra test could equally go in WHERE; for a LEFT JOIN it must stay in ON, as the previous page showed. Composite keys are the other common case: when a table's key is two columns, such as order_items (order_id, product_id), a join to it needs both: ON x.order_id = oi.order_id AND x.product_id = oi.product_id.
Many-to-many through order_items
A customer buys many products; a product is bought by many customers. order_items (through orders) is the junction table that connects them:
SELECT c.name AS customer, p.name AS product, SUM(oi.quantity) AS units
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.customer_id IN (1, 3)
GROUP BY c.customer_id, c.name, p.product_id, p.name
ORDER BY c.customer_id, p.product_id;+---------------+---------------------------+-------+
| customer | product | units |
+---------------+---------------------------+-------+
| Nadia Rahman | Python Crash Course | 1 |
| Nadia Rahman | Wireless Mouse | 1 |
| Nadia Rahman | 27-inch Monitor | 1 |
| Nadia Rahman | SQL Masterclass | 1 |
| Farhana Akter | Python Crash Course | 2 |
| Farhana Akter | Hands-On Machine Learning | 1 |
| Farhana Akter | Wireless Mouse | 1 |
| Farhana Akter | Laptop Stand | 1 |
| Farhana Akter | USB-C Hub | 1 |
+---------------+---------------------------+-------+This customer × product table is exactly the "interaction matrix" a recommender system starts from: who bought what, and how much.
Bought together: joining order_items to itself
Which pairs of products appear in the same order? Join order_items to itself on the same order_id. The extra condition a.product_id < b.product_id keeps each pair once (and stops a product pairing with itself):
SELECT pa.name AS product_a, pb.name AS product_b, COUNT(*) AS times_together
FROM order_items a
JOIN order_items b ON a.order_id = b.order_id AND a.product_id < b.product_id
JOIN products pa ON pa.product_id = a.product_id
JOIN products pb ON pb.product_id = b.product_id
GROUP BY a.product_id, b.product_id, pa.name, pb.name
ORDER BY times_together DESC, a.product_id, b.product_id
LIMIT 3;+---------------------------+-----------------+----------------+
| product_a | product_b | times_together |
+---------------------------+-----------------+----------------+
| Wireless Mouse | USB-C Hub | 2 |
| Python Crash Course | Wireless Mouse | 1 |
| Hands-On Machine Learning | SQL Masterclass | 1 |
+---------------------------+-----------------+----------------+The mouse and the USB-C hub were bought together twice (orders 7 and 14). On real data, this co-occurrence count is the first step of "customers who bought this also bought…" and of market-basket analysis.
The fan-out trap
Here is the bug that catches almost everyone. We want, per order, the value of its items and the amount paid. Both live in "many" tables under orders, so we join both:
SELECT o.order_id,
SUM(oi.quantity * oi.unit_price) AS items_total,
SUM(pay.amount) AS paid_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments pay ON pay.order_id = o.order_id
WHERE o.order_id IN (1, 2, 6, 9)
GROUP BY o.order_id
ORDER BY o.order_id;+----------+-------------+------------+
| order_id | items_total | paid_total |
+----------+-------------+------------+
| 1 | 2100 | 4200 |
| 2 | 4500 | 4500 |
| 6 | 30000 | 15000 |
| 9 | 4950 | 9900 |
+----------+-------------+------------+Order 6 sold one course for 15,000 but shows 30,000 of items. Order 1 was paid 2,100 once but shows 4,200 paid. Only order 2 (one item, one payment) is right. Look at the rows before grouping:
SELECT o.order_id, oi.product_id, oi.quantity * oi.unit_price AS item_value, pay.payment_id, pay.amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments pay ON pay.order_id = o.order_id
WHERE o.order_id IN (1, 6)
ORDER BY o.order_id, oi.product_id, pay.payment_id;+----------+------------+------------+------------+--------+
| order_id | product_id | item_value | payment_id | amount |
+----------+------------+------------+------------+--------+
| 1 | 1 | 1200 | 1 | 2100 |
| 1 | 4 | 900 | 1 | 2100 |
| 6 | 7 | 15000 | 5 | 5000 |
| 6 | 7 | 15000 | 6 | 10000 |
+----------+------------+------------+------------+--------+| Order | Items | Payments | Joined rows | What gets repeated |
|---|---|---|---|---|
| 1 | 2 | 1 | 2 × 1 = 2 | payment 1 appears twice → paid 4,200 |
| 6 | 1 | 2 | 1 × 2 = 2 | the item appears twice → items 30,000 |
| 2 | 1 | 1 | 1 × 1 = 1 | nothing → correct by luck |
Items and payments have no relationship with each other; they only share the order. Joining both makes every item meet every payment of the same order — a small cross join inside each order. This is fan-out: the grain became "item × payment", and neither sum makes sense at that grain. No error is raised, and with mostly one-item, one-payment orders the totals even look plausible.
The fix: aggregate each "many" table first, then join
Bring each table to one row per order before joining. A query in brackets inside FROM acts like a table, called a derived table. This is a preview: subqueries like this are taught properly on the next page, Subqueries, EXISTS and Correlated Queries; for now, read each bracket as "a small table with one row per order":
SELECT o.order_id, i.items_total, p.paid_total
FROM orders o
JOIN (SELECT order_id, SUM(quantity * unit_price) AS items_total
FROM order_items
GROUP BY order_id) i ON i.order_id = o.order_id
JOIN (SELECT order_id, SUM(amount) AS paid_total
FROM payments
GROUP BY order_id) p ON p.order_id = o.order_id
WHERE o.order_id IN (1, 2, 6, 9)
ORDER BY o.order_id;+----------+-------------+------------+
| order_id | items_total | paid_total |
+----------+-------------+------------+
| 1 | 2100 | 2100 |
| 2 | 4500 | 4500 |
| 6 | 15000 | 15000 |
| 9 | 4950 | 4950 |
+----------+-------------+------------+Each derived table has exactly one row per order, so joining them to orders cannot multiply anything. COUNT(DISTINCT …) fixes fan-out for counts, but do not reach for SUM(DISTINCT …): it drops genuinely repeated values, such as two separate payments of 5,000.
With the fix in place, the query becomes a useful reconciliation check: which orders are not fully paid? Use a LEFT JOIN to payments so unpaid orders stay in:
SELECT o.order_id, o.status, i.items_total, COALESCE(p.paid_total, 0) AS paid_total,
i.items_total - COALESCE(p.paid_total, 0) AS balance
FROM orders o
JOIN (SELECT order_id, SUM(quantity * unit_price) AS items_total
FROM order_items GROUP BY order_id) i ON i.order_id = o.order_id
LEFT JOIN (SELECT order_id, SUM(amount) AS paid_total
FROM payments GROUP BY order_id) p ON p.order_id = o.order_id
WHERE i.items_total <> COALESCE(p.paid_total, 0)
ORDER BY o.order_id;+----------+-----------+-------------+------------+---------+
| order_id | status | items_total | paid_total | balance |
+----------+-----------+-------------+------------+---------+
| 5 | cancelled | 18500 | 0 | 18500 |
| 13 | pending | 15000 | 0 | 15000 |
+----------+-----------+-------------+------------+---------+Every delivered and shipped order balances; the two that do not are exactly the ones that should not have been paid.
A practical report: one row per customer
Put it together into a customer summary — the shape of a feature table for a churn or lifetime-value model. Customers are the grain, so start from customers and LEFT JOIN, keeping customers with no orders. Orders and items form a single chain (each item belongs to one order), so there is no fan-out; COUNT(DISTINCT o.order_id) counts orders, not items:
SELECT c.customer_id, c.name, c.city,
COUNT(DISTINCT o.order_id) AS orders,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
COUNT(DISTINCT oi.product_id) AS distinct_products,
MAX(o.order_date) AS last_order
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'cancelled'
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.name, c.city
ORDER BY revenue DESC, c.customer_id;+-------------+-----------------+------------+--------+---------+-------------------+------------+
| customer_id | name | city | orders | revenue | distinct_products | last_order |
+-------------+-----------------+------------+--------+---------+-------------------+------------+
| 1 | Nadia Rahman | Dhaka | 3 | 23600 | 4 | 2026-04-19 |
| 2 | Tanvir Ahmed | Chattogram | 3 | 22500 | 3 | 2026-05-21 |
| 6 | Imran Hossain | Khulna | 2 | 19950 | 3 | 2026-06-02 |
| 3 | Farhana Akter | Dhaka | 3 | 9500 | 5 | 2026-06-09 |
| 7 | Mitu Das | Dhaka | 1 | 5500 | 2 | 2026-05-06 |
| 5 | Sadia Chowdhury | NULL | 1 | 4000 | 2 | 2026-03-15 |
| 4 | Rafiq Islam | Sylhet | 0 | 0 | 0 | NULL |
| 8 | Karim Uddin | Rajshahi | 0 | 0 | 0 | NULL |
+-------------+-----------------+------------+--------+---------+-------------------+------------+Notice the status test sits in the first ON, so Rafiq (only a cancelled order) keeps his row with zeros. If you added payments to this query, you would be back in the fan-out trap — aggregate them separately first. In Python, this query goes straight into a DataFrame ready for scikit-learn (pandas 3.0 shows text columns as dtype str):
import pandas as pd
from sqlhelp import con
features = pd.read_sql("""
SELECT c.customer_id, c.city,
COUNT(DISTINCT o.order_id) AS orders,
COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS revenue,
COUNT(DISTINCT oi.product_id) AS distinct_products
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status <> 'cancelled'
LEFT JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.city
ORDER BY c.customer_id
""", con)
print(features.shape)
print(features.dtypes)
print(features["revenue"].sum(), (features["orders"] == 0).sum())(8, 5)
customer_id int64
city str
orders int64
revenue int64
distinct_products int64
dtype: object
85050 2Eight rows for eight customers: a quick shape check like this catches both dropped rows (an inner join where a LEFT was needed) and fan-out (more rows than customers).
Common mistakes
- Joining two "many" tables to the same parent and summing. Items × payments doubles order 6's items and order 1's payment. Fix: aggregate each child table to one row per parent in a derived table, then join.
- Not knowing the grain. After
orders JOIN order_items, one row is an item, soCOUNT(*)counts items, not orders. Fix: say the grain out loud before aggregating; useCOUNT(DISTINCT o.order_id)for orders. - One inner join in a chain of LEFT JOINs.
customers LEFT JOIN orders … JOIN order_items …drops Karim again, because the inner join needs an order. Fix: once a chain starts with a LEFT JOIN to keep rows, keep the following joins LEFT too. - A missing link in the chain. Joining
order_itemsstraight tocustomerson some column that happens to be a number (oi.order_id = c.customer_id) runs and is nonsense. Fix: draw the key path and join only along it. - Checking nothing. Fix: compare row counts and one known total (for example the order-6 payments add up to 15,000) after every new join.
Try it yourself
- Easy: List each payment with the customer's name:
payment_id, customer name,amount,method, ordered bypayment_id(three tables). - Medium: Revenue per city for delivered orders, highest first. Which table do you need for the city, and what happens to Sadia, whose city is NULL?
- Hard: Per payment method, show the number of payments and the total amount, and also the number of distinct customers who used it. Make sure no total is inflated (payments → orders → customers is a chain towards the "one" side, so check whether fan-out can happen).
Answers
-- 1
SELECT pay.payment_id, c.name, pay.amount, pay.method
FROM payments pay
JOIN orders o ON o.order_id = pay.order_id
JOIN customers c ON c.customer_id = o.customer_id
ORDER BY pay.payment_id;-- 2
SELECT c.city, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'delivered'
GROUP BY c.city
ORDER BY revenue DESC;+------------+---------+
| city | revenue |
+------------+---------+
| Dhaka | 38600 |
| Chattogram | 19500 |
| Khulna | 4950 |
| NULL | 4000 |
+------------+---------+Sadia's revenue is grouped under a NULL city: GROUP BY puts all NULLs in one group. Show it as COALESCE(c.city, 'Unknown') if the report is for people.
-- 3
SELECT pay.method,
COUNT(*) AS payments,
SUM(pay.amount) AS total,
COUNT(DISTINCT o.customer_id) AS customers
FROM payments pay
JOIN orders o ON o.order_id = pay.order_id
GROUP BY pay.method
ORDER BY total DESC;Every payment has exactly one order, so joining towards orders cannot multiply payments; the customer id is already on orders, so customers is not even needed.
Summary
- Chain joins along the key path, one
JOIN … ONper relationship; eachONmay use any table joined before it. - Know the grain of the joined rows before you aggregate; a join towards the "one" side keeps it, a join towards a "many" side changes it.
- Two "many" tables joined to the same parent multiply each other (fan-out): aggregate each to one row per parent first, then join.
- Many-to-many relationships go through the junction table; a self join of
order_itemsgives "bought together" pairs. - Check every multi-join with a row count and one known total.
Next: Subqueries, EXISTS and Correlated Queries takes the "query in brackets" you used for the fan-out fix and turns it into a full tool: subqueries in WHERE, FROM and SELECT, EXISTS, and when a subquery is clearer than a join.