CROSS JOIN
- SQL course
- Intermediate
- Lesson 14/115
- 4 min
Take the cartesian product of two tables.
CROSS JOIN
CROSS JOIN produces every combination of rows from two tables. The row count is the product.
SELECT c.name, p.name
FROM customers c
CROSS JOIN products p;This query gives every customer-product pair.
c.name, p.name is selected from customers c. products p is joined, producing every possible pair.
CROSS JOIN — details
CROSS JOIN has no ON condition. On large tables the result explodes; use with care.
For example, CROSS JOIN of two 100-row tables produces 10,000 rows, which is why an accidental cross join on large tables is expensive.
Practice CROSS JOIN
Join customers with products using CROSS JOIN.
Starter code
-- cross join
SELECT c.name, p.name
FROM customers cSample solution
SELECT c.name, p.name
FROM customers c
CROSS JOIN products p;Expected output
name name Ayşe Kalem Ayşe Defter Mehmet Kalem
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add CROSS JOIN
Question
SELECT c.name, p.name
FROM customers c
____ products p;
Answer
SELECT c.name, p.name
FROM customers c
CROSS JOIN products p;Explanation: CROSS JOIN is the right answer.
2 Does CROSS JOIN have an ON clause?
- No, it does not Correct answer
- Yes, it does
- It is required
- It is optional
Explanation: CROSS JOIN has no ON condition.
3 CROSS JOIN: match the terms
| Term | Meaning |
|---|---|
CROSS JOIN |
cartesian |
ON |
none |
product |
row count |
cartesian |
all pairs |