How do you use CROSS JOIN to report zero-sale days for every product in a date range?

## The problem: rows that were never written Reports need to show absence. "Revenue by day and product for January" should list a zero for a product that sold nothing on the 14th, and a dashboard chart needs a point on every day or the line lies about the shape of the trend. But aggregation can only summarise rows that exist: ```sql SELECT sold_on, product_id, SUM(amount) AS revenue FROM sales WHERE sold_on BETWEEN DATE '2026-01-01' AND DATE '2026-01-31' GROUP BY sold_on, product_id; ``` If nothing sold on the 14th, `sales` has no row with that date, so `GROUP BY` forms no group, and the output simply skips the day. No filter, `COALESCE` or `HAVING` can recover it — the information is not missing from the aggregate, it is missing from the input. ## The spine pattern The answer is to generate the rows that ought to exist and attach the data to them. That generation is exactly a Cartesian product, and this is the canonical deliberate use of `CROSS JOIN`: ```sql SELECT d.day, p.product_id, COALESCE(SUM(s.amount), 0) AS revenue FROM calendar d CROSS JOIN products p LEFT JOIN sales s ON s.sold_on = d.day AND s.product_id = p.product_id WHERE d.day BETWEEN DATE '2026-01-01' AND DATE '2026-01-31' GROUP BY d.day, p.product_id ORDER BY d.day, p.product_id; ``` Three steps, each doing one job: 1. **`calendar CROSS JOIN products`** builds the spine — every `(day, product)` pair that should appear, whether or not it has data. For 31 days and 12 products that is 372 rows. 2. **`LEFT JOIN sales`** attaches the facts. The spine is the preserved side, so a pair with no sale survives with NULL sales columns. 3. **`COALESCE(SUM(s.amount), 0)`** converts the empty aggregate into a reported zero. `SUM` over a group whose only rows are NULL-extended returns NULL, not 0, so the `COALESCE` is not optional if the report must print 0. ## The predicate-placement trap Both join keys belong in the `ON` clause. Writing `WHERE s.product_id = p.product_id` instead filters the joined result after NULL-extension, and every unmatched spine row is discarded — silently turning the outer join back into an inner one and undoing the whole pattern. Filters on the *spine* (`WHERE d.day BETWEEN …`) are safe in `WHERE`, because those columns are never NULL-extended; filters on the *sales* side are not. ## Where the date list comes from A persisted `calendar` (or `dim_date`) table is the portable option and is standard practice in warehouses, because it also carries useful attributes like `is_weekend`, fiscal period and quarter. Engines differ in what they offer instead: PostgreSQL has `generate_series`, and other engines have their own row-generating idioms or none at all — check your engine's documentation rather than assuming a set-returning function exists. Where a fixed small range is enough, a `VALUES` table constructor in the `FROM` clause works too. The same shape generalises past dates: cross a region list with a category list to get a complete matrix; cross an hour list with a server list for a monitoring heat map; cross plans with currencies to build test fixtures covering every combination. ## Sizing the grid Because this is a deliberate product, its size is the multiplication you signed up for. Two years of days (730) crossed with 50,000 SKUs is 36.5 million spine rows before a single sale is joined. Keep the spine narrow: filter the date range and the dimension *before* crossing them — for example, cross the dates only with products that were active in the period, using a derived table — rather than generating the universe and discarding it afterwards. ## Why not solve it in the application You can fetch the sparse aggregate and fill the gaps in application code, and for a single chart that is often simpler. The SQL spine wins when the zero-filled result feeds further SQL — a running total, a rank, another join — or when several consumers must agree on what "zero" means. Doing it once in the query keeps the definition in one place.