A clothing seller in Tiruppur makes one T-shirt in 3 sizes and 4 colours and needs a row for every variant in the product catalogue. That is 3 × 4 = 12 rows, and producing "every row of A paired with every row of B" is exactly what CROSS JOIN does. The result is called a Cartesian product: if table A has m rows and table B has n rows, the result has m × n rows. There is no join condition, because nothing needs to match.
Example 1: generating every combination
CREATE TABLE sizes (size VARCHAR(4), sort_order INT);
CREATE TABLE colours (colour VARCHAR(10));
INSERT INTO sizes VALUES ('S',1), ('M',2), ('L',3);
INSERT INTO colours VALUES ('Black'), ('White'), ('Navy'), ('Maroon');
SELECT CONCAT('TSH-', UPPER(LEFT(c.colour, 3)), '-', s.size) AS sku,
c.colour, s.size
FROM colours c
CROSS JOIN sizes s
ORDER BY c.colour, s.sort_order;
| sku | colour | size |
|---|---|---|
| TSH-BLA-S | Black | S |
| TSH-BLA-M | Black | M |
| TSH-BLA-L | Black | L |
| TSH-MAR-S | Maroon | S |
| TSH-MAR-M | Maroon | M |
| TSH-MAR-L | Maroon | L |
| TSH-NAV-S | Navy | S |
| TSH-NAV-M | Navy | M |
| TSH-NAV-L | Navy | L |
| TSH-WHI-S | White | S |
| TSH-WHI-M | White | M |
| TSH-WHI-L | White | L |
Twelve rows, as expected. Add a fifth colour and you get fifteen without changing the query. The same pattern builds exam timetables (every batch × every subject), test matrices (every browser × every OS) or price grids.
Example 2: filling the gaps in a daily report
This is the use case analysts meet most. A report of daily sales per store should show a row for every store on every day, including days with no sales. The sales table only has rows for days that had sales:
CREATE TABLE stores (store_id INT PRIMARY KEY, city VARCHAR(20));
INSERT INTO stores VALUES (1,'Jaipur'), (2,'Surat'), (3,'Kochi');
CREATE TABLE store_sales (store_id INT, sale_date DATE, amount DECIMAL(10,2));
INSERT INTO store_sales VALUES
(1,'2026-11-01',18500), (1,'2026-11-03',22000),
(2,'2026-11-01', 9400), (2,'2026-11-02',12100),
(3,'2026-11-03',15750);
A plain GROUP BY on store_sales returns five rows, and a chart built on it would silently skip the zero days. The fix is to build the full grid first (stores × dates) with a cross join, then LEFT JOIN the actual sales onto it:
WITH RECURSIVE days AS (
SELECT DATE('2026-11-01') AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2026-11-03'
)
SELECT s.city, days.d AS sale_date, COALESCE(SUM(ss.amount), 0) AS revenue
FROM stores s
CROSS JOIN days
LEFT JOIN store_sales ss
ON ss.store_id = s.store_id AND ss.sale_date = days.d
GROUP BY s.city, s.store_id, days.d
ORDER BY s.store_id, days.d;
| city | sale_date | revenue |
|---|---|---|
| Jaipur | 2026-11-01 | 18500.00 |
| Jaipur | 2026-11-02 | 0.00 |
| Jaipur | 2026-11-03 | 22000.00 |
| Surat | 2026-11-01 | 9400.00 |
| Surat | 2026-11-02 | 12100.00 |
| Surat | 2026-11-03 | 0.00 |
| Kochi | 2026-11-01 | 0.00 |
| Kochi | 2026-11-02 | 0.00 |
| Kochi | 2026-11-03 | 15750.00 |
Nine rows: three stores × three days, with honest zeros. The date list comes from a recursive CTE; in production many teams keep a permanent calendar table instead.
Example 3: attaching one summary row to every row
Cross joining with a subquery that returns exactly one row adds its values to every row, which is handy for percentages of a total:
SELECT st.city, ss.sale_date, ss.amount,
ROUND(100 * ss.amount / t.total, 1) AS pct_of_total
FROM store_sales ss
JOIN stores st ON st.store_id = ss.store_id
CROSS JOIN (SELECT SUM(amount) AS total FROM store_sales) AS t
ORDER BY pct_of_total DESC;
| city | sale_date | amount | pct_of_total |
|---|---|---|---|
| Jaipur | 2026-11-03 | 22000.00 | 28.3 |
| Jaipur | 2026-11-01 | 18500.00 | 23.8 |
| Kochi | 2026-11-03 | 15750.00 | 20.3 |
| Surat | 2026-11-02 | 12100.00 | 15.6 |
| Surat | 2026-11-01 | 9400.00 | 12.1 |
Because t has one row, the cross join does not multiply anything. In MySQL 8 the window function SUM(amount) OVER () gives the same total without the join; see the window functions tutorial.
The accidental Cartesian product
Most people meet cross joins by mistake. The old comma syntax is a cross join until a WHERE condition links the tables:
SELECT COUNT(*) FROM stores, store_sales; -- forgot the WHERE
| COUNT(*) |
|---|
| 15 |
Fifteen rows (3 × 5) instead of five. On real tables, 50,000 customers × 2,00,000 orders is 10 billion rows, enough to hang a server. If a join returns far more rows than either table has, check for a missing or wrong join condition before anything else. Writing joins with explicit JOIN ... ON makes the omission much harder to miss.
MySQL-specific behaviour worth knowing
- In MySQL,
CROSS JOIN,JOINandINNER JOINare syntactic equivalents.stores s CROSS JOIN store_sales ss ON ss.store_id = s.store_idruns and returns 5 rows, like an inner join. Standard SQL does not allowONwithCROSS JOIN, so do not rely on this if the query must run on PostgreSQL or SQL Server. - A plain
JOINwith noONclause is also a cross join in MySQL. - Comma joins have lower precedence than
JOIN. Mixing them (FROM a, b JOIN c ON c.x = a.x) gives error 1054 "Unknown column 'a.x' in 'on clause'". Stick to one style.
When not to use CROSS JOIN
- Both tables are large and you then filter most rows away in
WHERE. That is an inner join written the slow way. - You only need to know whether a match exists. Use
EXISTSor a subquery.
For how cross joins fit with the other join types, read MySQL JOINs explained; the pair-finding pattern on a single table is covered in self JOIN in MySQL.