Row multiplication, inflated totals, and the joins that produce wrong numbers without erroring.

Cross Joins and Join Pitfalls

Joins fail quietly. A wrong ON clause does not raise an error — it returns a number, and the number is wrong. This lesson is the catalogue.

CROSS JOIN — every row with every row

SQL
SELECT COUNT(*) AS combinations
FROM departments
CROSS JOIN products;

Four departments × five products = twenty rows. A cross join has no ON clause, because there is no condition — it pairs everything with everything. The result size is the product of the inputs, which is why two tables of 10,000 rows produce a hundred million.

It has genuine uses: generating a grid to report against, pairing every store with every date so empty days still appear. It is also what you get by accident when you list two tables and forget the condition:

SQL
SELECT COUNT(*) AS accidental
FROM departments d, employees e;

Forty rows from four departments and ten employees — every employee in every department. The old comma syntax makes this easy to do and hard to see. Writing JOIN … ON explicitly means a missing condition is a syntax error instead of a silently wrong answer, and that is the reason to prefer it.

Fan-out — the one that inflates money

This is the most damaging join bug there is, because the query works, the numbers look plausible, and they are far too big.

SQL
SELECT ROUND(SUM(budget), 2) AS true_total_budget FROM departments;

SQL
SELECT ROUND(SUM(d.budget), 2) AS budget_after_joining_employees
FROM departments d
JOIN employees e ON e.dept_id = d.dept_id;

The real total is under two million. The joined total is over six. Nothing is broken — joining a department to its four employees produces four rows, each carrying that department's full budget, and SUM adds it four times.

Seeing it happen makes it obvious:

SQL
SELECT d.name, d.budget, COUNT(e.emp_id) AS staff,
       ROUND(SUM(d.budget), 2) AS budget_counted_once_per_employee
FROM departments d
JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.name, d.budget
ORDER BY d.name;

The rule: never aggregate a column from the "one" side of a one-to-many join. Once the join has multiplied the rows, the sum is meaningless.

Three ways out, in order of preference:

Aggregate before joining — total the many-side in a subquery or CTE, then join one row to one row.
SUM(DISTINCT …) — only safe when the values are genuinely distinct, which two departments with equal budgets would break.
Aggregate the key, not the value — COUNT(DISTINCT e.emp_id) instead of COUNT(*).

The tell-tale symptom in the wild is a total that is a suspiciously round multiple of the real one, or one that grows every time unrelated data is added.

Joins that lose rows

The mirror image, from lesson 19: every INNER JOIN in a chain can drop rows, and NULL join keys never match anything.

SQL
SELECT (SELECT COUNT(*) FROM orders)                                   AS orders,
       (SELECT COUNT(*) FROM orders o
          JOIN customers c ON c.customer_id = o.customer_id)           AS after_customers,
       (SELECT COUNT(*) FROM orders o
          JOIN customers c ON c.customer_id = o.customer_id
          JOIN payments  p ON p.order_id    = o.order_id)              AS after_payments;

Eight, seven, five. Each join silently removed rows, and by the third column nearly half the orders are gone. Running the counts side by side like this, before trusting a multi-join report, takes thirty seconds and catches the problem.

Joining on the wrong column

SQL
SELECT COUNT(*) AS nonsense
FROM orders o
JOIN customers c ON c.customer_id = o.order_id;

order_id values start at 1001 and no customer id is anywhere near, so this returns zero. When the two columns do overlap in range — joining on a quantity, or on the wrong id of two — you get a nonzero count instead, and nothing at all suggests it is wrong. Join on keys, and check the row count.

Duplicate keys on both sides

If the join key repeats on both sides, the result multiplies: three matching rows on the left and two on the right give six. This is how a join can return more rows than either input table has. If you did not expect duplicates, the fix is upstream — the data has a uniqueness problem the join is only revealing.

A checklist before trusting a join

Count the rows before and after. Did you expect the change?
Is any column you aggregate coming from the "one" side of a one-to-many?
Is the join key nullable? Then INNER is dropping those rows.
Is every ON comparing keys, not coincidentally-typed columns?
Should this be LEFT? If unmatched rows matter, yes.

Common mistakes

Comma-separated tables with no condition — an accidental cross join.
SUM on the one-side of one-to-many — inflated totals.
Assuming a working query is a correct query — joins do not error, they answer.
Not checking row counts — the only reliable detector for all of the above.

Interview question

A revenue report shows three times the real figure. Where do you look first?

A fan-out: a SUM over a column from the one-side of a one-to-many join, so each value is counted once per matching row. Check whether the join multiplied the rows — comparing COUNT(*) before and after the join shows it immediately.

Check yourself

How many rows does a cross join of a 200-row and a 50-row table produce?
Why does SUM(d.budget) overstate after joining employees?
Name two ways to compute a correct total across a one-to-many join.