MySQL CROSS JOIN
MySQL CROSS JOIN
The CROSS JOIN in MySQL returns the Cartesian product of two tables.
This means it returns all possible combinations of rows from the first table with rows from the second table.
⚠️ Be careful: The number of rows in the result = rows in Table1 × rows in Table2.
Syntax
-
No
ONcondition is needed. -
Every row of table1 is paired with every row of table2.
🧠 Example Tables
Students Table (students)
| student_id | name |
|---|---|
| 1 | John |
| 2 | Emma |
| 3 | Raj |
Subjects Table (subjects)
| subject_id | subject_name |
|---|---|
| 1 | Math |
| 2 | Science |
| 3 | English |
SQL Query
Result (3 × 3 = 9 rows)
| name | subject_name |
|---|---|
| John | Math |
| John | Science |
| John | English |
| Emma | Math |
| Emma | Science |
| Emma | English |
| Raj | Math |
| Raj | Science |
| Raj | English |
✅ Notice: Every student is paired with every subject.
Key Points
-
Produces a Cartesian product.
-
No matching condition is required.
-
Useful for generating combinations, schedules, or testing.
-
Can be written explicitly as
CROSS JOINor simply with a comma,:
Difference from INNER JOIN
| Feature | INNER JOIN | CROSS JOIN |
|---|---|---|
| Condition | Requires matching column | No condition required |
| Result | Only matching rows | All possible combinations |
| Use case | Combine related tables | Generate combinations or Cartesian product |
