MySQL CROSS JOIN

Last updated: October 19, 2026Oct 19, 2026 3 min read

CROSS JOIN combines every row of one table with every row of another, producing what mathematicians call a Cartesian product. It has no join condition. That sounds odd, but it's exactly what you need to generate combinations: every size in every colour, every store for every month, every seat in every row.

1. How CROSS JOIN Works

SELECT columns FROM table1 CROSS JOIN table2;
-- same as
SELECT columns FROM table1, table2;

2. Generating Combinations

SELECT s.size, c.colour, CONCAT('TSHIRT-', s.size, '-', UPPER(LEFT(c.colour, 3))) AS sku
FROM sizes s
CROSS JOIN colours c
ORDER BY s.sort_order, c.colour;

Result: 6 rows

3. Row Count Grows Fast

SELECT (SELECT COUNT(*) FROM sizes) AS sizes, (SELECT COUNT(*) FROM colours) AS colours,
       (SELECT COUNT(*) FROM sizes CROSS JOIN colours) AS combinations;

Result: 1 row

4. Report Grids with No Gaps

Sales exist only for some store-month pairs. To show every store for every month (with 0 where nothing was sold), cross join stores with the months, then LEFT JOIN the facts:

SELECT st.store, m.sale_month, COALESCE(SUM(sa.amount), 0) AS revenue
FROM stores st
CROSS JOIN (SELECT '2026-01' AS sale_month UNION ALL SELECT '2026-02' UNION ALL SELECT '2026-03') m
LEFT JOIN sales sa ON sa.store = st.store AND sa.sale_month = m.sale_month
GROUP BY st.store, m.sale_month
ORDER BY st.store, m.sale_month;

Result: 9 rows

5. Generating Number and Date Series

Cross joining a small digits table with itself creates a quick series of numbers, which can become dates:

WITH digits AS (SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
                UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
SELECT DATE('2026-03-01') + INTERVAL (tens.d * 10 + ones.d) DAY AS day
FROM digits tens CROSS JOIN digits ones
WHERE tens.d * 10 + ones.d < 7
ORDER BY day;

Result: 7 rows

6. Accidental Cross Joins

Forgetting the join condition turns an intended inner join into a cross join and multiplies rows:

SELECT COUNT(*) AS rows_returned FROM stores, sales;   -- forgot WHERE stores.store = sales.store

Result: 1 row

7. CROSS JOIN vs INNER JOIN

In MySQL, CROSS JOIN with an ON clause behaves exactly like an INNER JOIN.

8. Summary

• CROSS JOIN returns every combination of rows from both tables.
• Result size = product of the table sizes, so use it on small tables or with care.
• Great for SKUs, report grids (with LEFT JOIN) and number/date series.
• A missing join condition creates an accidental cross join.