Accidental Cartesian Product - Forgot ON Clause

-- Trying to get customer orders but forgot ON clause

SELECT

c.name,

o.order_id,

o.total

FROM customers c

INNER JOIN orders o; -- MISSING ON CLAUSE!

-- Result: Accidental cartesian product

-- 1,000 customers × 5,000 orders = 5,000,000 rows

-- Every customer paired with EVERY order (wrong!)

-- Correct: Use ON clause to match related rows

SELECT

c.name,

o.order_id,

o.total

FROM customers c

INNER JOIN orders o ON c.id = o.customer_id;

-- Result: Only matching customer-order pairs

-- ~5,000 rows (each order with its customer)

-- Correct data!

Forgetting the ON clause in INNER JOIN creates an accidental CROSS JOIN (cartesian product). Every customer gets paired with EVERY order, not just their own orders. This is the most common and dangerous CROSS JOIN mistake. The database tries to create millions of meaningless combinations. Always include ON clause with INNER JOIN unless you genuinely want a cartesian product (rare).

Rule: INNER JOIN always needs ON clause. CROSS JOIN never has ON clause. If you see INNER JOIN without ON, it's a bug.

Critical

Query returns millions of rows, runs forever, or crashes database

Using CROSS JOIN on Large Tables Without Filtering

-- Trying to generate all product-size combinations

-- But products table is huge!

SELECT

p.name AS product,

s.name AS size,

CONCAT(p.name, ' - ', s.name) AS sku

FROM products p

CROSS JOIN sizes s;

-- Result: 10,000 products × 10 sizes = 100,000 rows

-- Slow query, massive data transfer

-- Most combinations not needed

-- Filter BEFORE CROSS JOIN to reduce combinations

SELECT

p.name AS product,