SQL 38 🛢️ Cartesian Products with CROSS JOIN

The CROSS JOIN produces the Cartesian product of two tables: every row from the left table paired with every row from the right table. It has no join condition, and it makes no attempt to match rows by any criterion. If the left table has 100 rows and the right table has 50 rows, the result has 5,000 rows. The cross join is rarely what a query wants by accident, but it is exactly what is needed when generating combinations: every size with every color, every date with every region, every employee with every training course.

The cross join is the simplest of the joins, and it is the one that a missing join condition produces by accident. When a query writes FROM a, b without a WHERE clause that connects them, the result is a Cartesian product. This is why the explicit JOIN ... ON syntax is preferred: it makes the join condition mandatory, and the absence of a condition is visible.

This chapter covers the syntax of CROSS JOIN, the Cartesian product, the difference between an explicit cross join and an accidental one, the use cases where the cross join is the right tool, the performance implications, and the patterns for generating combinations.

Key point: CROSS JOIN produces the Cartesian product: every row from one table paired with every row from the other. It has no ON clause. Use it deliberately for generating combinations, not accidentally by omitting a join condition. A cross join of two large tables produces a result that grows multiplicatively and can overwhelm the database.

---

Why CROSS JOIN exists

The combination problem. Some queries need every combination of two sets. A product catalog needs every size paired with every color. A scheduling system needs every employee paired with every shift. A reporting tool needs every month paired with every region. The cross join generates these combinations in a single query.

The accidental join problem. When a query lists two tables in the FROM clause without a join condition, the database produces the Cartesian product. This is almost never the intent, and the result is usually wrong: duplicated rows, incorrect aggregates, and a result set that grows without bound. The CROSS JOIN is the explicit form of this operation, and the explicit form makes the intent visible.

The generation problem. The cross join is used to generate sequences and grids: a calendar of dates, a matrix of numbers, a list of all pairs. It is the SQL mechanism for expanding two small sets into a larger one.

The testing problem. The cross join is used to generate test data: every combination of input parameters, every pair of users, every permutation of states. The result is a comprehensive test matrix.

The symmetry problem. The cross join treats both tables as equal. It makes no distinction between left and right, because it makes no comparison. The result is symmetric: reversing the table order produces the same set of combinations in a different order.

---

a. Basic CROSS JOIN syntax

The CROSS JOIN keyword connects the two tables without an ON clause.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

The result has one row for each combination of a size and a color. If sizes has three rows and colors has four rows, the result has twelve rows.

The comma syntax produces the same result:

SELECT s.size, c.color
FROM sizes s, colors c;

The two forms are equivalent, but the explicit CROSS JOIN is clearer. It signals that the Cartesian product is intentional, not the result of a missing condition.

---

b. The Cartesian product

The Cartesian product is the set of all ordered pairs. In SQL, it is the result of joining two tables without a condition.

Left table	Right table	Result
3 rows	4 rows	12 rows
10 rows	10 rows	100 rows
100 rows	50 rows	5,000 rows
1,000 rows	1,000 rows	1,000,000 rows

The size of the result is the product of the sizes of the two tables. This is why a cross join of two large tables is dangerous: the result grows multiplicatively.

A small example:

-- sizes
-- ┌───────┐
-- │ size  │
-- ├───────┤
-- │ S     │
-- │ M     │
-- │ L     │
-- └───────┘

-- colors
-- ┌───────┐
-- │ color │
-- ├───────┤
-- │ Red   │
-- │ Blue  │
-- └───────┘

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

The result:

size	color
S	Red
S	Blue
M	Red
M	Blue
L	Red
L	Blue

Every size appears with every color. Six rows from three sizes and two colors.

---

c. Accidental Cartesian products

The most common cause of an accidental Cartesian product is a missing join condition in the implicit join syntax.

-- Missing WHERE clause: Cartesian product
SELECT c.name, o.order_id
FROM customers c, orders o;

This query returns every customer paired with every order. If there are 1,000 customers and 10,000 orders, the result has 10,000,000 rows. The query is almost certainly wrong.

The fix is to add the join condition:

SELECT c.name, o.order_id
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;

Or to use the explicit join syntax:

SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

The explicit syntax makes the missing condition a syntax error. The database rejects FROM a JOIN b without an ON clause, which prevents the accidental Cartesian product.

A partial join condition is another source of accidental Cartesian products. If a query joins three tables but connects only two of them, the third table is cross-joined with the result.

-- Connects customers to orders, but products is cross-joined
SELECT c.name, o.order_id, p.name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
CROSS JOIN products p;  -- missing condition: intentional or not?

If the cross join with products is intentional, the explicit keyword documents it. If it is not, the missing condition produces a result that is much larger than expected.

---

d. Use cases for CROSS JOIN

The cross join is the right tool when the query needs every combination of two sets.

Generating a product matrix. A catalog that offers sizes and colors needs every combination available for purchase.

SELECT p.name, s.size, c.color
FROM products p
CROSS JOIN sizes s
CROSS JOIN colors c
WHERE p.has_sizes AND p.has_colors;

Generating a calendar. A report that needs every date in a range paired with every region uses a date table cross-joined with a region table.

SELECT d.date, r.region
FROM dates d
CROSS JOIN regions r
WHERE d.date BETWEEN '2026-01-01' AND '2026-12-31';

Generating a test matrix. A test suite that exercises every combination of input parameters uses a cross join.

SELECT b.browser, o.os, v.version
FROM browsers b
CROSS JOIN operating_systems o
CROSS JOIN versions v;

Pairing rows within a table. A self-cross-join produces every pair of rows, including pairs of a row with itself.

SELECT a.name AS person1, b.name AS person2
FROM people a
CROSS JOIN people b
WHERE a.id < b.id;  -- exclude self-pairs and duplicates

The a.id < b.id condition excludes the diagonal (a person paired with themselves) and the reverse pairs (Alice-Bob and Bob-Alice), leaving each unordered pair exactly once.

---

e. Performance implications

The size of the result is the product of the sizes of the tables. This makes the cross join the most expensive join when the tables are large.

Left rows	Right rows	Result rows
10	10	100
100	100	10,000
1,000	1,000	1,000,000
10,000	10,000	100,000,000

A cross join of two tables with 10,000 rows each produces 100 million rows. The database must generate, sort, and return all of them, which consumes memory, CPU, and network bandwidth.

The cross join is appropriate when at least one of the tables is small. A table of 365 dates cross-joined with a table of 10 regions produces 3,650 rows, which is manageable. A table of 1,000 products cross-joined with a table of 1,000 customers produces 1,000,000 rows, which is not.

When the cross join is used, the filter conditions should be applied after the cross join to reduce the result. If the filter can be applied before the join, it reduces the size of the input and the result.

---

Complete Example Session

-- ============================================
-- PART 1: CREATE SAMPLE TABLES
-- ============================================
CREATE TABLE sizes (
    size VARCHAR(10)
);

CREATE TABLE colors (
    color VARCHAR(20)
);

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

-- ============================================
-- PART 2: BASIC CROSS JOIN
-- ============================================
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
-- 6 rows: every size with every color

-- ============================================
-- PART 3: COMMA SYNTAX
-- ============================================
SELECT s.size, c.color
FROM sizes s, colors c;
-- Same 6 rows

-- ============================================
-- PART 4: ACCIDENTAL CARTESIAN PRODUCT
-- ============================================
-- Missing WHERE clause
SELECT c.name, o.order_id
FROM customers c, orders o;
-- Every customer paired with every order

-- ============================================
-- PART 5: FIXING WITH EXPLICIT JOIN
-- ============================================
SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
-- Only matched rows

-- ============================================
-- PART 6: PRODUCT MATRIX
-- ============================================
SELECT p.name, s.size, c.color
FROM products p
CROSS JOIN sizes s
CROSS JOIN colors c
WHERE p.has_sizes AND p.has_colors;

-- ============================================
-- PART 7: CALENDAR GENERATION
-- ============================================
SELECT d.date, r.region
FROM dates d
CROSS JOIN regions r
WHERE d.date BETWEEN '2026-01-01' AND '2026-12-31'
ORDER BY d.date, r.region;

-- ============================================
-- PART 8: TEST MATRIX
-- ============================================
SELECT b.browser, o.os, v.version
FROM browsers b
CROSS JOIN operating_systems o
CROSS JOIN versions v;

-- ============================================
-- PART 9: SELF-CROSS-JOIN FOR PAIRS
-- ============================================
SELECT a.name AS person1, b.name AS person2
FROM people a
CROSS JOIN people b
WHERE a.id < b.id;
-- Each unordered pair once

-- ============================================
-- PART 10: CROSS JOIN WITH FILTER
-- ============================================
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c
WHERE c.color <> 'Blue' OR s.size = 'S';
-- Filters the combinations

These ten parts cover creating the tables, a basic cross join, the comma syntax, an accidental Cartesian product, fixing with an explicit join, a product matrix, calendar generation, a test matrix, a self-cross-join for pairs, and a cross join with a filter.

---

Quick Reference

CROSS JOIN Syntax

Form	Example
Explicit	FROM a CROSS JOIN b
Comma	FROM a, b
With filter	FROM a CROSS JOIN b WHERE ...
Self	FROM t a CROSS JOIN t b

Result Size

Left rows	Right rows	Result rows
3	4	12
10	10	100
100	50	5,000
1,000	1,000	1,000,000
10,000	10,000	100,000,000

Cross Join vs Other Joins

Join	Condition	Result
CROSS JOIN	None	All combinations
INNER JOIN	Matching	Matched rows only
LEFT JOIN	Matching	All left + matched right
RIGHT JOIN	Matching	All right + matched left
FULL OUTER JOIN	Matching	All rows from both

Use Cases

Use case	Example
Product matrix	Sizes × colors
Calendar	Dates × regions
Test matrix	Browsers × OSes × versions
Self-pairs	People × people

Accidental Cartesian Product

Cause	Fix
Missing WHERE clause	Add join condition
Partial join condition	Connect all tables
Implicit join syntax	Use explicit JOIN … ON
Forgotten condition	Add ON to every join

---

Best Practices

✅ Do This:

-- Use the explicit CROSS JOIN keyword
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

-- Filter after the cross join
SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c
WHERE c.color <> 'Blue';

-- Exclude self-pairs and duplicates
SELECT a.name, b.name
FROM people a CROSS JOIN people b
WHERE a.id < b.id;

-- Cross join small tables
FROM dates d CROSS JOIN regions r

❌ Don’t Do This:

-- Accidental Cartesian product
FROM customers c, orders o  -- ❌ missing WHERE

-- Cross join large tables
FROM products p CROSS JOIN customers c  -- ❌ 1M+ rows

-- Forget the join condition for one table
FROM a JOIN b ON a.id = b.id CROSS JOIN c  -- ⚠️ intentional?

-- Use CROSS JOIN when INNER JOIN is intended
FROM a CROSS JOIN b WHERE a.id = b.id  -- ❌ use JOIN ... ON

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Result far larger than expected	Missing join condition	Add the condition
Duplicate rows	Accidental Cartesian product	Use explicit JOIN … ON
Slow query	Cross join of large tables	Reduce input size or filter early
Aggregates wrong	Cross join inflates row count	Verify the join condition
Self-pairs duplicated	No a.id < b.id filter	Add the filter
Filter applied too late	Filter after the cross join	Filter before if possible

---

Real-World Examples

1. Sizes and Colors

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;

2. Calendar Grid

SELECT d.date, r.region FROM dates d CROSS JOIN regions r;

3. Test Matrix

SELECT b.browser, o.os FROM browsers b CROSS JOIN operating_systems o;

4. Unordered Pairs

SELECT a.name, b.name FROM people a CROSS JOIN people b
WHERE a.id < b.id;

5. Product Availability

SELECT p.name, s.size FROM products p CROSS JOIN sizes s
WHERE p.has_sizes;

6. Date Range

SELECT d.date, r.region FROM dates d CROSS JOIN regions r
WHERE d.date BETWEEN '2026-01-01' AND '2026-12-31';

7. Filter Combinations

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c
WHERE c.color <> 'Blue';

8. Accidental Fix

-- Before: FROM customers c, orders o
-- After:
FROM customers c JOIN orders o ON c.customer_id = o.customer_id

9. Explicit Documentation

FROM sizes s CROSS JOIN colors c  -- intentional cross join

10. Self-Cross-Join with Condition

SELECT a.id, b.id FROM items a CROSS JOIN items b
WHERE a.id <> b.id;

---

Visual

Cartesian Product

┌──────────────────────────────────────────────────────────────┐
│  sizes: S, M, L        colors: Red, Blue                     │
│                                                              │
│  CROSS JOIN:                                                 │
│  ┌──────┬───────┐                                            │
│  │ size │ color │                                            │
│  ├──────┼───────┤                                            │
│  │ S    │ Red   │                                            │
│  │ S    │ Blue  │                                            │
│  │ M    │ Red   │                                            │
│  │ M    │ Blue  │                                            │
│  │ L    │ Red   │                                            │
│  │ L    │ Blue  │                                            │
│  └──────┴───────┘                                            │
│                                                              │
│  3 rows × 2 rows = 6 rows                                    │
└──────────────────────────────────────────────────────────────┘

Accidental vs Intentional

┌──────────────────────────────────────────────────────────────┐
│  ACCIDENTAL:                                                 │
│  FROM customers c, orders o                                  │
│  └── No condition → Cartesian product                        │
│  └── Result: every customer × every order                    │
│                                                              │
│  INTENTIONAL:                                                │
│  FROM sizes s CROSS JOIN colors c                            │
│  └── Explicit keyword → documented intent                    │
│  └── Result: every size × every color                        │
│                                                              │
│  The explicit form makes the intent visible.                 │
└──────────────────────────────────────────────────────────────┘

Result Size Growth

┌──────────────────────────────────────────────────────────────┐
│  Left × Right = Result                                       │
│                                                              │
│  10 × 10     = 100                                           │
│  100 × 100   = 10,000                                        │
│  1,000 × 1,000 = 1,000,000                                   │
│  10,000 × 10,000 = 100,000,000                               │
│                                                              │
│  The result grows multiplicatively.                          │
│  Cross join only when at least one table is small.           │
└──────────────────────────────────────────────────────────────┘

Self-Cross-Join for Pairs

┌──────────────────────────────────────────────────────────────┐
│  people: Alice (1), Bob (2), Carol (3)                       │
│                                                              │
│  CROSS JOIN with a.id < b.id:                                │
│  ┌────────┬────────┐                                         │
│  │ Alice  │ Bob    │                                         │
│  │ Alice  │ Carol  │                                         │
│  │ Bob    │ Carol  │                                         │
│  └────────┴────────┘                                         │
│                                                              │
│  Each unordered pair appears once.                           │
│  Self-pairs and reverse pairs are excluded.                  │
└──────────────────────────────────────────────────────────────┘

---

Summary

Item	Value
CROSS JOIN	Cartesian product of two tables
Condition	None
Result size	Left rows × right rows
Syntax	FROM a CROSS JOIN b
Comma syntax	FROM a, b
Accidental cause	Missing join condition
Fix	Add ON or WHERE
Use cases	Combinations, calendars, test matrices
Self-pairs	FROM t a CROSS JOIN t b WHERE a.id < b.id
Performance	Expensive on large tables

Key takeaways:

• CROSS JOIN produces the Cartesian product. Every row from the left table is paired with every row from the right table. The result size is the product of the two table sizes.
• The cross join has no ON clause. It makes no comparison between the tables. The result is symmetric, and reversing the table order produces the same combinations in a different order.
• The most common use is generating combinations. A product matrix, a calendar, a test matrix, or a list of pairs are all cross-join use cases. The cross join expands two small sets into a larger one.
• The accidental Cartesian product is a common bug. A missing join condition in the implicit join syntax produces the Cartesian product. The explicit JOIN ... ON syntax makes the condition mandatory and prevents the mistake.
• The result grows multiplicatively. A cross join of two tables with 1,000 rows each produces 1,000,000 rows. The cross join is appropriate only when at least one table is small.
• Filter conditions reduce the result. A filter applied after the cross join reduces the output. If the filter can be applied before the join, it reduces the input and the cost.
• Self-cross-joins generate pairs. The a.id < b.id condition excludes self-pairs and reverse pairs, leaving each unordered pair exactly once.

Remember: The CROSS JOIN is the simplest join and the most dangerous one. It produces every combination of two tables, and its result size is the product of the input sizes. When the cross join is intentional, it is the right tool for generating combinations, calendars, test matrices, and pairs. When it is accidental, it is the result of a missing join condition, and it produces a result that is larger than expected and usually wrong. The explicit CROSS JOIN keyword documents the intent, and the explicit JOIN ... ON syntax prevents the mistake. Use the cross join deliberately, on small tables, with filters that reduce the result.

---

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!