Joining Several Tables
How to chain joins across several tables, why MySQL reorders them, and where the fan-out that doubles your SUM comes from.
FROM a JOIN b ON ... JOIN c ON ... LEFT JOIN d ON ...Joins chain: the result of the first join becomes the left-hand operand of the next, so FROM a JOIN b ON … JOIN c ON … is read left to right and each ON may reference any table already introduced. MySQL allows up to 61 tables in one join, counting merged views and derived tables.
Written order is not execution order
The optimizer reorders joins freely by cost; the order you type is only a starting point. When its estimate is badly wrong you can pin the order with the JOIN_ORDER, JOIN_PREFIX, JOIN_SUFFIX or JOIN_FIXED_ORDER optimizer hints, or with SELECT STRAIGHT_JOIN, which is the older shorthand for the same thing as JOIN_FIXED_ORDER. Reach for these only after EXPLAIN has shown you a bad plan.
Fan-out
A join to a one-to-many child duplicates the parent row once per child. Join two such children to the same parent and the duplicates multiply: an order with 3 items and 2 payments yields 6 rows, and SUM(o.total_cents) now counts the order six times. This is the bug that produces revenue figures a few times too large. The fix is to aggregate each child in its own derived table or CTE so it contributes exactly one row, then join those.
What trips people up
Do not mix commas with JOIN in one FROM clause: JOIN has higher precedence, so FROM t1, t2 JOIN t3 ON t1.i = t3.i raises "Unknown column 't1.i' in 'on clause'".
Example
-- What each customer bought, across four tables.
-- Each ON may reference any table introduced to its left.
SELECT c.email,
o.id AS order_id,
o.placed_at,
p.sku,
oi.quantity,
oi.unit_cents
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
LEFT JOIN discounts AS d ON d.order_id = o.id -- optional, so LEFT
WHERE o.status = 'paid'
AND o.placed_at >= '2026-01-01'
ORDER BY c.email, o.placed_at, p.sku;
-- Note: once a join is LEFT, anything joined off its right-hand table
-- must be LEFT too, or the unmatched rows are deleted again.When to use it
- Assemble an order line report spanning customers, orders, items and products.
- Fix a revenue total that reads as an exact multiple of the real figure.
- Pre-aggregate child tables so a dashboard query returns one row per parent.
- Read EXPLAIN to find out which table MySQL actually reads first in a five-table query.
More examples
The fan-out that doubles your SUM, and the fix
The single most expensive multi-join bug, because the query returns a plausible number rather than an error. The symptom is a total that is an exact small multiple of the truth. Whenever a query joins two independent one-to-many children and then aggregates, pre-aggregate each child; SUM(DISTINCT ...) is not a fix, since two children can legitimately have equal amounts.
-- WRONG. An order with 3 items and 2 payments produces 6 rows,
-- so o.total_cents is added 6 times and the item count is doubled.
SELECT o.id,
SUM(o.total_cents) AS revenue, -- inflated
SUM(oi.quantity) AS units, -- inflated
SUM(pay.amount_cents) AS paid -- inflated
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN payments AS pay ON pay.order_id = o.id
GROUP BY o.id;
-- RIGHT. Each child is collapsed to one row per order first,
-- so the join is one-to-one and nothing multiplies.
SELECT o.id,
o.total_cents AS revenue,
COALESCE(i.units, 0) AS units,
COALESCE(p.paid, 0) AS paid
FROM orders AS o
LEFT JOIN (
SELECT order_id, SUM(quantity) AS units
FROM order_items GROUP BY order_id
) AS i ON i.order_id = o.id
LEFT JOIN (
SELECT order_id, SUM(amount_cents) AS paid
FROM payments GROUP BY order_id
) AS p ON p.order_id = o.id;The same query with CTEs instead of derived tables
Readability at scale: a CTE names each pre-aggregated block, so a reviewer can check 'payments are summed per order' without parsing a nested subquery in the middle of a FROM clause. It costs nothing over the derived-table form because MySQL optimizes derived tables and CTEs the same way — and here both blocks contain GROUP BY and aggregates, which prevent merging into the outer query block, so each is materialized exactly as the derived tables in the previous example were.
-- Identical plan shape, but each stage is named and can be read
-- top to bottom. Prefer this once a query has more than two subqueries.
WITH item_totals AS (
SELECT order_id, SUM(quantity) AS units, COUNT(*) AS line_count
FROM order_items
GROUP BY order_id
),
payment_totals AS (
SELECT order_id, SUM(amount_cents) AS paid
FROM payments
WHERE captured_at IS NOT NULL
GROUP BY order_id
)
SELECT c.email,
o.id,
i.units,
i.line_count,
COALESCE(p.paid, 0) AS paid_cents,
o.total_cents - COALESCE(p.paid, 0) AS outstanding_cents
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
LEFT JOIN item_totals AS i ON i.order_id = o.id
LEFT JOIN payment_totals AS p ON p.order_id = o.id
WHERE o.status = 'paid'
ORDER BY outstanding_cents DESC;Forcing a join order when the optimizer gets it wrong
Shows the hint syntax next to the legacy keyword it replaced. Treat hints as a last resort: they freeze a decision that would otherwise improve as statistics change, and a missing index or a stale ANALYZE TABLE is the more common cause of a bad join order.
-- Read the plan first. Look at the estimated row counts: a join order
-- is bad when a huge table is scanned before a cheap filter is applied.
EXPLAIN FORMAT=TREE
SELECT c.email, o.id
FROM events AS e
JOIN orders AS o ON o.id = e.order_id
JOIN customers AS c ON c.id = o.customer_id
WHERE e.kind = 'refund_requested';
-- Pin the order only if the plan is genuinely wrong.
SELECT /*+ JOIN_ORDER(e, o, c) */ c.email, o.id
FROM events AS e
JOIN orders AS o ON o.id = e.order_id
JOIN customers AS c ON c.id = o.customer_id
WHERE e.kind = 'refund_requested';
-- JOIN_FIXED_ORDER pins every table to the FROM order; the older
-- SELECT STRAIGHT_JOIN keyword means the same thing.
SELECT /*+ JOIN_FIXED_ORDER */ ... ;
SELECT STRAIGHT_JOIN ... ;
Discussion