MySQL — Cross Join

Basic Cross Join

A cross join returns the Cartesian product — every row from the first table paired with every row from the second.

SELECT * FROM colors CROSS JOIN sizes;

If colors has 3 rows and sizes has 4 rows, the result has 12 rows.

Cross Join with ON

You can add a condition, but this turns it into an inner join semantically:

SELECT c.color_name, s.size_name
FROM colors c
CROSS JOIN sizes s;

Implicit Cross Join

Writing comma-separated tables without a WHERE clause produces the same result:

SELECT c.color_name, s.size_name
FROM colors c, sizes s;

Cross Join with ON (Filtered)

While rarely useful, you can filter a cross join:

SELECT p.name AS product, c.color_name
FROM products p
CROSS JOIN colors c
WHERE p.category = 'shoes';

Practical Use: Generating Combinations

Cross joins are useful for generating test data or combinatorial reports:

-- All day × shift combinations for scheduling
SELECT d.day_name, s.shift_name
FROM days d
CROSS JOIN shifts s;

-- Every product in every warehouse
SELECT p.product_name, w.warehouse_name
FROM products p
CROSS JOIN warehouses w;

Cross Join Performance

Because cross joins produce M × N rows, they can be extremely large:

Table A	Table B	Result Rows
10	100	1,000
100	100	10,000
1,000	1,000	1,000,000

Always filter or limit cross joins in production.

Aliases with Cross Join

SELECT 
    c.color_name AS Color,
    s.size_name AS Size,
    p.base_price
FROM products p
CROSS JOIN colors c
CROSS JOIN sizes s;

Mini Practice

Write SQL code that:

1. Creates two small tables (e.g. days and shifts)
2. Uses CROSS JOIN to generate all combinations
3. Filters the cross join result with a WHERE clause
4. Compares implicit and explicit cross join syntax

Up Next

Continue with Union — combining result sets from multiple SELECT statements.