Generating All Combinations: CROSS JOIN

The Calendar Problem

The marketing team needed a report showing revenue for every day of the month, even days with zero sales. A LEFT JOIN on `orders` would skip days with no orders. I needed to generate those "Empty" days first.

The Quest: The Cartesian Product

A `CROSS JOIN` combines every row from Table A with every row from Table B, with no condition. If Table A has 10 rows and Table B has 5 rows, you get 50 rows.

This is often called a **Cartesian Product**.

The Implementation: The Syntax

-- Generate a row for every product in every store

SELECT s.store_name, p.product_name

FROM stores s

CROSS JOIN products p;

The Real Use Case: Date Scaffolding

I created a "Calendar" table with all dates, then cross-joined it with a list of products to create a grid of every date-product combination.

SELECT d.date, p.product_name, COALESCE(SUM(o.qty), 0) AS sales

FROM calendar d

CROSS JOIN products p

LEFT JOIN orders o ON d.date = o.order_date AND p.id = o.product_id

GROUP BY d.date, p.product_name

ORDER BY d.date;

The "Oops" Moment

I once accidentally ran a cross join on two tables with millions of rows each. The result set was trillions of rows. The query never finished.

**Pro Tip**: Only use CROSS JOIN on small, controlled datasets (calendars, dimension tables). Never on large fact tables.

The Victory

The marketing team got their report with zeroes on quiet days, a true representation of the sales trend. CROSS JOIN is a niche but essential tool for data scaffolding.

Your Task for Today

Create a small calendar table (or use a series generator) and CROSS JOIN it with a list of categories to build a reporting grid.

*Day 41: Non-Equality Joins.*