SQL / JOINS
CROSS JOIN and accidental row explosions
Predict how many rows a join returns, use CROSS JOIN deliberately, and spot fan-out that silently inflates SUM and COUNT.
What you will learn
- Write CROSS JOIN when you want every pair: no ON clause, m x n rows, always.
- Compute expected rows as the sum of (left matches x right matches) per key value.
- Detect fan-out by comparing COUNT(*) with COUNT(DISTINCT parent_id) after the join.
- Pre-aggregate each child table to one row per key before joining two of them.
Understanding CROSS JOIN and accidental row explosions
CROSS JOIN is the one join that takes no ON clause, because it has nothing to match: it pairs every row on the left with every row on the right. Three sizes and two colours give six pairs, and the grain of the result is no longer one row per size but one row per (size, colour) pair. The old comma form, FROM sizes s, colors c, is the same operator with different syntax, which is why a forgotten join condition produces a full product rather than an error.
That gives you the mental model for every other join: start from the cross product, then keep only the pairs the ON condition accepts. An inner join on customer_id feels safe only because customer_id is unique in customers, so each order finds exactly one partner. When the join column repeats on both sides, say two 'east' rows in targets and two 'east' rows in sales, the condition is true for all four combinations, so you get a small cross product inside each key value. Total rows equals the sum over key values of (left count x right count), which is why joins multiply instead of adding.
A row explosion never raises an error, it corrupts aggregates. Join orders to order_items and to payments in one query and each item row repeats once per payment while each payment repeats once per item, so both SUM values come back multiplied by the other table's row count. The cure is to match grains: collapse each child to one row per order in a derived table before joining, or add the missing column to ON when the real relationship is a composite key. Get in the habit of comparing COUNT(*) with COUNT(DISTINCT o.order_id) after a join; if the first is larger, something on the right is not unique per order.
WITH sizes(size_name) AS (
SELECT 'S' UNION ALL SELECT 'M' UNION ALL SELECT 'L'
),
colors(color_name) AS (
SELECT 'red' UNION ALL SELECT 'blue'
)
-- no ON clause: 3 rows x 2 rows = 6 rows, unconditionally
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c -- same operator as: FROM sizes s, colors c
ORDER BY s.size_name, c.color_name;Every join returns the slice of the cross product that its ON condition allows, so a key that repeats on both sides multiplies rows instead of pairing them one to one.
Worked examples
Two children of one parent
Joining orders to both items and payments multiplies each child's rows by the other child's count.
WITH orders(order_id, customer) AS (
SELECT 1, 'Ana' UNION ALL SELECT 2, 'Bo'
),
items(order_id, product, qty) AS (
SELECT 1, 'mug', 2 UNION ALL
SELECT 1, 'pen', 5 UNION ALL
SELECT 2, 'hat', 1
),
payments(order_id, amount) AS (
SELECT 1, 30 UNION ALL
SELECT 1, 20 UNION ALL
SELECT 2, 15
)
SELECT o.order_id,
SUM(i.qty) AS qty_sum,
SUM(p.amount) AS paid_sum,
COUNT(*) AS join_rows
FROM orders o
JOIN items i ON i.order_id = o.order_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY o.order_id
ORDER BY o.order_id;Example explained
Line 1Both JOINs match order 1, so the engine builds every (item, payment) combination for it: 2 x 2 = 4 rows, visible in join_rows.
Line 2qty_sum is 14 instead of 7 because each item row appears once per payment, and paid_sum is 100 instead of 50 because each payment appears once per item.
Line 3GROUP BY runs after the join, so it aggregates rows that are already duplicated and cannot undo the multiplication.
Line 4Order 2 has one item and one payment, so 1 x 1 = 1 row and its totals are correct, which is how this bug survives testing on tiny data.
A cross join hiding in the ON clause
An ON condition on a column that repeats on both sides produces a cross product inside each key value.
WITH targets(region, target) AS (
SELECT 'east', 100 UNION ALL
SELECT 'east', 150 UNION ALL
SELECT 'west', 200
),
sales(region, amount) AS (
SELECT 'east', 40 UNION ALL
SELECT 'east', 60 UNION ALL
SELECT 'west', 90
)
SELECT t.region,
COUNT(*) AS rows_out,
SUM(s.amount) AS amount_sum
FROM targets t
JOIN sales s ON s.region = t.region
GROUP BY t.region
ORDER BY t.region;Example explained
Line 1region is not unique in either table, so s.region = t.region is true for all 2 x 2 pairings of the east rows.
Line 2rows_out exposes the real grain of the join: 4 east rows, one cross product per key value.
Line 3amount_sum doubles to 200 because each east sale is paired with both east targets; the true east total is 100.
Line 4west has one row on each side and looks perfectly correct, which is why the mistake is easy to miss in a mixed result.
CROSS JOIN as a deliberate scaffold
Building a complete month-by-product grid on purpose, then attaching the sparse facts to it.
WITH months(month_no) AS (
SELECT 1 UNION ALL SELECT 2
),
products(product) AS (
SELECT 'mug' UNION ALL SELECT 'pen'
),
sales(month_no, product, units) AS (
SELECT 1, 'mug', 5 UNION ALL
SELECT 2, 'pen', 3
)
SELECT m.month_no, p.product, COALESCE(s.units, 0) AS units
FROM months m
CROSS JOIN products p
LEFT JOIN sales s
ON s.month_no = m.month_no
AND s.product = p.product
ORDER BY m.month_no, p.product;Example explained
Line 1CROSS JOIN runs first and manufactures the full 2 x 2 grid, including the two combinations that never sold.
Line 2The LEFT JOIN then hangs sales off that grid, so a month-product cell with no sale keeps its row.
Line 3COALESCE converts the NULL of an unmatched cell into 0, which is what a report needs.
Line 4Here multiplying rows is the goal, and the row count is fixed by the two dimension lists rather than by duplicate keys.
Important notes
Postgres rejects CROSS JOIN ... ON as a syntax error, but MySQL and SQLite treat CROSS JOIN as another spelling of INNER JOIN and accept a condition, so the keyword alone does not prove a join is unconditional.
A cross product of two 20,000-row tables is 400 million rows: the query does not fail, it just runs until it exhausts time or temp space, and a LIMIT will not rescue you if an ORDER BY or an aggregate has to see every row first.
Common mistakes
Selecting SUM(items.qty) and SUM(payments.amount) in one query joined to the same order: each total is multiplied by the other table's row count, so the revenue figure is quietly too high and no error is raised.
Patching a fan-out with SELECT DISTINCT: it removes the duplicate rows, but it also collapses two genuinely identical rows such as two payments of 20, so the total flips from too high to too low while the row count looks plausible.
Assuming a column ending in _id is unique on the side you join to; order_items.order_id repeats, so each order row returns once per item and COUNT(*) counts items while you believe it counts orders.
Try it yourself
Change, predict, then run
In a browser editor, build a students CTE with 3 rows and a subjects CTE with 4 rows, CROSS JOIN them, and confirm 12 rows come back. Then add a grades CTE that holds two rows for the same student and subject, join it in, and predict the new row count before you run it.
Open the SQL workspaceCheck your understanding
An order has 3 rows in order_items and 2 rows in payments. One query joins orders to both children and selects SUM(order_items.qty) and SUM(payments.amount), grouped by order_id. What do the two sums show?
- Both are correct, because GROUP BY order_id collapses the duplicated rows.
- qty is 2x too high and amount is 3x too high.
- qty is 3x too high and amount is 2x too high.
- Each sum is off by one row, since only the extra payment is duplicated.
Show answer
The join builds 3 x 2 = 6 rows for that order, so every item row is repeated once per payment (x2) and every payment row once per item (x3): a table's inflation factor is the other side's row count, which is exactly what option 3 gets backwards. GROUP BY cannot rescue either total, because it aggregates rows the join has already duplicated.