SQL CROSS JOIN
intermediatejoins
2 min read
What is CROSS JOIN?
A CROSS JOIN produces the Cartesian product of two tables — every combination of rows. If table A has 10 rows and table B has 5 rows, the result has 50 rows.
Syntax
SELECT columns
FROM table1
CROSS JOIN table2;Equivalent to: SELECT columns FROM table1, table2;
When to Use
- Generating all possible pairs or combinations
- Creating a calendar grid (dates CROSS JOIN time slots)
- Generating test data
Key Points
- No ON Clause — CROSS JOIN does not take a join condition.
- Result Size — The result is rows_in_A * rows_in_B. This can get very large very quickly.
- Accidental Cross Joins — Forgetting the ON clause in an INNER JOIN effectively creates a cross join.
- With WHERE — Adding a
WHEREcondition to a CROSS JOIN makes it behave like an INNER JOIN. - Performance — Use with caution on large tables.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL CROSS JOIN Challenge
Write a query that solve this task: generate every possible student-course pairing.
Expected result
Every student paired with every course. If 10 students and 5 courses, result has 50 rows.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is CROSS JOIN?
Initializing database...Each run starts from fresh sample data.
More Examples
Cross join with filter
Use WHERE to filter the Cartesian product.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is a Cartesian product?
A Cartesian product is every possible combination of rows from two tables. For tables with m and n rows, the result has m * n rows.
Is CROSS JOIN the same as listing tables with commas?
Yes. SELECT * FROM a, b is equivalent to SELECT * FROM a CROSS JOIN b. The explicit syntax is preferred for clarity.