SQL: A JOIN Without ON Multiplies Rows

The symptom

You join two tables and the query runs, but the row count explodes. Totals are far too high, every customer appears with unrelated orders, or every product seems connected to every category.

Why it happens

A join needs a rule that says which row from the left table matches which row from the right table. Without that predicate, the database creates a cross join: every left row is paired with every right row. Ten customers and twenty orders become two hundred joined rows.

Not every database will even parse a bare join with noon clause. Postgres and DuckDB reject it outright; MySQL and SQLite accept it as a cross join. The form that runs everywhere is the older comma syntax,from customers as c, orders as o, which reads like a list of tables rather than a join — which is why the missing condition is so easy to overlook.

This is especially dangerous because the query may not error. It can return a result that looks structured while every aggregate downstream is inflated.

The fix

Join on the matching key columns. In the Garden Shop data, each order points back to its customer through customer_id. Both queries below report count(*) rather than the rows themselves, because the unconstrained version returns more rows than a table on this page could usefully show.

select count(*) as rows_returned
from customers as c, orders as o

select count(*) as rows_returned
from customers as c
join orders as o on o.customer_id = c.customer_id

Garden Shop has 20 customers and 24 orders, and every order belongs to exactly one customer — so the join on customer_id returns one row per order. Drop the condition and each order is paired with all 20 customers instead, which is where 480 comes from: every order counted 20 times over, and every count or sum built on top of it inflated by the same factor.

Learn more

• Lesson 5.1 · Joining customers to orders
• Mistake · COUNT or SUM is too high after a JOIN
• Reference · JOIN types
• Cheatsheet · Joins