SQL: CROSS JOIN produces every combination

No join condition means every left row pairs with every right row. It is occasionally what you want - generating a calendar grid - and otherwise it is a missing ON clause.

Code

SELECT * FROM sizes CROSS JOIN colours;   -- deliberate: 4 x 6 = 24 rows

-- accidental: the old comma syntax with a forgotten WHERE
SELECT * FROM orders o, customer c;       -- 9876 x 12000 rows

-- Useful: every day in a range, so gaps show as zero
SELECT d::date, COALESCE(COUNT(o.id), 0)
  FROM generate_series('2026-08-01', '2026-08-31', '1 day') d
  LEFT JOIN orders o ON o.created_at::date = d::date
 GROUP BY d ORDER BY d;

Output

deliberate : 24 rows
accidental : 118,512,000 rows and a query that never finishes

-- Always write JOIN ... ON. The comma form makes an accidental cross join
-- look like an ordinary query.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25