Why does SELECT * FROM orders o, customers c WHERE o.total > 100 return millions of rows?

## The symptom A query over a modest orders table comes back with tens of millions of rows, or never comes back at all. Every customer name appears against orders they never placed, and any `SUM` computed over the result is wildly inflated. This is the signature of an accidental Cartesian product. ## Why the comma is the culprit ```sql SELECT * FROM orders o, customers c WHERE o.total > 100; ``` A comma-separated `FROM` list is the older, implicit join syntax. It means exactly what `CROSS JOIN` means: form every pairing of a row from the left source with a row from the right source. The relationship between the two tables — if any — has to be expressed as a predicate in `WHERE`. Here the only predicate, `o.total > 100`, mentions a single table. Nothing constrains which customer goes with which order, so all of them do. ## The arithmetic of the blow-up The row count is the product of the two factors after single-table filtering: - orders matching `total > 100`: 10,000 - customers: 2,000 - result: 10,000 × 2,000 = **20,000,000 rows** The useful diagnostic habit is to check whether an implausible row count *factors* into the sizes of the tables involved. If the answer is a clean product of two cardinalities, a join predicate is missing. Note that adding more single-table filters shrinks a factor but never changes the shape: the product survives until a predicate ties the two tables together. The problem compounds with more tables. In `FROM a, b, c WHERE a.id = b.a_id`, table `c` is unconstrained, so the correct `a`/`b` result is multiplied by every row of `c`. A query with one missing predicate among four tables looks almost right and is quietly wrong. ## The fix Add the predicate that relates the tables: ```sql SELECT * FROM orders o, customers c WHERE o.customer_id = c.customer_id AND o.total > 100; ``` Better, use explicit join syntax, which makes the relationship structural rather than optional: ```sql SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.total > 100; ``` With `JOIN ... ON`, omitting the condition is a syntax error the parser catches, instead of a semantic error the production database discovers at 3 a.m. That is the strongest practical argument for the explicit form: it converts a silent class of bug into a compile-time one. And where a Cartesian product genuinely is wanted, writing `CROSS JOIN` explicitly signals to every future reader that the product is intentional. ## What does *not* fix it - **`SELECT DISTINCT`.** Deduplication may shrink the printed output when the selected columns happen to repeat, but the engine still forms the product first, and the values are still wrong — a customer paired with a stranger's order is not a duplicate, it is a fabrication. Masking a cardinality bug with `DISTINCT` is a well-known anti-pattern. - **Adding `LIMIT`.** You get a small number of rows, all of them nonsense pairings. - **Tightening the single-table filter.** It shrinks one factor. The output is still a product. ## How to catch it before production Sanity-check cardinality: for a many-to-one join from a fact table to a lookup table, the result should have the same row count as the fact table after filtering. If it has more, something multiplied. Run the query with `SELECT COUNT(*)` against the same `FROM`/`WHERE` and compare it to the count of the driving table alone. In review, treat any comma-separated `FROM` list as a prompt to count predicates: N tables joined in a chain need at least N − 1 relating predicates, and a query that has fewer has at least one unconstrained table. ## Related trap worth naming The explosion above is a *Cartesian* one — pairings that should not exist at all. It is distinct from legitimate one-to-many fan-out, where every output row is a real match but a `SUM` over the joined result double-counts because the one side was repeated. Both inflate counts; only the Cartesian case is fixed by adding a predicate.