CROSS JOIN
CROSS JOIN returns every possible combination of rows from two tables.
Unlike INNER JOIN, LEFT JOIN, or RIGHT JOIN, CROSS JOIN does not require an ON condition. It creates a Cartesian Product of the two tables.
What is CROSS JOIN?
A CROSS JOIN combines every row from the first table with every row from the second table.
For example, if the first table contains 3 rows and the second table contains 4 rows, the result will contain:
This type of result is called a Cartesian Product.
CROSS JOIN Syntax
SELECT columns
FROM Table1
CROSS JOIN Table2;
Example Tables
Students Table
| StudentId | StudentName |
|---|---|
| 101 | Rahul |
| 102 | Priya |
| 103 | Amit |
Courses Table
| CourseId | CourseName |
|---|---|
| 1 | .NET Full Stack |
| 2 | Java Full Stack |
| 3 | Python Full Stack |
Basic CROSS JOIN Example
SELECT
Students.StudentName,
Courses.CourseName
FROM Students
CROSS JOIN Courses;
Result
Students contains 3 records and Courses contains 3 records.
Therefore:
| StudentName | CourseName |
|---|---|
| Rahul | .NET Full Stack |
| Rahul | Java Full Stack |
| Rahul | Python Full Stack |
| Priya | .NET Full Stack |
| Priya | Java Full Stack |
| Priya | Python Full Stack |
| Amit | .NET Full Stack |
| Amit | Java Full Stack |
| Amit | Python Full Stack |
Cartesian Product
The result produced by a CROSS JOIN is known as a Cartesian Product.
Every row of the first table is combined with every row of the second table.
Number of Result Rows
=
Rows in Table 1
×
Rows in Table 2
CROSS JOIN with Multiple Tables
CROSS JOIN can also be used with more than two tables.
SELECT
S.StudentName,
C.CourseName,
T.TrainerName
FROM Students AS S
CROSS JOIN Courses AS C
CROSS JOIN Trainers AS T;
This produces combinations between all rows of the participating tables.
CROSS JOIN with WHERE
A WHERE condition can be used after a CROSS JOIN when specific combinations need to be selected.
SELECT
S.StudentName,
C.CourseName
FROM Students AS S
CROSS JOIN Courses AS C
WHERE C.CourseName = '.NET Full Stack';
This filters the combinations and returns only those involving the .NET Full Stack course.
Practical Uses of CROSS JOIN
CROSS JOIN is useful when every possible combination is required.
- Creating combinations of products and sizes.
- Generating combinations of courses and batches.
- Creating possible schedules.
- Generating test data combinations.
- Creating all possible combinations between categories.
CROSS JOIN vs INNER JOIN
| CROSS JOIN | INNER JOIN |
|---|---|
| Returns every possible combination. | Returns only matching records. |
| Does not require an ON condition. | Usually uses an ON condition. |
| Produces a Cartesian Product. | Produces related matching records. |
Important Points
- CROSS JOIN returns every possible combination between two tables.
- CROSS JOIN does not require an ON condition.
- The result is called a Cartesian Product.
- Number of rows = rows in first table × rows in second table.
- CROSS JOIN can produce a very large result when tables contain many rows.
- CROSS JOIN should therefore be used carefully.
Summary
CROSS JOIN returns every possible combination of rows from two tables. It creates a Cartesian Product and does not require an ON condition. It is useful when all possible combinations between two sets of data are required.