CROSS JOIN (MySQL Join)
CROSS JOIN is a type of join operation in MySQL that returns the Cartesian product of two tables. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of CROSS JOIN in MySQL.
Syntax
The syntax for CROSS JOIN in MySQL is as follows:
SELECT *
FROM table1
CROSS JOIN table2;
In this syntax, table1 and table2 are the names of the two tables we want to join using CROSS JOIN. The * in the SELECT statement represents all columns from both tables.
Example
Suppose we have two tables, students and courses, which look like this:
students table:
| id | name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
courses table:
| id | course |
|---|---|
| 1 | Math |
| 2 | Science |
| 3 | English |
We can use CROSS JOIN to get the Cartesian product of both tables:
SELECT *
FROM students
CROSS JOIN courses;
This will return the following result:
| id | name | id | course |
|---|---|---|---|
| 1 | Alice | 1 | Math |
| 1 | Alice | 2 | Science |
| 1 | Alice | 3 | English |
| 2 | Bob | 1 | Math |
| 2 | Bob | 2 | Science |
| 2 | Bob | 3 | English |
| 3 | Charlie | 1 | Math |
| 3 | Charlie | 2 | Science |
| 3 | Charlie | 3 | English |
Output
The output of the above example would be as follows:
| id | name | id | course |
|---|---|---|---|
| 1 | Alice | 1 | Math |
| 1 | Alice | 2 | Science |
| 1 | Alice | 3 | English |
| 2 | Bob | 1 | Math |
| 2 | Bob | 2 | Science |
| 2 | Bob | 3 | English |
| 3 | Charlie | 1 | Math |
| 3 | Charlie | 2 | Science |
| 3 | Charlie | 3 | English |
Explanation
In the example above, we used CROSS JOIN to get the Cartesian product of two tables, students and courses. The result is a table that combines every row in students with every row in courses. The result contains all possible combinations of rows from both tables.
Use
CROSS JOIN is useful when you want to combine all possible rows from two tables. It can be used to generate data that does not exist in either table. It is also useful for data analysis, where all possible combinations of data need to be considered.
Important Points
- CROSS JOIN returns the Cartesian product of two tables.
- The result set of a CROSS JOIN has a number of rows equal to the number of rows in the first table multiplied by the number of rows in the second table.
- It is recommended to use a WHERE clause to filter the result set of a CROSS JOIN, otherwise it may return a very large result set.
Summary
In this tutorial, we discussed the syntax, example, output, explanation, use, important points, and summary of CROSS JOIN in MySQL. CROSS JOIN is used to return the Cartesian product of two tables. The result set of a CROSS JOIN has a number of rows equal to the number of rows in the first table multiplied by the number of rows in the second table. CROSS JOIN can be useful for data analysis and to generate data that does not exist in either table.