Course contentsShow
← Data Science
Lesson 942 of 2,14524. SQL JoinsFree lesson

Understanding CROSS JOIN Syntax and Mechanics

Learn what a CROSS JOIN does, how it produces a Cartesian product, and when this operation is appropriate.

Understanding CROSS JOIN Syntax and Mechanics

You'll learn what a CROSS JOIN does, how it creates every possible combination of rows, and when you'd actually want to use it.

What is a CROSS JOIN?

A CROSS JOIN produces a Cartesian product of two tables—every row from the first table is paired with every row from the second table. Unlike the joins you've learned (INNER, LEFT, RIGHT, FULL OUTER), a CROSS JOIN has no join condition with ON or WHERE. It simply combines everything with everything.

Think of it like this: if you have 3 shirts and 4 pairs of pants, a CROSS JOIN gives you all 12 possible outfits (3 × 4 = 12).

Basic Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

Or the older implicit syntax (without the keyword):

SELECT columns
FROM table1, table2;

How It Works

If colors has 5 rows and sizes has 3 rows, the result will have 15 rows (5 × 3). Each color appears with each size exactly once.

When to Use CROSS JOIN

CROSS JOINs are appropriate when you genuinely need all combinations:

  • Generating test data or scenarios
  • Creating calendar tables (dates × hours)
  • Building product catalogs (all size/color combinations)
  • Mathematical operations requiring pairwise comparisons

Warning

CROSS JOINs grow exponentially large. Two tables with 1,000 rows each produce 1,000,000 result rows! Use them intentionally, never accidentally by forgetting a join condition.

Key Takeaway: A CROSS JOIN creates every possible row combination between two tables (Cartesian product) without any filtering condition—use it only when you genuinely need all pairings.