CROSS JOIN
CROSS JOIN pairs every row of one table with every row of another, which is how you build calendars, grids and gap-free reports.
SELECT ... FROM a CROSS JOIN bA CROSS JOIN emits every combination of rows from two tables: three sizes crossed with four colours gives twelve rows. There is no ON clause because nothing is being matched. Accidentally it is the classic runaway query; deliberately it is how you manufacture the rows that your data is missing.
Why you would want one
Reports usually need a row for every slot, including the empty ones. Real tables only contain what happened. So you generate the complete grid — every day crossed with every product, every region crossed with every month — and LEFT JOIN the facts onto it. Days with no sales then appear with a zero instead of vanishing. A recursive CTE (MySQL 8.0 and later) is the usual way to produce the date side of that grid without a permanent calendar table.
Shorthand and longhand
FROM a CROSS JOIN b and the comma form FROM a, b with no join condition produce the same Cartesian product. Be careful mixing the two styles: JOIN binds tighter than the comma, so FROM t1, t2 JOIN t3 ON t1.i = t3.i fails with "Unknown column 't1.i' in 'on clause'" because the ON clause can only see t2 and t3.
What trips people up
Output size is the product of the inputs, so filter each side before crossing, and remember that in MySQL CROSS JOIN accepts an ON clause and behaves as an inner join when given one.
Example
-- A dense daily sales report: one row per (day, product), including
-- days when a product sold nothing at all.
WITH RECURSIVE calendar AS (
SELECT CAST('2026-09-01' AS DATE) AS day
UNION ALL
SELECT day + INTERVAL 1 DAY
FROM calendar
WHERE day < '2026-09-30' -- always bound the recursion
)
SELECT cal.day,
p.sku,
COALESCE(SUM(s.units), 0) AS units -- SUM of nothing is NULL
FROM calendar AS cal
CROSS JOIN products AS p -- the complete grid
LEFT JOIN daily_sales AS s -- the sparse facts
ON s.product_id = p.id
AND s.sold_on = cal.day
WHERE p.discontinued = 0 -- shrink the grid early
GROUP BY cal.day, p.sku
ORDER BY cal.day, p.sku;When to use it
- Build a calendar or grid so a report has a row for every day, even the ones with no data.
- List every size/colour or region/product combination that has not been created yet.
- Generate a numbers sequence for splitting strings or expanding ranges.
- Attach one grand total to every row of a result set without a correlated subquery.
More examples
Which combinations are missing
Gap-finding: generate everything that should exist, subtract what does. The same shape answers 'which employees have not completed which mandatory courses' and 'which regions are missing a price for which currency' — cross the two dimension tables, outer join the fact table, keep the NULLs.
-- Every size/colour pair that has no variant row yet.
-- CROSS JOIN builds the intended grid; the LEFT JOIN and IS NULL
-- subtract the pairs that already exist.
SELECT sz.code AS size_code,
co.code AS colour_code
FROM sizes AS sz
CROSS JOIN colours AS co
LEFT JOIN variants AS v
ON v.size_code = sz.code
AND v.colour_code = co.code
WHERE v.sku IS NULL -- v.sku is NOT NULL, so this means 'no match'
ORDER BY sz.code, co.code;Generating a number sequence without a table
The pre-CTE way to manufacture a sequence, still worth knowing because a small physical digits table is cheap, indexed, and avoids the cte_max_recursion_depth limit (1000 by default) that a recursive CTE runs into. Shown in both spellings so you can recognise either.
-- A ten-row digits table crossed with itself gives 0-99;
-- cross it three times for 0-999.
CREATE TABLE digits (n TINYINT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO digits (n) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
SELECT tens.n * 10 + ones.n AS n
FROM digits AS tens
CROSS JOIN digits AS ones
ORDER BY n;
-- The comma form is the same query. JOIN binds tighter than the comma,
-- so do not mix the two styles in one FROM clause.
SELECT tens.n * 10 + ones.n AS n
FROM digits AS tens, digits AS ones
ORDER BY n;Crossing to a single-row subquery
A one-row CROSS JOIN is the readable way to reuse an aggregate across a whole result set, and it computes the aggregate once rather than once per row as a correlated scalar subquery would. Window functions can also do this, but the cross join stays clearer when the total comes from a differently filtered set.
-- Each order's share of total revenue. The CROSS JOIN is to a
-- derived table of exactly one row, so it multiplies nothing --
-- it just makes one scalar available to every row.
SELECT o.id,
o.total_cents,
ROUND(100 * o.total_cents / t.grand_total, 2) AS pct_of_revenue
FROM orders AS o
CROSS JOIN (
SELECT SUM(total_cents) AS grand_total
FROM orders
WHERE status = 'paid'
) AS t
WHERE o.status = 'paid';
Discussion