CROSS JOIN

Overview

A CROSS JOIN produces the Cartesian product of two tables, pairing every row from the first table with every row from the second table.

Learning Objectives

• Generate Cartesian combinations with CROSS JOIN.
• Calculate output row counts ($N \times M$).
• Build grid matrixes and missing date-dimension combinations.

Detailed Concept Explanation

A CROSS JOIN does not take an ON clause because it matches every single row in Table A with every single row in Table B.

If Table A has $N$ rows and Table B has $M$ rows, the resulting table will have $N \times M$ rows.

Code Examples

SQL

-- Generate all possible product variant combinations (Colors x Sizes)
SELECT 
  c.color_name,
  s.size_name
FROM colors c
CROSS JOIN sizes s;

-- Generate missing store-product combinations for inventory reporting
SELECT 
  st.store_id,
  p.product_id
FROM stores st
CROSS JOIN products p;

Best Practices

• Caution on Table Size: Joining two tables with 10,000 rows each using CROSS JOIN outputs 100,000,000 rows, which can easily overwhelm memory and crash query engines.

Interview Perspective

Interactive Challenges

Summary

CROSS JOIN computes Cartesian matrixes ($N \times M$ combinations) without needing any join key condition.