CROSS JOIN — MySQL

CROSS JOIN in MySQL

A CROSS JOIN produces the cartesian product of two tables: every row from the left table is paired with every row from the right table. If the left table has 5 rows and the right has 4, the result has 20 rows. Unlike other JOINs, there is no matching condition — every combination is returned by design.

CROSS JOIN Syntax

SQL

-- Explicit CROSS JOIN keyword
SELECT *
FROM table_a
CROSS JOIN table_b;

-- No ON or USING clause — that's intentional
-- Every row in table_a × every row in table_b

-- Simple example: 4 sizes × 3 colors = 12 combinations
SELECT s.size, c.color
FROM sizes  AS s
CROSS JOIN colors AS c;

Concrete Cartesian Product Example

SQL

CREATE TEMPORARY TABLE sizes  (size  VARCHAR(5));
CREATE TEMPORARY TABLE colors (color VARCHAR(10));

INSERT INTO sizes  VALUES ('S'), ('M'), ('L'), ('XL');
INSERT INTO colors VALUES ('Red'), ('Blue'), ('Green');

-- Result: 4 × 3 = 12 rows
SELECT s.size, c.color
FROM sizes AS s
CROSS JOIN colors AS c
ORDER BY s.size, c.color;

size	color
L	Blue
L	Green
L	Red
M	Blue
M	Green
M	Red
S	Blue
S	Green
S	Red
XL	Blue
XL	Green
XL	Red

Generating a Number Sequence Table

A CROSS JOIN of digit tables produces a sequence of integers without needing a real sequence table. This is a foundational pattern for generating calendar tables, test data, and gap analysis:

SQL

-- Generate integers 0–999 by crossing three digit tables
SELECT a.n + b.n * 10 + c.n * 100 AS num
FROM
  (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN
  (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS b
CROSS JOIN
  (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS c
ORDER BY num;

Tip

Store this as a real numbers table with values 0–99999: CREATE TABLE numbers AS SELECT .... A numbers table is reusable for calendar generation, gap detection, and test data — much faster than inline CROSS JOIN subqueries.

Calendar Table Generation

A calendar table (one row per date) is extremely useful for date-range reporting. Generate all dates in a year by crossing digit tables to produce offsets, then add them to a start date:

SQL

-- Generate every date in 2024 (366 days for leap year)
SELECT DATE_ADD('2024-01-01', INTERVAL seq.n DAY) AS calendar_date,
       DAYNAME(DATE_ADD('2024-01-01', INTERVAL seq.n DAY)) AS day_name,
       MONTH(DATE_ADD('2024-01-01', INTERVAL seq.n DAY))   AS month_num
FROM (
  SELECT a.n + b.n * 10 + c.n * 100 AS n
  FROM
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
  CROSS JOIN
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
     UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
  CROSS JOIN
    (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) c
) AS seq
WHERE seq.n < 366
ORDER BY calendar_date;

-- Save for repeated use
CREATE TABLE calendar_2024 AS
SELECT DATE_ADD('2024-01-01', INTERVAL n DAY) AS dt
FROM (
  SELECT a.n + b.n * 10 + c.n * 100 AS n
  FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
  CROSS JOIN
       (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
  CROSS JOIN
       (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) c
) seq WHERE n < 366;

Product Variant Generation

SQL

-- E-commerce: create all SKU combinations for a new product
-- Given sizes, colors, and materials — generate every variant row

CREATE TEMPORARY TABLE materials (material VARCHAR(20));
INSERT INTO materials VALUES ('Cotton'), ('Polyester'), ('Wool');

-- 4 sizes × 3 colors × 3 materials = 36 variants
INSERT INTO product_variants (product_id, size, color, material, sku)
SELECT
  42 AS product_id,
  s.size,
  c.color,
  m.material,
  CONCAT('PROD-42-', s.size, '-',
         UPPER(LEFT(c.color, 3)), '-',
         UPPER(LEFT(m.material, 3))) AS sku
FROM sizes    AS s
CROSS JOIN colors     AS c
CROSS JOIN materials  AS m
ORDER BY s.size, c.color, m.material;

Gap Detection in Time Series

LEFT JOIN a calendar table against your data to reveal dates with no activity:

SQL

-- Find days in January 2024 with no orders
SELECT cal.dt AS missing_date
FROM (
  SELECT DATE_ADD('2024-01-01', INTERVAL n DAY) AS dt
  FROM (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
  CROSS JOIN
       (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) b
  WHERE a.n + b.n * 10 < 31
) AS cal
LEFT JOIN orders o ON DATE(o.created_at) = cal.dt
WHERE o.id IS NULL
ORDER BY cal.dt;

Report Matrix — All Customers x All Months

Business reports often need to show every combination even when data is absent (e.g., all months for all customers, with zeros for months with no sales). CROSS JOIN all customers with all months, then LEFT JOIN the actual sales data:

SQL

-- Monthly sales report: show all 12 months for every customer
-- Even if a customer had no orders that month (shows 0)
SELECT
  c.customer_id,
  c.name,
  m.month_num,
  COALESCE(SUM(o.total), 0) AS monthly_revenue
FROM customers AS c
CROSS JOIN (
  SELECT 1 AS month_num UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
  UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
  UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
) AS m
LEFT JOIN orders o
  ON  o.customer_id = c.customer_id
  AND MONTH(o.created_at) = m.month_num
  AND YEAR(o.created_at)  = 2024
GROUP BY c.customer_id, c.name, m.month_num
ORDER BY c.name, m.month_num;

Implicit CROSS JOIN (Comma Notation)

A comma between table names in FROM without a WHERE join condition is an implicit CROSS JOIN. This is how accidental cartesian products are introduced in legacy code.

SQL

-- Implicit CROSS JOIN — intentional (same as CROSS JOIN keyword)
SELECT s.size, c.color
FROM sizes AS s, colors AS c;

-- DANGEROUS: looks like an INNER JOIN but the WHERE join condition is missing
SELECT c.first_name, o.order_id
FROM customers AS c, orders AS o;
-- Returns customers * orders rows — ACCIDENTAL cartesian product

-- The correct version adds the join condition in WHERE
SELECT c.first_name, o.order_id
FROM customers AS c, orders AS o
WHERE c.customer_id = o.customer_id;  -- now an implicit INNER JOIN

Warning

Always use explicit JOIN syntax (JOIN ... ON) in new code. Comma-separated FROM lists are a common source of accidental cross joins that can return billions of rows and bring down production databases.

CROSS JOIN with WHERE to Exclude Unwanted Combinations

Adding a WHERE clause to a CROSS JOIN narrows the result — useful for generating all combinations and then filtering out ones that already exist:

SQL

-- Find all employee × shift slot pairs NOT yet assigned this week
SELECT
  e.employee_id,
  e.full_name,
  s.slot_id,
  s.slot_start,
  s.slot_end
FROM employees AS e
CROSS JOIN shift_slots AS s
WHERE s.shift_date = '2024-07-15'
  AND NOT EXISTS (
    SELECT 1
    FROM shift_assignments sa
    WHERE sa.employee_id = e.employee_id
      AND sa.slot_id     = s.slot_id
  )
ORDER BY s.slot_start, e.full_name;

Scheduling Matrix Example

SQL

-- Generate an open schedule grid for a work week
SELECT
  d.day_name,
  t.slot_label,
  t.start_time,
  t.end_time
FROM (
  SELECT 'Monday'     AS day_name, 1 AS day_num UNION ALL
  SELECT 'Tuesday',   2                          UNION ALL
  SELECT 'Wednesday', 3                          UNION ALL
  SELECT 'Thursday',  4                          UNION ALL
  SELECT 'Friday',    5
) AS d
CROSS JOIN (
  SELECT '9am-1pm'  AS slot_label, '09:00' AS start_time, '13:00' AS end_time UNION ALL
  SELECT '1pm-5pm',                '13:00',                '17:00'             UNION ALL
  SELECT '5pm-9pm',                '17:00',                '21:00'
) AS t
ORDER BY d.day_num, t.start_time;
-- Produces 15 rows: 5 days × 3 time slots

Numbers Table as a Permanent Utility

A permanent numbers table is more efficient than inline CROSS JOIN digit sequences. Create it once and use it for all date generation, gap detection, and test data:

SQL

-- Create a reusable numbers table (0 to 99,999)
CREATE TABLE numbers (n INT UNSIGNED PRIMARY KEY);

INSERT INTO numbers (n)
SELECT a.n + b.n * 10 + c.n * 100 + d.n * 1000 + e.n * 10000 AS n
FROM
  (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
CROSS JOIN
  (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
CROSS JOIN
  (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
CROSS JOIN
  (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
CROSS JOIN
  (SELECT 0 n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
   UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) e;
-- 100,000 rows inserted in < 1 second

-- Use the numbers table to generate any date range
SELECT DATE_ADD('2024-01-01', INTERVAL n DAY) AS dt
FROM numbers
WHERE n < 366  -- 2024 is a leap year
ORDER BY dt;

Using CROSS JOIN for Pivoting Data

CROSS JOIN combined with conditional aggregation can pivot row data into columns without using CASE WHEN repetition:

SQL

-- Pivot quarterly sales by product using CROSS JOIN + conditional SUM
SELECT
  p.product_name,
  SUM(CASE WHEN q.quarter = 1 THEN s.amount ELSE 0 END) AS q1,
  SUM(CASE WHEN q.quarter = 2 THEN s.amount ELSE 0 END) AS q2,
  SUM(CASE WHEN q.quarter = 3 THEN s.amount ELSE 0 END) AS q3,
  SUM(CASE WHEN q.quarter = 4 THEN s.amount ELSE 0 END) AS q4
FROM products AS p
CROSS JOIN (
  SELECT 1 AS quarter UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
) AS q
LEFT JOIN sales s
  ON s.product_id = p.product_id
  AND QUARTER(s.sale_date) = q.quarter
  AND YEAR(s.sale_date) = 2024
GROUP BY p.product_id, p.product_name
ORDER BY p.product_name;

CROSS JOIN Performance Warning

Result size grows multiplicatively. Always estimate the output size before running a CROSS JOIN on real tables:

SQL

-- Estimate result size before running
SELECT
  (SELECT COUNT(*) FROM table_a) *
  (SELECT COUNT(*) FROM table_b) AS estimated_rows;

-- If dangerously large, add WHERE conditions or LIMIT for previewing
SELECT *
FROM table_a
CROSS JOIN table_b
WHERE table_a.type = 'active'   -- filter before cross product explodes
LIMIT 100;

Left rows	Right rows	Result rows
10	10	100
100	50	5,000
1,000	1,000	1,000,000
10,000	10,000	100,000,000 — dangerous
100,000	100,000	10,000,000,000 — will crash the server

Generating Test Data with CROSS JOIN

SQL

-- Generate realistic test user data by crossing name parts
INSERT INTO test_users (username, email, created_at)
SELECT
  CONCAT(f.first_name, '_', l.last_name) AS username,
  LOWER(CONCAT(f.first_name, '.', l.last_name, '@', d.domain)) AS email,
  DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND() * 1460) DAY) AS created_at
FROM
  (SELECT 'alice' AS first_name UNION SELECT 'bob' UNION SELECT 'carol'
   UNION SELECT 'dave' UNION SELECT 'eve' UNION SELECT 'frank'
   UNION SELECT 'grace' UNION SELECT 'henry' UNION SELECT 'iris'
   UNION SELECT 'james') AS f
CROSS JOIN
  (SELECT 'smith' AS last_name UNION SELECT 'jones' UNION SELECT 'patel'
   UNION SELECT 'garcia' UNION SELECT 'nguyen') AS l
CROSS JOIN
  (SELECT 'example.com' AS domain UNION SELECT 'test.org' UNION SELECT 'demo.net') AS d;
-- Generates 10 * 5 * 3 = 150 test user rows

CROSS JOIN vs INNER JOIN — The Key Distinction

Adding an ON clause to a CROSS JOIN transforms it into a semantically equivalent INNER JOIN. Knowing this helps you understand that CROSS JOIN is just INNER JOIN without a filter condition:

SQL

-- These two are logically identical:

-- CROSS JOIN with WHERE filter
SELECT c.name, p.name AS product
FROM customers AS c
CROSS JOIN products AS p
WHERE p.category_id = c.preferred_category_id;

-- INNER JOIN with ON clause (preferred style)
SELECT c.name, p.name AS product
FROM customers AS c
INNER JOIN products AS p ON p.category_id = c.preferred_category_id;

-- Use CROSS JOIN syntax only when you INTEND a cartesian product.
-- Use JOIN ... ON when you intend a filtered join — it documents intent clearly.

Real-World CROSS JOIN Use Cases Summary

Use Case	Left table	Right table	Result
Product variants	sizes (4 rows)	colors × materials	All size/color/material combinations
Calendar table	digits 0-9	digits 0-9 × (0-3)	365/366 sequential dates
Report matrix	All customers	All months (1-12)	12 rows per customer, even with no data
Gap detection	Generated date sequence	Actual data (LEFT JOIN)	Dates where no data exists
Scheduling grid	Days of week	Time slots	Every day × every time slot combination
Test data	Name prefixes	Surnames × domains	Large set of realistic test user combinations

Use CROSS JOIN intentionally for combinations, calendar tables, number sequences, and test data generation
Always estimate result size: left_count * right_count = output rows
Use the explicit CROSS JOIN keyword to document intent — not comma notation in FROM
Add WHERE or LIMIT when previewing large cartesian products
Use LEFT JOIN against a generated date/number series to detect gaps in time series data
Store frequently used calendar or number tables as real tables for better performance
A CROSS JOIN with a WHERE or ON clause is equivalent to an INNER JOIN — use JOIN syntax for filtered joins