Join without an ON or USING clause

Claim: Correctness

A join with no ON or USING clause pairs every row of one input with every row of the other, unless something else links them. A WHERE condition or NATURAL can do that. When nothing does, the result is a cartesian product. If both tables have 1,000 rows, the result has 1,000,000 rows.

Examples

-- Pairs every user with every order
SELECT * FROM users, orders;

PostgreSQL rejects a plain JOIN with no ON or USING clause as a syntax error. A comma join with a forgotten WHERE condition runs without an error.

Why it happens

A cartesian product is rarely intentional. It usually appears when:

Tables are listed with commas in FROM (pre-SQL-92 join syntax) and the linking WHERE condition was forgotten
A table was added for a future join but the condition hasn’t been written yet

The result set grows multiplicatively with the size of each table, which can crash queries, fill memory, and lock up the database.

How to fix it

If nothing links the inputs and something should, add the join condition:

-- An explicit join with an ON clause
SELECT * FROM users
JOIN orders ON orders.user_id = users.id;

-- A comma join with the linking condition in WHERE
SELECT * FROM users, orders
WHERE orders.user_id = users.id;

Detection

Query Doctor reports this Finding in two cases:

A NATURAL JOIN.
Two or more tables listed with commas in FROM, when the WHERE clause has no = between columns of two of those tables. Each column must be written as name.column, where name is what the FROM clause calls its table: the alias if there is one, otherwise the table name without its schema.

FROM users u, orders o WHERE o.user_id = u.id and FROM public.users, public.orders WHERE orders.user_id = users.id are not reported. These comma joins are reported:

FROM users, orders WHERE user_id = users.id, because user_id does not name its table
FROM public.users, public.orders WHERE public.orders.user_id = public.users.id, because each column starts with the schema rather than the table name
FROM users, orders WHERE orders.created_at > users.created_at, because the condition is not =
FROM users u, orders o WHERE o.user_id = u.id + 1, because one side of the = is an expression rather than a column

In the first two, the WHERE condition links the tables all the same, and Postgres joins them on it.

Only tables and CTEs count toward case 2, so a comma join between a table and a subquery is not reported. A CTE that yields at most one row does not count either: one with no FROM, such as WITH target AS (SELECT ARRAY[$1]::uuid[] AS ids), a single-row VALUES, or an aggregate with no GROUP BY. Joining it pairs its one row with each row of the table, so it adds no rows.

An explicit CROSS JOIN is not reported. It names the pairing it asks for, and seed scripts use it on purpose, as in CROSS JOIN LATERAL generate_series(1, n). A NATURAL JOIN joins on every column name the two tables share, and is a cartesian product only when they share none. Query Doctor reports it either way, because the SQL text does not show the columns.

The Finding’s marker points to the second table in the FROM clause for comma joins.