Joining three or more tables
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Real questions often need data from three, four or more tables. A multi-table join is just several two-table joins in a row: each new JOIN attaches one more table to the rows built so far, using the key that links them.
Customers, orders, order items and products
In ShopLite, one question such as "who bought what, how many, at what price?" touches four tables. customers holds who, orders holds when, order_items holds how many of which product at what price, and products holds the product names.
Example 1 · Four tables: who bought what
SELECT c.name AS customer, o.order_id, p.name AS product, oi.quantity, oi.unit_price, oi.quantity * oi.unit_price AS line_totalFROM customers cJOIN orders o ON o.customer_id = c.customer_idJOIN order_items oi ON oi.order_id = o.order_idJOIN products p ON p.product_id = oi.product_idWHERE o.order_id IN (1, 2)ORDER BY o.order_id, p.name;+------------+----------+-------------------+----------+------------+------------+ | customer | order_id | product | quantity | unit_price | line_total | +------------+----------+-------------------+----------+------------+------------+ | Asha Rao | 1 | boAt Airdopes 141 | 2 | 1299.00 | 2598.00 | | Asha Rao | 1 | Redmi Note 13 | 1 | 17999.00 | 17999.00 | | Ravi Kumar | 2 | Atomic Habits | 2 | 399.00 | 798.00 | | Ravi Kumar | 2 | Clean Code | 1 | 499.00 | 499.00 | | Ravi Kumar | 2 | Hands-On Python | 1 | 650.00 | 650.00 | +------------+----------+-------------------+----------+------------+------------+
What this code does
- Each
JOINadds one table using one link of the chain: customer to order, order to item, item to product. - Order 2 has three items, so Ravi Kumar and order 2 appear on three rows. Every row is one item line, and the columns come from all four tables.
The tables form a chain of keys: customers.customer_id ← orders.customer_id, orders.order_id ← order_items.order_id, and order_items.product_id → products.product_id. order_items sits in the middle because one order holds many products and one product appears in many orders.
A four-table join follows this chain: draw it before you write the query, and the ON conditions then write themselves.
Following the keys one join at a time
A key chain is the path of foreign key to primary key links that connects the tables a question needs, such as customers to orders to order items to products. Each JOIN ... ON adds one link.
MySQL reads FROM a JOIN b ON ... JOIN c ON ... JOIN d ON ... as a chain: join a and b, join that result with c, then with d.
Example 2 · Counting rows after each join
SELECT COUNT(*) AS step1_ordersFROM orders o;
SELECT COUNT(*) AS step2_with_customersFROM orders oJOIN customers c ON c.customer_id = o.customer_id;
SELECT COUNT(*) AS step3_with_itemsFROM orders oJOIN customers c ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_id;
SELECT COUNT(*) AS step4_with_productsFROM orders oJOIN customers c ON c.customer_id = o.customer_idJOIN order_items oi ON oi.order_id = o.order_idJOIN products p ON p.product_id = oi.product_id;+--------------+ | step1_orders | +--------------+ | 15 | +--------------+ +----------------------+ | step2_with_customers | +----------------------+ | 15 | +----------------------+ +------------------+ | step3_with_items | +------------------+ | 25 | +------------------+ +---------------------+ | step4_with_products | +---------------------+ | 25 | +---------------------+
What this code does
- Adding a parent (customer, product) keeps the count, because each row has exactly one. Adding a child (
order_items) raises it from 15 to 25, one row per item line. - This is how you debug a long join: when a step changes the count in a way you did not expect, the mistake is in that step's
ON.
Example 3 · Three tables in the college database
SELECT s.name, s.branch, c.code, en.marksFROM students sJOIN enrollments en ON en.student_id = s.student_idJOIN courses c ON c.course_id = en.course_idWHERE c.code = 'CS202'ORDER BY en.marks DESC;+---------------+--------+-------+-------+ | name | branch | code | marks | +---------------+--------+-------+-------+ | Aditya Joshi | CSE | CS202 | 92 | | Deepika Nair | CSE | CS202 | 91 | | Bhavana Reddy | CSE | CS202 | 85 | | Gaurav Mishra | IT | CS202 | 38 | +---------------+--------+-------+-------+
What this code does
enrollmentsis the middle table that links students and courses, so it is joined to both: once bystudent_idand once bycourse_id.- The filter is on
courses, the sort is onenrollments, and the names come fromstudents: after the joins, every table's columns can be used anywhere.
Each ON may use any table that is already in the chain, so c's condition can mention a or b.
Build a long join one table at a time and check COUNT(*) after each step. Outer joins need care in a chain. If you LEFT JOIN a table, later joins to that table must also be LEFT JOIN; an inner join there drops the very rows the left join kept, because their columns are NULL.
Order of execution
Build the four-table join
Order the lines so each JOIN uses a table already in the chain.
Joins with GROUP BY
Grouping after a multi-table join works exactly as on one table: the join builds the detailed rows first, then GROUP BY folds them into one row per group.
Example 4 · Revenue per customer
SELECT c.customer_id, c.name, COUNT(DISTINCT o.order_id) AS orders, SUM(oi.quantity * oi.unit_price) AS revenueFROM customers cJOIN orders o ON o.customer_id = c.customer_idJOIN order_items oi ON oi.order_id = o.order_idWHERE o.status <> 'CANCELLED'GROUP BY c.customer_id, c.nameORDER BY revenue DESC;+-------------+--------------+--------+----------+ | customer_id | name | orders | revenue | +-------------+--------------+--------+----------+ | 1 | Asha Rao | 3 | 68834.00 | | 6 | Arjun Mehta | 1 | 64990.00 | | 4 | Kabir Singh | 2 | 27196.00 | | 8 | Rahul Verma | 1 | 19298.00 | | 2 | Ravi Kumar | 3 | 8741.00 | | 5 | Priya Sharma | 1 | 5196.00 | | 9 | Sneha Patil | 1 | 1300.00 | | 7 | Divya Nair | 1 | 898.00 | +-------------+--------------+--------+----------+
What this code does
- The join builds one row per item line;
WHEREdrops the cancelled orders;GROUP BYfolds the rest into one row per customer. - Meena Iyer is missing because her only order was cancelled, and Imran Khan because he never ordered.
COUNT(DISTINCT o.order_id)counts orders, not item lines, which matters in the last section.
Example 5 · Revenue per category: four tables
SELECT cat.name AS category, SUM(oi.quantity) AS units, SUM(oi.quantity * oi.unit_price) AS revenueFROM categories catJOIN products p ON p.category_id = cat.category_idJOIN order_items oi ON oi.product_id = p.product_idJOIN orders o ON o.order_id = oi.order_idWHERE o.status <> 'CANCELLED'GROUP BY cat.category_id, cat.nameHAVING revenue > 5000ORDER BY revenue DESC;+-------------+-------+-----------+ | category | units | revenue | +-------------+-------+-----------+ | Laptops | 2 | 107980.00 | | Mobiles | 3 | 55997.00 | | Accessories | 11 | 16539.00 | | Fashion | 6 | 10194.00 | +-------------+-------+-----------+
What this code does
- The chain starts from the table the report is about (
categories) and follows the keys outward toorders, which is needed only for the status filter. HAVING revenue > 5000filters groups after they are totalled. Books and Toys sold less than that, and the gift card has no category, so it never joins.
You can group by a column of any joined table and add up a column of any other.
Revenue per customer needs the customer's name (customers), the status to skip cancelled orders (orders) and quantity × price (order_items). Revenue per category needs two more steps of the chain: products to reach categories. Filter before grouping with WHERE (cancelled orders out), and after grouping with HAVING (customers above Rs. 20,000). Group by the key as well as the name, GROUP BY c.customer_id, c.name, so two customers with the same name are never merged.
Duplicate rows from one-to-many joins
A fan-out happens when a one-to-many join repeats the row of the "one" side once for every matching "many" row. Sums and counts of the "one" side are then too big.
A one-to-many join repeats the "one" side once per matching "many" row. After joining orders to order_items, an order with 3 items appears on 3 rows. That is correct for listing items, and wrong for anything counted or summed on the "one" side.
Example 6 · Trap: double counting after a one-to-many join
-- Orders per customer, counted after joining the itemsSELECT c.name, COUNT(*) AS wrong_orders, COUNT(DISTINCT o.order_id) AS ordersFROM customers cJOIN orders o ON o.customer_id = c.customer_idJOIN order_items oi ON oi.order_id = o.order_idWHERE c.customer_id IN (1, 2)GROUP BY c.customer_id, c.nameORDER BY c.customer_id;
-- Stock of Accessories: summed after joining order items, and summed correctlySELECT SUM(p.stock) AS wrong_stockFROM products pJOIN order_items oi ON oi.product_id = p.product_idWHERE p.category_id = 4;
SELECT SUM(p.stock) AS stockFROM products pWHERE p.category_id = 4;+------------+--------------+--------+ | name | wrong_orders | orders | +------------+--------------+--------+ | Asha Rao | 5 | 3 | | Ravi Kumar | 7 | 3 | +------------+--------------+--------+ +-------------+ | wrong_stock | +-------------+ | 600 | +-------------+ +-------+ | stock | +-------+ | 180 | +-------+
What this code does
- Asha has 3 orders with 5 item lines, so
COUNT(*)says 5.COUNT(DISTINCT o.order_id)counts each order once and gives the real 3. - The boAt earbuds (stock 120) appear in 4 orders, so the join adds their stock 4 times: 600 instead of 180. Stock does not need
order_itemsat all; leaving the table out is the fix.
The classic bug: count orders per customer after joining items. COUNT(*) now counts item rows, so a customer with 3 orders and 6 items shows 6. Summing a parent column is worse: a product's stock is added once per order it appears in, so stock looks several times larger than it is.
The fixes: count the parent's key with COUNT(DISTINCT o.order_id), and do not join a table the question does not need. When you need totals from two different child tables, total each one separately first; the Subqueries module shows how to join those totals.
Predict the output
Predict the fan-out
There are 15 orders and 25 order items in total.
Next step
Check your understanding
12 quick questions