by
SQL Tutorial › Joins & Set Operators › CROSS JOIN

SQL CROSS JOIN

IntermediateLesson 37 of 698 min read4 queries run on MySQL 8.4

A CROSS JOIN pairs every row of one table with every row of another, with no join condition. 3 rows × 4 rows = 12 rows. That sounds useless until you need every combination: all sizes in all colours, every product in every month, every employee on every training day. It is also what you get by accident when you forget a join condition.

Advertisement
In this lesson
  • Build a Cartesian product with CROSS JOIN
  • Generate combinations and report grids
  • Fill gaps so missing combinations show 0
  • Recognise an accidental cross join

CROSS JOIN syntax

MySQL
SELECT columnsFROM table1CROSS JOIN table2;

Every combination

SQL
CREATE TABLE sizes  (size VARCHAR(5));CREATE TABLE colours (colour VARCHAR(10));INSERT INTO sizes VALUES ('S'), ('M'), ('L');INSERT INTO colours VALUES ('Black'), ('White');SELECT s.size, c.colour, CONCAT(c.colour, '-', s.size) AS skuFROM sizes sCROSS JOIN colours cORDER BY c.colour, s.size;
ResultMySQL 8.4
mysql> CREATE TABLE sizes (size VARCHAR(5))
Query OK, 0 rows affected
mysql> CREATE TABLE colours (colour VARCHAR(10))
Query OK, 0 rows affected
mysql> INSERT INTO sizes VALUES ('S'), ('M'), ('L')
Query OK, 3 rows affected
mysql> INSERT INTO colours VALUES ('Black'), ('White')
Query OK, 2 rows affected
mysql> SELECT s.size, c.colour, CONCAT(c.colour, '-', s.size) AS sku
sizecoloursku
LBlackBlack-L
MBlackBlack-M
SBlackBlack-S
LWhiteWhite-L
MWhiteWhite-M
SWhiteWhite-S
6 rows in set
How this query works, step by step
  1. sizes has 3 rows and colours has 2.
  2. CROSS JOIN pairs each size with each colour: 3 × 2 = 6 rows.
  3. CONCAT builds a product code for each combination.

Fill gaps in a report

Units sold per category per month. A plain GROUP BY simply leaves out combinations with no sales. Build a grid of every category × every month with a cross join, aggregate the real sales separately, then LEFT JOIN the sales onto the grid:

MySQL
SELECT g.category, g.month, COALESCE(s.units, 0) AS unitsFROM (  SELECT c.category, m.month  FROM (SELECT DISTINCT category FROM products) c  CROSS JOIN (SELECT DISTINCT DATE_FORMAT(order_date, '%Y-%m') AS month FROM orders) m) gLEFT JOIN (  SELECT p.category, DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(oi.quantity) AS units  FROM orders o  JOIN order_items oi ON oi.order_id = o.order_id  JOIN products p ON p.product_id = oi.product_id  GROUP BY p.category, month) s ON s.category = g.category AND s.month = g.monthORDER BY g.category, g.month;
ResultMySQL 8.4
categorymonthunits
Electronics2025-011
Electronics2025-022
Electronics2025-032
Electronics2025-041
Electronics2025-051
Electronics2025-060
Furniture2025-011
Furniture2025-021
Furniture2025-031
Furniture2025-044
Furniture2025-050
Furniture2025-060
Stationery2025-015
Stationery2025-020
Stationery2025-0314
Stationery2025-040
Stationery2025-050
Stationery2025-065
18 rows in set
How this query works, step by step
  1. The grid g is every category crossed with every month that has orders: 3 × 6 = 18 rows.
  2. The subquery s is the real sales, one row per category and month that sold something.
  3. The LEFT JOIN keeps the whole grid; months with no sales for a category get NULL, which COALESCE turns into 0.

The accidental cross join

Listing two tables with a comma and forgetting the WHERE produces a cross join:

SQL
SELECT COUNT(*) AS rows_returnedFROM employees, departments;
ResultMySQL 8.4
rows_returned
60
1 row in set
How this query works, step by step
  1. FROM employees, departments with no condition pairs all 12 employees with all 5 departments.
  2. 12 × 5 = 60 rows, almost all of them meaningless.
  3. On two tables of 100,000 rows this is 10 billion rows and can bring a server down. Always write explicit JOIN ... ON.

CROSS JOIN with a single row

Crossing with a one-row result attaches a value to every row, for example the company average:

SQL
SELECT e.first_name, e.salary, a.avg_salary,       e.salary - a.avg_salary AS diff_from_avgFROM employees eCROSS JOIN (SELECT ROUND(AVG(salary)) AS avg_salary FROM employees) aWHERE e.dept_id = 1;
ResultMySQL 8.4
first_namesalaryavg_salarydiff_from_avg
Asha1250008833336667
Rahul98000883339667
Priya7200088333-16333
Karan7200088333-16333
Amit2500088333-63333
5 rows in set

Quick quiz

1. Table A has 5 rows and B has 4. How many rows does A CROSS JOIN B return?

2. What does SELECT * FROM a, b without WHERE produce?

3. Why cross join categories with months in a report?

CROSS JOIN FAQ

What is a Cartesian product in SQL?

The set of all combinations of rows from two tables. A CROSS JOIN returns it.

What is the difference between CROSS JOIN and INNER JOIN?

CROSS JOIN has no join condition and returns every combination. INNER JOIN returns only the combinations that satisfy the ON condition.

Is CROSS JOIN slow?

The result grows as rows × rows, so it is fine for small lookup lists but dangerous on large tables.