MySQL CROSS JOIN

A CROSS JOIN produces the Cartesian product of two tables — every row from the first table is combined with every row from the second table. No ON condition is required or used. If the first table has 4 rows and the second has 3 rows, the result has 4 × 3 = 12 rows.

Unlike other joins, CROSS JOIN does not look for matching values — it blindly combines everything with everything.

Syntax

SELECT columns
FROM table_a
CROSS JOIN table_b;

No ON clause is needed — or allowed — because there is no matching condition.

Example

Imagine two small tables — one for colours and one for sizes:

SELECT colour, size
FROM colours
CROSS JOIN sizes;

3 colours × 3 sizes = 9 rows. Every possible combination is generated.

When to Use CROSS JOIN

CROSS JOIN is useful in specific scenarios where you genuinely need all combinations:

• Product variants — generate all size/colour/style combinations for a product catalogue.
• Scheduling — pair every employee with every shift slot to build a scheduling matrix.
• Test data generation — create a full grid of test scenarios by combining two sets of parameters.
• Reports — pair every category with every month to ensure all cells exist in a pivot-style report, even if no data exists yet.

CROSS JOIN with WHERE

You can add a WHERE clause to filter the Cartesian product down to only the combinations you need:

-- Only Blue combinations
SELECT colour, size
FROM colours
CROSS JOIN sizes
WHERE colour = 'Blue';

Key Points to Remember

• CROSS JOIN produces the Cartesian product — every row from table A paired with every row from table B.
• No ON clause is used — there is no matching condition.
• Result row count = rows in table A × rows in table B.
• Use it for generating combinations: product variants, schedules, test matrices.
• Avoid on large tables — the result set grows exponentially with table size.