In SQL, a CROSS JOIN is used to combine all rows from two or more tables, resulting in a Cartesian product. This means that every row from the first table is matched with every row from the second table, creating a new table with a number of rows equal to the product of the number of rows in the two tables.
Syntax
The basic syntax for a CROSS JOIN is as follows:
SELECT *
FROM table1
CROSS JOIN table2;
Example
Suppose we have two tables, employees and departments, with the following data:
employees
+----+-----------+
| id | name |
+----+-----------+
| 1 | Alice |
| 2 | Bob |
+----+-----------+
departments
+----+------------+
| id | department |
+----+------------+
| 1 | Sales |
| 2 | Marketing |
+----+------------+
If we apply a CROSS JOIN to these two tables, the resulting table will contain:
+----+--------+----+------------+
| id | name | id | department |
+----+--------+----+------------+
| 1 | Alice | 1 | Sales |
| 1 | Alice | 2 | Marketing |
| 2 | Bob | 1 | Sales |
| 2 | Bob | 2 | Marketing |
+----+--------+----+------------+
Use Cases
- When a combination of all rows from two or more tables is needed to perform analysis or data processing.
- In scenarios where a Cartesian product is required, such as for generating all possible combinations.
Conclusion
A CROSS JOIN in SQL is a powerful tool for combining data from multiple tables, and it produces a result set containing the Cartesian product of the input tables. However, it can generate a large number of rows, so it should be used judiciously.
References: