SR

CROSS JOIN

Generate every possible combination of rows from two tables

Intermediate 8 minCROSS JOINcartesian

Cartesian Product

CROSS JOIN produces every possible combination of rows from two tables. No ON condition — it generates the pure Cartesian product. Here's what to know:

Multiplicative output — 3 rows × 4 rows = 12 result rows. Two 1000-row tables produce 1,000,000 rows. Always use CROSS JOIN deliberately and only with small tables to avoid accidentally generating massive result sets.
Practical uses — Generating all date-category combinations for a report scaffold, creating test data from template values, building comparison matrices, and pairing every item with every other item for analysis.
No ON clause — Unlike INNER and LEFT JOIN, CROSS JOIN has no matching condition at all. Every single left row pairs with every single right row. The result size is always the product of both table sizes.

Knowledge Canvas

How CROSS JOIN Works

Generate every combination

  • Produces cartesian product: every left row × every right row
  • No ON clause needed — all combinations are included
  • 10 rows × 10 rows = 100 rows in the result
  • Rarely used by accident — but devastating when it happens

Cross Join Dangers

Row explosion warning

  • 1,000 × 1,000 = 1,000,000 rows — grows quadratically
  • Missing ON in INNER JOIN: a silent cross join in SQLite and MySQL, a syntax error in PostgreSQL, SQL Server and Oracle
  • Most "cross join" results are bugs, not features
  • Always verify row counts after joins

Legitimate Uses

When cross join is actually correct

  • Generate all date × product combinations (fill gaps)
  • Create lookup grids: sizes × colors
  • Test data generation
  • Scaffold: all users × all permission types

Safety Rules

Keep cross joins under control

  • Only cross join small reference tables
  • Always add WHERE or LIMIT as a safety net
  • Document intent: comment why cross join is deliberate
  • Verify expected row count before running

Syntax

Syntax Template
1SELECT
2 a.col,
3 b.col
4FROM table_a a
5CROSS JOIN table_b b;
CROSS JOINNo ON clause — all combinations
WarningOutput = rows_A × rows_B — use with small tables

Sample Data

products
10 rows
idnamecategory
P101Wireless MouseElectronics
P102Office DeskFurniture
P103LED Desk LampFurniture
P104USB-C CableElectronics
P105Bluetooth SpeakerElectronics
P106Office ChairFurniture
P107Coffee MakerAppliances
P108Notebook SetStationery
P109Wireless HeadphonesElectronics
P110Standing DeskFurniture
customers
12 rows
idnameemailcitycountry
C1001Aarav Sharmaaarav@email.comMumbaiIndia
C1002Sara Chensara@email.comSingaporeSingapore
C1003James Wilsonjames@email.comLondonUK
C1004Maria Garciamaria@email.comMadridSpain
C1005Yuki Tanakayuki@email.comTokyoJapan
C1006Priya Patelpriya@email.comDelhiIndia
C1007Alex Johnsonalex@email.comNew YorkUSA
C1008Chen Weiwei@email.comShanghaiChina
C1009Emma Brownemma@email.comSydneyAustralia
C1010Omar Hassanomar@email.comDubaiUAE
C1011Lena Mullerlena@email.comBerlinGermany
C1012Ravi Kumarravi@email.comBangaloreIndia

Worked Example

Generate every unique pair of product categories.

SQL
1SELECT DISTINCT
2 a.category AS cat_1,
3 b.category AS cat_2
4FROM products a
5CROSS JOIN products b
6WHERE a.category < b.category
7ORDER BY
8 cat_1,
9 cat_2;
CROSS JOIN pairs every product with every other product. DISTINCT collapses duplicate category pairs. The WHERE a.category < b.category keeps only unique unordered pairs.
Output
6 rows
cat_1cat_2
AppliancesElectronics
AppliancesFurniture
AppliancesStationery
ElectronicsFurniture
ElectronicsStationery
FurnitureStationery

Key Concepts

1Output = rows_A × rows_B — grows multiplicatively
2No ON clause — every left row pairs with every right row
3Use only with small tables or for deliberate scaffolding
41000 × 1000 = 1,000,000 rows — be careful with table sizes

Pro Tip

Rarely used in basic queries, but essential for generating complete grids and scaffolds — especially reports that need a row for every combination even when data is missing.

When to Use

Report scaffolds (every month × every product), test data generation, date dimensions, all-pairs comparisons.

Challenge

Solve the problem below

How many total row combinations would a CROSS JOIN between customers and products produce? Use COUNT(*).

Your Query

Previous

SELF JOIN

Next

Subqueries