#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Science

CROSS JOIN in MySQL: Cartesian Products and Use Cases

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;
skucoloursize
TSH-BLA-SBlackS
TSH-BLA-MBlackM
TSH-BLA-LBlackL
TSH-MAR-SMaroonS
TSH-MAR-MMaroonM
TSH-MAR-LMaroonL
TSH-NAV-SNavyS
TSH-NAV-MNavyM
TSH-NAV-LNavyL
TSH-WHI-SWhiteS
TSH-WHI-MWhiteM
TSH-WHI-LWhiteL

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;
citysale_daterevenue
Jaipur2026-11-0118500.00
Jaipur2026-11-020.00
Jaipur2026-11-0322000.00
Surat2026-11-019400.00
Surat2026-11-0212100.00
Surat2026-11-030.00
Kochi2026-11-010.00
Kochi2026-11-020.00
Kochi2026-11-0315750.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;
citysale_dateamountpct_of_total
Jaipur2026-11-0322000.0028.3
Jaipur2026-11-0118500.0023.8
Kochi2026-11-0315750.0020.3
Surat2026-11-0212100.0015.6
Surat2026-11-019400.0012.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

When not to use CROSS JOIN

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.

Launch Your Data Science Career

Learn MySQL, Python, Machine Learning and AI with Uncodemy's Data Science Training in Noida — hands-on projects, industry mentors and Dedicated Placement Support.

Enroll in Data Science Course →