A CROSS JOIN is a type of SQL join that produces the Cartesian product of two tables. This means that it combines each row from the first table with every row from the second table. The result is a new table that contains all possible combinations of rows from the two tables.
Syntax
The basic syntax for a CROSS JOIN is:
SELECT *
FROM table1
CROSS JOIN table2;
You can also write it using a comma as a separator, though this is less common with modern SQL:
SELECT *
FROM table1, table2;
Example
Consider the following two tables:
Table: Employees
| EmployeeID | EmployeeName |
|------------|---------------|
| 1 | Alice |
| 2 | Bob |
Table: Departments
| DepartmentID | DepartmentName |
|--------------|------------------|
| 1 | HR |
| 2 | IT |
| 3 | Sales |
To find the Cartesian product of Employees and Departments, you would run the following SQL query:
SELECT *
FROM Employees
CROSS JOIN Departments;
Result Set
The resulting table would look like this:
| EmployeeID | EmployeeName | DepartmentID | DepartmentName |
|------------|--------------|--------------|-----------------|
| 1 | Alice | 1 | HR |
| 1 | Alice | 2 | IT |
| 1 | Alice | 3 | Sales |
| 2 | Bob | 1 | HR |
| 2 | Bob | 2 | IT |
| 2 | Bob | 3 | Sales |
Considerations
- Size: Be cautious when using CROSS JOINs with large tables, as the result set can grow rapidly in size (number of rows returned = number of rows in table1 * number of rows in table2).
- Usage: While CROSS JOINs can be useful in specific cases, they are often avoided in practice unless you have a clear reason to generate every possible combination.
- Filtering: If you need to impose conditions on the results, you can do so by adding a WHERE clause after your CROSS JOIN.
Example with Filtering
To combine the tables but only for a specific DepartmentID, you could enter:
SELECT *
FROM Employees
CROSS JOIN Departments
WHERE DepartmentID = 1; -- Only HR department
This would yield the results for all employees for the HR department, keeping the Cartesian product idea but narrowing the focus with further conditions.