What is a CROSS JOIN?
Quick Answer
A CROSS JOIN returns the Cartesian product — every row of the first table paired with every row of the second (M × N rows). It has no ON clause. Deliberate uses include generating all combinations (sizes × colours) or a calendar; an accidental cross join (a missing join predicate) is a common cause of runaway queries.
A cross join gives you every combination — each row on the left paired with each row on the right, so M rows times N rows. There's no ON clause. It's intentional when you want all combinations, like every size crossed with every colour, or building out a date table. The thing to watch is the accidental version: if you list two tables in the FROM with a comma and forget the join condition, you get a cross join, and on real tables that's millions of rows.
SELECT s.Size, c.Colour FROM Sizes s CROSS JOIN Colours c;→ every size/colour pair.- Pairing a single-row table (e.g. a computed date range) against another table is a tidy way to add constant columns.
- A comma-separated
FROM A, Bwith noWHEREjoin condition is an implicit cross join — the classic accidental huge result. - Related:
CROSS APPLYis like a cross join where the right side is a table expression that can reference the left row.