CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables - every possible combination of rows from both tables.

Unlike other JOINs, CROSS JOIN does not require a join condition because it intentionally combines every row with every other row.

In this tutorial, you will learn:

• What a CROSS JOIN is and how it works
• The syntax for CROSS JOIN
• When CROSS JOIN is actually useful
• How to avoid accidental Cartesian products

A CROSS JOIN combines every row from the first table with every row from the second table.

Example

sizes table:

colors table:

CROSS JOIN Result:

Every size paired with every color = 3 × 2 = 6 rows

Explicit Syntax (Recommended)

SELECT *
FROM table1
CROSS JOIN table2;

Implicit Syntax (Comma)

SELECT *
FROM table1, table2;

Both produce the same result, but explicit CROSS JOIN is clearer.

No ON Clause!

Unlike other JOINs, CROSS JOIN does NOT use an ON clause:

-- Correct
SELECT * FROM table1 CROSS JOIN table2;

-- Wrong! CROSS JOIN doesn't need ON
SELECT * FROM table1 CROSS JOIN table2 ON table1.id = table2.id;

If you add an ON clause, you've essentially created an INNER JOIN.

The Multiplication Effect

CROSS JOIN result size = rows in A × rows in B

⚠️ Danger Zone

CROSS JOINing large tables can:

• Consume all available memory
• Take hours or days to complete
• Crash your database

Safe Practice

1. Only CROSS JOIN small tables (typically < 100 rows each)
2. Use LIMIT when experimenting
3. Check row counts first:

-- Check sizes before CROSS JOIN
SELECT 
    (SELECT COUNT(*) FROM table1) AS rows_table1,
    (SELECT COUNT(*) FROM table2) AS rows_table2,
    (SELECT COUNT(*) FROM table1) * 
    (SELECT COUNT(*) FROM table2) AS cross_join_rows;

CROSS JOIN has legitimate use cases when you actually need all combinations.

Use Case 1: Generate All Combinations

Product variants (size × color):

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

Use Case 2: Calendar/Report Scaffolding

All dates × all categories for reporting:

SELECT d.date, c.category
FROM dates d
CROSS JOIN categories c;

This ensures every category appears for every date, even with zero values.

Use Case 3: Pairing for Comparison

Compare every item to every other item:

SELECT 
    a.product_name AS product_a,
    b.product_name AS product_b
FROM products a
CROSS JOIN products b
WHERE a.product_id < b.product_id;  -- Avoid duplicates

Use Case 4: Multiplying Reference Tables

Time slots × rooms for scheduling:

SELECT t.time_slot, r.room_name
FROM time_slots t
CROSS JOIN rooms r;

The difference is intent.

Accidental Cartesian Product (BAD)

-- Forgot the WHERE clause - creates millions of rows!
SELECT c.customer_city, o.order_id
FROM olist_customers_dataset c, olist_orders_dataset o;

Intentional CROSS JOIN (GOOD)

-- Explicitly creating all combinations - clear intent
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Best Practice: Be Explicit

Never rely on comma syntax (FROM a, b) - always use explicit JOIN keywords.

You can filter CROSS JOIN results with WHERE.

Generate Then Filter

-- All unique pairs (no self-pairs, no duplicates)
SELECT 
    a.customer_state AS state_a,
    b.customer_state AS state_b
FROM (SELECT DISTINCT customer_state FROM olist_customers_dataset) a
CROSS JOIN (SELECT DISTINCT customer_state FROM olist_customers_dataset) b
WHERE a.customer_state < b.customer_state;

Pattern: Self Cross Join

Comparing a table to itself:

-- Find products in same category with different prices
SELECT 
    p1.product_id AS product1,
    p2.product_id AS product2,
    p1.product_category_name
FROM products p1
CROSS JOIN products p2
WHERE p1.product_category_name = p2.product_category_name
    AND p1.product_id < p2.product_id;

Note: Adding a WHERE condition to CROSS JOIN is similar to INNER JOIN, but CROSS JOIN + WHERE is evaluated differently by the query optimizer.

Let's practice CROSS JOIN with small result sets.

Mistake 1: CROSS JOIN on Large Tables

❌ Dangerous:

-- This creates ~10 billion rows!
SELECT * 
FROM olist_customers_dataset 
CROSS JOIN olist_orders_dataset;

✅ Safe: Only CROSS JOIN small tables or use aggregated subqueries.

Mistake 2: Using CROSS JOIN Instead of INNER JOIN

❌ Wrong approach:

SELECT *
FROM customers c
CROSS JOIN orders o
WHERE c.customer_id = o.customer_id;  -- This is just a slow INNER JOIN!

✅ Correct:

SELECT *
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Mistake 3: Forgetting CROSS JOIN is Intentional

If you don't specifically need all combinations, you probably want a different JOIN type.

Key Takeaways

1. CROSS JOIN returns all possible combinations (Cartesian product)

2. Result size = Table A rows × Table B rows

3. Syntax:

SELECT * FROM table1 CROSS JOIN table2
-- No ON clause!

4. Use CROSS JOIN when:

• Generating all possible combinations
• Creating report scaffolds
• Building complete matrices
• Pairing items for comparison

5. Safety Rules:

• Only CROSS JOIN small tables
• Check row counts before running
• Use LIMIT when experimenting
• Use explicit CROSS JOIN syntax for clarity

6. Don't confuse with:

• Accidental Cartesian products (missing WHERE/ON)
• INNER JOIN with WHERE filter

What's Next?

Next, we'll learn how to choose the right JOIN for your specific use case.