CROSS JOIN

Introduction

CROSS JOIN returns the Cartesian product of two tables — every combination of A × B. Powerful for generating series and grids; dangerous if used by accident.

Understanding the topic

Core concepts to understand:

• FROM a CROSS JOIN b — no ON clause.
• Result count = rows(A) × rows(B).
• Use case: generate calendar × users matrix for cohort reports.
• Accidental cross join (missing ON) is a classic perf bug.
• Combine with generate_series in Postgres for date grids.

Syntax reference

Visual workflow / architecture:

dates                      users
2024-01  ─┐               ┌── alice
2024-02  ─┤  CROSS JOIN  ─┤── bob
2024-03  ─┘               └── carol

Result: 9 rows  (3 dates × 3 users)
Useful for "every user per month" cohort grids.

SELECT <cols>
FROM   <a>
CROSS JOIN <b>;
-- no ON clause — every A × every B

SELECT d.day, u.name
FROM   generate_series('2026-01-01'::date, '2026-01-03', '1 day') AS d(day)
CROSS JOIN users u;

Real-world use

Reports needing zero-filled rows ('show 0 sales for months with none') use CROSS JOIN with a generated date series.

Best practices

• Use it intentionally — never let a missing ON clause sneak in.
• Pair with generate_series for time grids.
• Watch out for explosion on big tables.

Common mistakes

• Forgotten ON → millions of rows, slow query, memory blow up.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What is a Cartesian product?
• Q2. Use case for CROSS JOIN.
• Q3. How to detect an accidental cross join.
• Q4. CROSS JOIN with generate_series — example.
• Q5. Difference vs INNER JOIN with ON true.