SQL CROSS JOIN – Cartesian Product

CROSS JOIN Syntax

SQL

-- Explicit CROSS JOIN (preferred — makes intent clear)
SELECT columns
FROM table_a
CROSS JOIN table_b;

-- No ON clause — CROSS JOIN takes no join condition

-- Example: 3 sizes × 4 colours = 12 combinations
SELECT s.size_name, c.colour_name
FROM sizes s
CROSS JOIN colours c;

Result Size: It Grows Fast

The number of rows returned by a CROSS JOIN is rows_in_A × rows_in_B. With large tables this explodes quickly.

Table A rows	Table B rows	CROSS JOIN result
3 (sizes)	4 (colours)	12 rows
12 (months)	31 (days)	372 rows
100	100	10,000 rows
1,000	1,000	1,000,000 rows
10,000	10,000	100,000,000 rows (100 million!)

⚠️

Always check table sizes before CROSS JOIN

A CROSS JOIN on two tables of 10,000 rows each produces 100 million rows. This can exhaust memory and crash your query. Only use CROSS JOIN on small, controlled datasets or apply a WHERE clause immediately after to filter the result.

Practical Use Cases

SQL

-- 1. Generate all product variants (size × colour)
SELECT
  p.product_name,
  s.size_name,
  c.colour_name,
  CONCAT(p.product_name, ' - ', s.size_name, ' - ', c.colour_name) AS sku
FROM products p
CROSS JOIN sizes   s
CROSS JOIN colours c;

-- 2. Generate a date series for a month (31 days × 1 base date)
-- MySQL: generate all days in June 2026
SELECT
  DATE_ADD('2026-06-01', INTERVAL (ones.n + tens.n) DAY) AS calendar_date
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 ones
CROSS JOIN
  (SELECT 0 AS n UNION SELECT 10 UNION SELECT 20) AS tens
WHERE DATE_ADD('2026-06-01', INTERVAL (ones.n + tens.n) DAY)
      BETWEEN '2026-06-01' AND '2026-06-30'
ORDER BY calendar_date;

-- 3. Create a scheduling grid: every employee × every shift
SELECT
  e.name  AS employee,
  s.shift_name,
  s.shift_hours
FROM employees e
CROSS JOIN shifts s
ORDER BY e.name, s.shift_name;

SQL

-- 4. Generate test data: all combinations of status × priority
SELECT
  s.status,
  p.priority,
  CONCAT('Test ticket - ', s.status, ' - ', p.priority) AS ticket_description
FROM (SELECT 'open'   AS status UNION
      SELECT 'closed' UNION
      SELECT 'pending') s
CROSS JOIN
     (SELECT 'high'   AS priority UNION
      SELECT 'medium' UNION
      SELECT 'low') p;
-- Produces 9 rows (3 statuses × 3 priorities)

-- 5. Reachability matrix: which warehouses can serve which regions
SELECT
  w.warehouse_name,
  r.region_name
FROM warehouses w
CROSS JOIN regions r
ORDER BY w.warehouse_name, r.region_name;

Accidental Cross Joins to Avoid

The old-style SQL comma join syntax produces a Cartesian product when no WHERE condition links the tables. This is an accidental cross join — a frequent source of query bugs.

SQL

-- DANGEROUS: comma join with missing WHERE condition
-- Returns rows_employees × rows_departments (accidental Cartesian product)
SELECT e.name, d.dept_name
FROM employees e, departments d;   -- Missing: WHERE e.dept_id = d.id

-- CORRECT: explicit JOIN with ON condition
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Another accidental cross join: two table references with no link
SELECT a.col, b.col
FROM table_a a, table_b b;   -- Forgot the ON condition!

-- CORRECT
SELECT a.col, b.col
FROM table_a a
JOIN table_b b ON a.id = b.a_id;

💡

Always use explicit JOIN syntax

Never use the old comma-style FROM a, b WHERE ... syntax in new code. It is error-prone (a missing WHERE clause silently becomes a CROSS JOIN) and hard to read. Always write FROM a JOIN b ON ... explicitly. Code reviewers and linters can catch missing ON clauses more easily with explicit JOIN syntax.

Summary

CROSS JOIN: Every Combination (and the Accidental One)

A CROSS JOIN produces the Cartesian product — every row of the first table paired with every row of the second. 10 rows × 20 rows = 200 rows. There's no ON condition because nothing is being matched.

-- intentional: every size in every color
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;      -- 4 sizes × 5 colors = 20 combinations

The accidental cross join: the most common cause of a query returning wildly too many rows is a missing join condition. Listing two tables comma-separated with no WHERE link — or an INNER JOIN where you forgot the ON — silently becomes a cross join:

-- ⚠️ no join condition → every order paired with every customer
SELECT * FROM orders, customers;         -- accidental Cartesian explosion!
-- ✅ intended
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

Legit uses: generating all combinations (sizes×colors for a product matrix), building a calendar by crossing dates with categories, or creating test data. But if a query is unexpectedly slow and returns a huge row count, suspect an accidental cross join first — check that every joined table has a matching ON condition.

🏋️ Practical Exercise

Write a CROSS JOIN of two small tables.
Predict the row count (n × m).
Generate all size/color combinations.
Compare it to an INNER JOIN with no ON.
Limit the output rows.

🔥 Challenge Exercise

Use a CROSS JOIN to generate every combination of sizes and colors for a product catalog. Explain that CROSS JOIN produces the Cartesian product and why an accidental cross join (a missing ON) can explode the result size.

📋 Summary

CROSS JOIN — Every row from table A × every row from table B. No ON condition.
Result size — rows_A × rows_B. Can explode exponentially. Check sizes first.
Legitimate uses — Generating combinations (variants, schedules), date series, test data.
Accidental cross join — Comma syntax with missing WHERE. Always use explicit JOIN syntax.
Filter immediately — If you CROSS JOIN large tables, apply a WHERE clause right after to limit rows.

Interview Questions

What does a CROSS JOIN produce?
How many rows does a CROSS JOIN return?
What is a Cartesian product?
How can a CROSS JOIN happen accidentally?
When is a CROSS JOIN genuinely useful?

FAQ

When should I actually use CROSS JOIN?+

CROSS JOIN is appropriate when you genuinely need every combination: generating product SKUs from sizes and colours, producing a scheduling matrix, generating a sequence of numbers or dates, or creating test fixtures. If you cannot explain why every combination is needed, you probably want an INNER JOIN or LEFT JOIN instead.

Can I add a WHERE clause to a CROSS JOIN?+

Yes — you can filter the result of a CROSS JOIN with WHERE. However, once you add a WHERE condition that compares columns from both tables (like WHERE a.id = b.a_id), the query is functionally equivalent to an INNER JOIN. Be explicit: if you mean INNER JOIN, write INNER JOIN. Use CROSS JOIN only when no filtering condition links the two tables.

Is CROSS JOIN the same as the old comma syntax?+

Yes — FROM a, b without a WHERE condition linking them is equivalent to FROM a CROSS JOIN b. However, using explicit CROSS JOIN is far safer and more readable. The comma syntax is ambiguous — it relies on a WHERE condition to become an INNER JOIN, and a missing WHERE silently produces a Cartesian product that is hard to catch in code review.

Can I CROSS JOIN more than two tables?+

Yes: FROM a CROSS JOIN b CROSS JOIN c produces rows_a × rows_b × rows_c results. With three tables of 10 rows each you get 1,000 rows. Three tables of 100 rows give 1,000,000 rows. Be very careful — the multiplication of sizes grows very quickly with each additional table.