CROSS JOIN with example - SQL Query
SQL CROSS JOIN returns the cartesian product of rows from joined table. The cartesian join is a join of all rows of table1 to all rows of table2.
For example: If table1 with three rows is joined with table2 with three rows a cartesian join will return 9 rows.
Consider the following table 'Employees'.
| E_ID | E_Name | Dept_ID |
|---|
| 1 | Alex | 01 |
| 2 | Albert | 02 |
| 3 | Andrew | 03 |
Consider the following table 'Department'.
| Dept_ID | Dept_Name |
|---|
| 01 | Production |
| 02 | Marketing |
| 03 | HR |
Q. Write a query to perform cross join on table 'Employee' and table 'Department' and display the columns such as E_ID, E_Name, Dept_Name.
Answer:
SELECT E_ID, E_Name, Dept_Name
FROM Employee CROSS JOIN Department
| E_ID | E_Name | Dept_Name |
|---|
| 1 | Alex | Production |
| 2 | Albert | Production |
| 3 | Andrew | Production |
|
| 1 | Alex | Marketing |
| 2 | Albert | Marketing |
| 3 | Andrew | Marketing |
| 1 | Alex | HR |
| 2 | Albert | HR |
| 3 | Andrew | HR |
