CROSS JOIN

The IPL scheduling team wants every possible opener × ground pairing to plan practice sessions. No key links an opener to a ground — they simply want all combinations. That's a CROSS JOIN.

Definition

CROSS JOIN returns the Cartesian product of two tables: every row of A combined with every row of B. It has no join condition, so the result has exactly rows(A) × rows(B) rows.

• Cartesian product — the maths term for "all possible pairs".
• No ON clause — nothing is matched; every pair is produced.
• Row count — 2 openers × 3 grounds = 6 rows; players (6) × teams (7) = 42 rows.

Syntax

SELECT a.column_name, b.column_name
FROM table_a AS a
CROSS JOIN table_b AS b;     -- no ON condition

Example — every opener at every ground

The setup creates two small tables: openers (Virat Kohli, Rohit Sharma) and grounds (Wankhede, Chepauk, Eden Gardens).

SELECT o.name, g.ground
FROM openers AS o
CROSS JOIN grounds AS g;

How it works: each of the 2 openers is paired with each of the 3 grounds → 2 × 3 = 6 rows. The order of tables does not matter for which rows you get (only the default display order changes).

Example — how fast it grows

SELECT COUNT(*) AS total_rows
FROM players
CROSS JOIN teams;

6 × 7 = 42. Two tables with 10,000 rows each would give 100 million rows — so use CROSS JOIN deliberately.

Example — generating fixtures

SELECT h.team AS home, a.team AS away
FROM teams AS h
CROSS JOIN teams AS a
WHERE h.team IN ('MI', 'CSK', 'RCB')
  AND a.team IN ('MI', 'CSK', 'RCB')
  AND h.team <> a.team;

Joining a table to itself with a CROSS JOIN, then removing "a team plays itself", gives every home-and-away fixture.

When is CROSS JOIN useful?

Common mistakes

• ❌ Adding ON to a CROSS JOIN → syntax error. ✅ CROSS JOIN never has ON.
• ❌ Accidental CROSS JOIN: FROM players, teams with the WHERE condition forgotten gives 42 rows instead of 6. ✅ Always use explicit INNER JOIN ... ON.
• ❌ Running CROSS JOIN on two big tables. ✅ Check COUNT(*) of each table first.

SQL Server vs MySQL / PostgreSQL

CROSS JOIN works the same in all of them. (SQL Server also has CROSS APPLY — a different, more advanced operator used with table-valued functions; don't confuse the two.)

Interview tip

Q: Table A has 4 rows and table B has 0 rows. How many rows does A CROSS JOIN B return? A: 0 — 4 × 0 = 0. There is nothing to pair with.