Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT a.x, b.y
FROM a
CROSS JOIN b;

Try it

SQL Every size in every color ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL A complete customer-by-genre matrix ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL Counting the size of a product ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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 ON is an accidental cross join. With the comma syntax, a missing WHERE condition silently produces a Cartesian product. Explicit JOIN ... ON syntax makes the mistake impossible, and in most databases a plain JOIN without ON is a syntax error.
  • Matrix pattern. Cross join two lists of dimensions (customers and genres, days and products), then LEFT JOIN the facts and use COUNT or COALESCE to show zeros for missing combinations.
  • Combinations with a filter. A cross join followed by a WHERE that 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 NULL surprises. Because there is no comparison, NULL values are just carried along.
  • Generating sequences usually uses a recursive CTE or generate_series instead; see WITH RECURSIVE.