When to CROSS JOIN

Learn suitable use cases for CROSS JOIN.

CROSS JOIN use cases

CROSS JOIN is ideal for generating combinations from dimension tables. Color and size pairs are an example.

SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

This query produces every color-size pair.

c.color, s.size is selected from colors c. sizes s is joined, producing every possible pair.

CROSS JOIN helps with reporting and test data generation. Production queries rarely need it.

For example, CROSS JOIN of a sizes table and a colors table generates every size-color combination of a product in one query.

Join colors and sizes using CROSS JOIN.

Show example solution

SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

Add CROSS JOIN

SELECT c.color, s.size
FROM colors c
____ sizes s;

Show answer

SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;

Next lesson: Comma JOIN