Why would you CROSS JOIN a table to a one-row subquery such as (SELECT SUM(amount) AS total FROM sales)?

## The arithmetic that makes it safe A `CROSS JOIN` multiplies row counts. Crossing a 500-row table with a one-row source gives 500 × 1 = 500 rows — the original rows, each widened by the extra columns. Nothing is duplicated and nothing is lost. That degenerate case of the Cartesian product is the whole point of the idiom. ```sql SELECT s.region, s.amount, s.amount * 100.0 / t.total AS pct_of_total FROM sales s CROSS JOIN (SELECT SUM(amount) AS total FROM sales) AS t; ``` Every sales row now carries `t.total`, so a per-row share of the overall total is a plain arithmetic expression. ## Why no join predicate appears There is only one row on the right, so there is nothing to choose between. Any `ON` condition would be either always true (redundant) or sometimes false (which would drop rows). This is one of the rare places where the absence of a join predicate is correct by construction rather than a bug — and it is worth saying so out loud in review, because a reviewer trained to look for missing predicates should be able to see immediately why this one is fine. ## What it buys over the alternatives - **Against repeating the aggregate in the select list.** You could write `(SELECT SUM(amount) FROM sales)` inline in every expression that needs it. Crossing once names the value, computes it in one place, and lets several expressions reference it. - **Against parameters.** The same shape injects run-time constants: `CROSS JOIN (SELECT DATE '2026-01-01' AS from_dt, DATE '2026-01-31' AS to_dt) p` gives a single place to change a report's window, referenced as `p.from_dt` throughout. - **Against a correlated subquery.** A correlated scalar subquery recomputes conceptually per row and cannot be reused across expressions; the crossed derived table is evaluated as one independent query. Where the summary must be computed *per group* rather than globally — a running total, or each row's share of its own region — a window function such as `SUM(amount) OVER (PARTITION BY region)` is the natural tool instead; the cross-joined subquery is for values that are the same for every row. ## The failure modes The idiom's safety rests entirely on the "exactly one row" assumption: - **Zero rows returned.** The product is `N × 0 = 0` and the entire result disappears. An aggregate with no `GROUP BY` cannot do this — `SELECT SUM(amount) FROM sales` over an empty table still returns one row containing NULL — but a subquery with a `WHERE` clause or a `GROUP BY` easily can. If the right side might be empty and you must keep the outer rows, use `LEFT JOIN … ON TRUE` instead, which NULL-extends rather than annihilating. - **More than one row returned.** The outer rows are silently multiplied by that count. This is the dangerous one: a subquery that grows a `GROUP BY` clause during a later edit turns a correct report into a duplicated one with no error anywhere. If the subquery has a `GROUP BY` or a filter that might not be unique, the shape is no longer a scalar attachment and needs a real join predicate. ```sql -- annihilation risk: the filter may match nothing FROM sales s CROSS JOIN (SELECT SUM(amount) AS total FROM sales WHERE region = 'EU') t -- safe under emptiness: keeps every sales row, total may be NULL FROM sales s LEFT JOIN (SELECT SUM(amount) AS total FROM sales WHERE region = 'EU') t ON TRUE ``` ## Reading it in review When you meet a bare `CROSS JOIN` in a query, the first question is whether the right side is provably one row. An ungrouped aggregate over a table is; a filtered subquery or a grouped one is not, and needs a second look. Documenting the assumption with a comment — or asserting it with a uniqueness constraint on the underlying data — is what keeps the idiom from decaying into an accidental product later.