Summary -

In this topic, we described about the Cross Join with detailed example.

CROSS JOIN is used to combine all rows present in one table with all rows present in another table. CROSS JOIN is also called as CARTESIGN JOIN.

Syntax1 -

SELECT column1,column2 FROM table1 CROSS JOIN table2;

Syntax2 -

SELECT column1,column2 FROM  table1, table2;

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

Scenario – Combining rows from two tables by using CROSS JOIN.

Requirement - Combining rows from employee_details and department_details tables by using CROSS JOIN with specific columns. The query was as follows –

SELECT emp_id, emp_name, department_details.dept_name 
FROM employee_details CROSS JOIN department_details;

It can also written as follows the output same in both queries-

SELECT emp_id, emp_name, department_details.dept_name 
FROM employee_details, department_details;

By executing above query we can combine all rows present in employee_details with all rows present in department_details table. The output was as follows –

emp_id emp_name dept_name
001 Employee1 Office
002 Employee2 Office
003 Employee3 Office
004 Employee4 Office
005 Employee5 Office
006 Employee6 Office
007 Employee7 Office
008 Employee8 Office
009 Employee9 Office
001 Employee1 Sales
002 Employee2 Sales
003 Employee3 Sales
004 Employee4 Sales
005 Employee5 Sales
006 Employee6 Sales
007 Employee7 Sales
008 Employee8 Sales
009 Employee9 Sales

Scenario – Combining rows from two tables by using CROSS JOIN with WHERE clause.

Requirement - Combining rows from employee_details and department_details tables of specific columns by using CROSS JOIN with WHERE clause. The query was as follows –

SELECT emp_id,emp_name,department_details.dept_name 
FROM employee_details CROSS JOIN department_details 
WHERE employee_details.dept_id = 1000;

By executing above query we can get output like as follows -

emp_id emp_name dept_name
001 Employee1 Office
001 Employee1 Sales
002 Employee2 Office
002 Employee2 Sales
003 Employee3 Office
003 Employee3 Sales
004 Employee4 Office
004 Employee4 Sales
005 Employee5 Office
005 Employee5 Sales
006 Employee6 Office
006 Employee6 Sales
007 Employee7 Office
007 Employee7 Sales
008 Employee8 Office
008 Employee8 Sales
009 Employee9 Office
009 Employee9 Sales

Scenario – Combining rows from two tables by using CROSS JOIN with all columns.

Requirement - Combining rows from employee_details and department_details tables by using CROSS JOIN with all columns. The query was as follows –

SELECT * FROM employee_details CROSS JOIN department_details;

By executing above query we can combine all rows of all columns present in employee_details with all rows of all columns present in department_details table. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id dept_name dept_location
001 Employee1 Director 2019-07-11 45000.00 1000 Office Location1
002 Employee2 Director 2019-07-11 40000.00 1000 Office Location1
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000 Office Location1
004 Employee4 Manager Employee2 2019-10-08 25000.00 1000 Office Location1
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000 Office Location1
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000 Office Location1
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000 Office Location1
008 Employee8 Salesman Employee4 2019-09-09 14000.00 1000 Office Location1
009 Employee9 Salesman Employee4 2019-10-08 13000.00 1000 Office Location1
001 Employee1 Director 2019-07-11 45000.00 2000 Sales Location2
002 Employee2 Director 2019-07-11 40000.00 2000 Sales Location2
003 Employee3 Manager Employee1 2019-07-11 27000.00 2000 Sales Location2
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000 Sales Location2
005 Employee5 Analyst Employee3 2019-07-11 20000.00 2000 Sales Location2
006 Employee6 Analyst Employee3 2019-10-08 18000.00 2000 Sales Location2
007 Employee7 Clerk Employee3 2019-07-11 15000.00 2000 Sales Location2
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000 Sales Location2
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000 Sales Location2