A cross join in SQL produces a Cartesian product of two tables, meaning that it combines every row from the first table with every row from the second table.
Here's the basic syntax for performing a cross join:
SELECT *
FROM table1
CROSS JOIN table2;
Alternatively, you can achieve the same result without explicitly using CROSS JOIN by simply using a comma to separate the tables in the FROM clause:
SELECT *
FROM table1, table2;
Example:
Suppose you have two tables, employees and departments:
employees:
| id | name |
|----|----------|
| 1 | Alice |
| 2 | Bob |
departments:
| id | department_name |
|----|-----------------|
| 1 | HR |
| 2 | IT |
Executing a cross join on these tables would look like this:
SELECT *
FROM employees
CROSS JOIN departments;
Or:
SELECT *
FROM employees, departments;
Result:
The result would produce a set of rows where each employee is paired with each department:
| id | name | id | department_name |
|----|-------|----|-----------------|
| 1 | Alice | 1 | HR |
| 1 | Alice | 2 | IT |
| 2 | Bob | 1 | HR |
| 2 | Bob | 2 | IT |
Notes:
1. Result Size: If table1 has m rows and table2 has n rows, the result of a cross join will contain m * n rows.
2. Performance: Cross joins can result in large datasets, so use them with caution, especially with large tables.
3. Use Cases: Cross joins can be useful for generating combinations or testing purposes, but they are not commonly used in practical scenarios due to their potential for producing a vast number of records.