What is the CROSS JOIN clause (cross join)?
The CROSS JOIN clause, also known as a cross join, is a type of join method used in SQL (Structured Query Language) to combine all the rows from two or more tables, resulting in a Cartesian product of the two tables. It creates all possible combinations of rows between the tables involved.
When performing a CROSS JOIN, each row from the first table is joined with every row from the second table, irrespective of any matching criteria or conditions. This differs from other join types like INNER JOIN or LEFT JOIN, which typically require a join condition based on a common column between the tables.
The syntax for a CROSS JOIN is as follows:
“`
SELECT * FROM table1
CROSS JOIN table2;
“`
It is important to note that the resulting number of rows in a cross join can be quite large, as it multiplies the number of rows from each table involved. Therefore, it is crucial to use cross joins cautiously and only when necessary, as it can lead to a significant increase in the result set size and possibly impact query performance.
Example:
Let’s consider two tables: `Employees` and `Departments`.
“`
Employees
+—-+——-+
| ID | Name |
+—-+——-+
| 1 | John |
| 2 | Sarah |
+—-+——-+
Departments
+—-+———–+
| ID | Department|
+—-+———–+
| 1 | Marketing |
| 2 | Finance |
+—-+———–+
“`
If we perform a CROSS JOIN between these two tables, the result would be the combination of each employee with each department, resulting in four rows:
“`
CROSS JOIN result
+—-+——-+———–+
| ID | Name | Department|
+—-+——-+———–+
| 1 | John | Marketing |
| 1 | John | Finance |
| 2 | Sarah | Marketing |
| 2 | Sarah | Finance |
+—-+——-+———–+
“`
As you can see, the cross join created all possible combinations between the rows of the two tables.
In summary, the CROSS JOIN clause allows you to create all combinations between rows of two or more tables, resulting in a Cartesian product. However, it’s essential to be mindful of the potential impact on performance and the resulting number of rows before using it.
Reference Articles
Read also
[Google Chrome] The definitive solution for right-click translations that no longer come up.