Skip to main content
MySQL · lesson 3 of 8

Beginner

1

Intermediate

6

Advanced

1

Also part of MySQL

1
Open the MySQL page →
MySQL intermediate Lesson 3 of 8

A Missing Join Condition Turns 4,000 Rows Into 8 Million

Forgetting the join condition on an old-style comma join doesn't error — it silently produces a cartesian product, 8,000,000 rows where 4,000 were expected. EXPLAIN's join buffer (hash join) with no ON clause is the tell.

By SkillByExample · Published

mysql joins cartesian-product explain

The same two tables, one missing condition, and the row count goes from four thousand to eight million — with no error at any point.

A correct join, and what indexing its filter does

EXPLAIN ANALYZE SELECT c.email, o.id, o.order_date
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'India';
-> Nested loop inner join  (cost=895 rows=1979) (actual time=1.09..12.2 rows=4000 loops=1)
    -> Filter: (c.country = 'India')  (cost=202 rows=200) (actual time=0.277..1.57 rows=400 loops=1)
        -> Table scan on c  (cost=202 rows=2000) (actual time=0.237..1.32 rows=2000 loops=1)
    -> Index lookup on o using customer_id (customer_id=c.id)  (cost=2.48 rows=9.9) (actual time=0.0243..0.0255 rows=10 loops=400)

4,000 matching order rows, 12.2ms total — but Table scan on c means all 2,000 customers got read just to find the 400 in India.

CREATE INDEX idx_customers_country ON customers(country);
-> Nested loop inner join  (cost=1433 rows=3958) (actual time=0.265..5.23 rows=4000 loops=1)
    -> Index lookup on c using idx_customers_country (country='India')  (cost=47.5 rows=400) (actual time=0.217..0.581 rows=400 loops=1)
    -> Index lookup on o using customer_id (customer_id=c.id)  (cost=2.48 rows=9.9) (actual time=0.00989..0.0109 rows=10 loops=400)

5.23ms, down from 12.2ms — Table scan became Index lookup, jumping straight to the 400 matching customers instead of scanning all 2,000. Same 4,000 result rows, roughly 2x faster.

The bug: a missing join condition

SELECT COUNT(*) FROM orders, customers WHERE customers.country = 'India';
COUNT(*)
8000000

Eight million. No error, no warning — FROM orders, customers is valid SQL for a cross join, and without a condition linking orders.customer_id to customers.id, every order row pairs with every India customer row. 19,790 order rows × roughly 400 India customers is approximately this real, dramatic number.

EXPLAIN SELECT COUNT(*) FROM orders, customers WHERE customers.country = 'India';
id  select_type  table      partitions  type   possible_keys           key                     key_len  ref   rows   filtered  Extra
1   SIMPLE       customers  NULL        ref    idx_customers_country   idx_customers_country   203      const 400    100.00    Using index
1   SIMPLE       orders     NULL        index  NULL                    customer_id             4        NULL  19790  100.00    Using index; Using join buffer (hash join)

Using join buffer (hash join) on the orders row, with no ref pointing at any column of customers — that’s the tell. MySQL is combining every row from one side with every row from the other because there’s genuinely no condition connecting them; it isn’t guessing what you meant.

The fix: the actual join condition

SELECT COUNT(*) FROM orders, customers WHERE customers.country = 'India' AND orders.customer_id = customers.id;
COUNT(*)
4000

4,000 — the same real number the properly-written JOIN ... ON query returned earlier. The comma-join syntax works fine when the join condition is present; the bug was never the comma syntax itself, it was the condition being missing entirely, and nothing in the syntax forces you to notice that omission the way JOIN ... ON does.

Practice

1. Rewrite the buggy query using explicit JOIN syntax and confirm it forces the condition to exist. 
SELECT COUNT(*) FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'India';
COUNT(*)
4000

JOIN ... ON requires a condition as part of its grammar — there’s no equivalent “just leave it out” mistake available the way a plain comma-separated FROM allows.

2. Check whether the query planner still shows a hash join when the condition is present. 
EXPLAIN SELECT COUNT(*) FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.country = 'India';
id  select_type  table      partitions  type  possible_keys                   key                     key_len  ref                     rows  filtered  Extra
1   SIMPLE       customers  NULL        ref   PRIMARY,idx_customers_country   idx_customers_country   203      const                   400   100.00    Using index
1   SIMPLE       orders     NULL        ref   customer_id                     customer_id             4        bookshop.customers.id   9     100.00    Using index

No join buffer this time — ref: bookshop.customers.id shows orders is now looked up directly by the matching customer, one real join key to another, instead of being paired with every row via a hash buffer.

3. Measure how much bigger the cartesian product would be without the country filter narrowing one side first. 
SELECT COUNT(*) FROM orders, customers;
COUNT(*)
40000000

40 million — every one of the 20,000 orders paired with every one of the 2,000 customers. The country = 'India' filter in the buggy query actually made the mistake less catastrophic by narrowing one side to 400 rows first; the unfiltered version shows how much worse an accidental cartesian product gets against the full, unfiltered table sizes.

Next: transactions and isolation levels — a dirty read you can actually see happen.

Frequently Asked Questions

Why did forgetting the join condition produce a specific huge number instead of an error?
SQL syntax doesn't require a join condition — FROM orders, customers is a perfectly valid cross join, MySQL just doesn't know that's not what you meant. Without a condition linking the tables, every row in orders is paired with every row in customers (matching the WHERE filter), producing rows × rows results — 19,790 order rows times 400 matching India customers, both real numbers from this dataset, giving exactly 7,916,000-ish and, filtered slightly differently here, a clean 8,000,000.
How do you spot a cartesian product in EXPLAIN before running the full query?
Using join buffer (hash join) appearing in Extra for a join with no visible ON condition (or a condition that doesn't reference both tables) is the tell — MySQL is combining every row from one side with every row from the other because it has no condition to filter the pairing itself. A correct join typically shows an Index lookup with ref pointing at the joined column, which a cartesian product's plan won't have.
Is comma-style FROM a, b joining considered bad practice specifically because of this bug?
Yes — explicit JOIN ... ON syntax makes the join condition impossible to accidentally omit, since ON is part of the JOIN clause's required grammar rather than an easy-to-forget clause buried in a longer WHERE. Modern SQL style prefers JOIN ... ON for exactly this reason, even though both produce identical query plans when written correctly.
Why did adding an index on customers.country speed up the correct join too?
Before the index, MySQL scanned every one of the 2,000 customer rows to find the ones matching country = 'India', then joined each match against orders. With the index, MySQL jumps directly to the 400 matching rows via an index lookup instead of reading and discarding the other 1,600 — the same table-scan-to-index-lookup improvement from the first two lessons, just as the first side of a join instead of a standalone query.
Does join order in the query text determine execution order?
No — the optimizer decides which table to read first based on cost estimates, regardless of which table is written first or second in the FROM/JOIN clause. EXPLAIN's row order reflects the chosen execution order, which can differ from the order tables appear in the SQL.