SQL Reference ยท Joins and Subqueries
SQL CROSS JOIN
Reference for SQL CROSS JOIN: build the Cartesian product of two tables, generate every combination of values, and avoid accidental row explosions.
A CROSS JOIN pairs every row of the left table with every row of the right table. If the left side has 3 rows and the right side has 4, the result has 12 rows. There is no join condition: the result is the Cartesian product of the two inputs.
That sounds dangerous, and it can be: two tables of 100,000 rows each would produce ten billion rows. But used on purpose with small inputs, a cross join is the cleanest way to generate every combination of two lists, build a calendar grid, or create a "complete matrix" that you later fill in with a LEFT JOIN.
SELECT a.x, b.y FROM a CROSS JOIN b;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT columns FROM table_a CROSS JOIN table_b; -- Older comma syntax, same result SELECT columns FROM table_a, table_b;
| Part | Description |
|---|---|
table_a, table_b |
Any tables, views, derived tables or CTEs. |
CROSS JOIN |
Pairs every row from the left with every row from the right. It takes no ON or USING clause in standard SQL. |
Result size: rows(a) * rows(b). If either side is empty, the result is empty. Columns from both inputs are returned.
Dialect differences
| Database | Notes |
|---|---|
| SQLite | Supports CROSS JOIN and the comma form. SQLite never reorders tables on either side of a CROSS JOIN, so it is sometimes used to force the join order. |
| PostgreSQL | Supports both forms. |
| MySQL | CROSS JOIN and INNER JOIN are interchangeable; MySQL even allows CROSS JOIN ... ON. Write CROSS JOIN only when you mean "no condition". |
| SQL Server | Supports both forms. |
| Oracle | Supports CROSS JOIN since version 9i, and the comma form. |
-- PostgreSQL: generate the combinations without any table SELECT d::date AS day, s AS shift FROM generate_series('2025-01-01'::date, '2025-01-03', '1 day') AS d CROSS JOIN (VALUES ('early'), ('late')) AS shifts(s);
Usage notes
- Check the row counts first. Multiply the table sizes in your head. A cross join of two tables with thousands of rows is rarely what you want.
- A forgotten
ONis an accidental cross join. With the comma syntax, a missingWHEREcondition silently produces a Cartesian product. ExplicitJOIN ... ONsyntax makes the mistake impossible, and in most databases a plainJOINwithoutONis a syntax error. - Matrix pattern. Cross join two lists of dimensions (customers and genres, days and products), then
LEFT JOINthe facts and useCOUNTorCOALESCEto show zeros for missing combinations. - Combinations with a filter. A cross join followed by a
WHEREthat compares the two sides behaves like a join with a non-equality condition, for example pairing each item with every cheaper item. - Pairing a value with every row. Cross joining a one-row subquery (such as an overall average) attaches that value to each row of the other table.
- No
NULLsurprises. Because there is no comparison,NULLvalues are just carried along. - Generating sequences usually uses a recursive CTE or
generate_seriesinstead; see WITH RECURSIVE.
Related
- INNER JOIN, LEFT JOIN, self join, NATURAL JOIN and USING, VALUES
- Lessons: CROSS JOIN, Joins