Missing JOIN condition

Severity: Warning

A JOIN without an ON clause, or multiple tables listed in a FROM clause without an explicit join condition, produces a cartesian product — every row from one table is paired with every row from the other. If both tables have 1,000 rows, the result is 1,000,000 rows.

Examples

-- Explicit JOIN with no ON clause
SELECT * FROM users JOIN orders;

-- Old-style comma join with no WHERE condition linking the tables
SELECT * FROM users, orders;

Both of these return every combination of user and order rows.

Why it happens

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

• The ON clause was accidentally omitted from a JOIN
• 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

Add the appropriate join condition:

-- Fix: add ON clause
SELECT * FROM users JOIN orders ON orders.user_id = users.id;

-- Fix: convert comma join to explicit JOIN
SELECT * FROM users
JOIN orders ON orders.user_id = users.id;

If you genuinely need a cartesian product (e.g. for generating combinations), use CROSS JOIN to make the intent explicit:

-- Intentional cartesian product
SELECT * FROM sizes CROSS JOIN colors;

Detection

Query Doctor flags this nudge in two cases:

1. A JOIN expression with no ON clause (quals)
2. Multiple tables listed directly in the FROM clause without explicit JOIN syntax

The nudge marker points to the second table in the FROM clause for comma joins.