Accidental Cartesian Product — The Cross Join Trap

What Is a Cartesian Product?

A Cartesian product occurs when every row from one table is paired with every row from another table. If Table A has 1,000 rows and Table B has 1,000 rows, the result is 1,000,000 rows. While this is intentional with CROSS JOIN, it is a devastating bug when it happens by accident.

Common Causes

1. Missing ON Clause

-- BUG: forgot the ON clause
SELECT o.id, c.name
FROM orders o
JOIN customers c;
-- Produces orders * customers rows

2. Comma Syntax Without WHERE

-- BUG: old-style join without condition
SELECT o.id, c.name
FROM orders o, customers c;
-- Same Cartesian product

3. Wrong Join Column

-- BUG: joining on non-unique column
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.status = c.status;
-- If 500 orders and 200 customers share status 'active',
-- that alone produces 100,000 rows

4. Missing Join in Multi-Table Query

-- BUG: table C is not joined to anything
SELECT a.*, b.*, c.*
FROM table_a a
JOIN table_b b ON a.id = b.a_id,
table_c c;
-- table_c is cross-joined with the rest

How to Detect

1. Check the row count: If the result has way more rows than expected, suspect a Cartesian product.
2. EXPLAIN plan: Look for Nested Loop without an index condition or Cross Join in the plan.
3. Rule of thumb: A proper join should never produce more rows than MAX(left_rows, right_rows) unless there are legitimate one-to-many or many-to-many relationships.

How to Fix

1. Add the correct ON clause linking the tables.
2. Use explicit JOIN syntax instead of comma-separated tables.
3. Review every table in the FROM clause to ensure it participates in at least one join condition.

Prevention

• Use a SQL linter that warns about missing join conditions.
• Always run EXPLAIN on new queries before executing them on production data.
• Use modern JOIN ... ON syntax exclusively — never the comma syntax.