A query joining a 2,000-row customers table to a 50,000-row orders table returns 100,000,000 rows. The query is: SELECT c.name, o.total FROM customers c, orders o WHERE o.total > 0; What is the most likely cause?
Select an answer to reveal the explanation.
Short Explanation
Do the math: 2,000 times 50,000 is exactly 100 million. When your row count equals the two table sizes multiplied, you've paired every row with every row, a Cartesian product. The fix is the missing join condition, not a DISTINCT band-aid on top.
Full Explanation
Listing two tables separated by a comma creates a cross join unless a predicate relates them. The WHERE clause filters only on o.total, so nothing connects an order to its own customer, and every one of the 2,000 customers is paired with every one of the 50,000 orders: 2,000 multiplied by 50,000 is exactly 100,000,000. Adding a condition such as o.customer_id = c.customer_id, ideally with explicit JOIN ... ON syntax, restores one row per order. HAVING filters groups after aggregation; this query has no GROUP BY or aggregate, so moving the predicate into HAVING would not relate the tables and would not shrink the product. Duplicate rows in orders could inflate a result modestly, but they cannot explain a result that is exactly the product of the two row counts, and SELECT DISTINCT would merely hide part of the explosion at a large sorting cost. Comma-separated joins are valid standard SQL syntax, and the engine does apply the WHERE filter; the filter simply removes only orders with non-positive totals. Exam caveat: a result that is an exact multiple of the input sizes is the signature of a missing or incomplete join predicate. Operational check: add the join condition, rerun the query, and confirm the row count now matches the number of qualifying orders.