When to CROSS JOIN
- SQL course
- Intermediate
- Lesson 15/115
- 4 min
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 use cases — details
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.
Practice CROSS JOIN use cases
Join colors and sizes using CROSS JOIN.
Starter code
-- color size pairs
SELECT c.color, s.size
FROM colors cSample solution
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;Expected output
color size Kırmızı S Kırmızı M Mavi S
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 When to CROSS JOIN: match the terms
| Term | Meaning |
|---|---|
CROSS JOIN |
combinations |
color |
size |
cartesian |
product |
ON |
none |
2 Add CROSS JOIN
Question
SELECT c.color, s.size
FROM colors c
____ sizes s;
Answer
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;Explanation: CROSS JOIN is the right answer.
3 What does CROSS JOIN produce?
- The matching rows
- Every possible pair Correct answer
- Only the left
- Only the right
Explanation: CROSS JOIN produces all row pairs.