Cross Join

• Join operation in SQL is used to combine multiple tables together into a single table.
• If we use the cross join to combine two different tables, then we will get the Cartesian product of the sets of rows from the joined table. When each row of the first table is combined with each row from the second table, it is known as Cartesian join or cross join.
• After performing the cross join operation, the total number of rows present in the final table will be equal to the product of the number of rows present in table 1 and the number of rows present in table 2.
• For example:
  If there are two records in table 1 and three records in table 2, then after performing cross join operation, we will get six records in the final table.
• Let us take a look at the syntax of writing a query to perform the cross join operation in SQL.

SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 CROSS JOIN TableName2 ON TableName1.ColumnName = TableName2.ColumnName;    

Now let us see take a deeper dive into the cross join in SQL with the help of examples. All the queries in the examples will be written using the MySQL database.

Consider we have the following tables with the given data:

Table 1: MatchScore

Player	Department_id	Goals
Franklin	1	2
Alan	1	3
Priyanka	2	2
Rajesh	3	5

Table 2: Departments

Department_id	Department_name
1	IT
2	HR
3	Marketing

Table 3: employee

EmployeeID	Employee_Name	Employee_Salary
1	Arun Tiwari	50000
2	Sachin Rathi	64000
3	Harshal Pathak	48000
4	Arjun Kuwar	46000
5	Sarthak Gada	62000

Table 4: department

DepartmentID	Department_Name	Employee_ID
1	Production	1
2	Sales	3
3	Marketing	4
4	Accounts	5

Table 5: loan

LoanID	Branch	Amount
1	B1	15000
2	B2	10000
3	B3	20000
4	B4	100000

Table 6: borrower

CustID	CustName	LoanID
1	Sonakshi Dixit	1
2	Shital Garg	4
3	Swara Joshi	5
4	Isha Deshmukh	2

Table 7: customer

Customer_ID	Name	Age	Salary
1	Aryan Jain	51	56000
2	Arohi Dixit	21	25000
3	Vineet Garg	24	31000

Table 8: orders

Order_ID	Order_Date	Cutomer_ID	Amount
1	2012-01-20	2	3000
2	2012-05-18	2	2000
3	2012-06-28	3	4000

Example 1:

Write a query to perform the cross join operation considering the MatchScore table as the left table and the Departments table as the right table.

Query:

SELECT * FROM MatchScore CROSS JOIN Departments;  

We have used the SELECT command with the asterisk to retrieve all the columns present in the MatchScore and Departments table. Then we have used the CROSS JOIN keyword to perform the cross join operation on the MatchScore and Departments table. Since there are 4 records in the MatchScore and 3 records in the Departments table, after performing the cross join operation, we will get 12 rows.

After executing this query, you will find the following result:

Player	Department_id	Goals	Depatment_id	Department_name
Franklin	1	2	1	IT
Alan	1	3	1	IT
Priyanka	2	2	1	IT
Rajesh	3	5	1	IT
Franklin	1	2	2	HR
Alan	1	3	2	HR
Priyanka	2	2	2	HR
Rajesh	3	5	2	HR
Franklin	1	2	3	Marketing
Alan	1	3	3	Marketing
Priyanka	2	2	3	Marketing
Rajesh	3	5	3	Marketing

Each row from the MatchScore table is combined with each row of the Departments table. Since there are four records in the MatchScore and three records in the Departments table, we have got 12 rows in the final table after performing the cross join operation.

Example 2:

Write a query to perform the cross join operation considering the employee table as the left table and the department table as the right table.

Query:

mysql> SELECT *FROM employee CROSS JOIN department;  

We have used the SELECT command with the asterisk to retrieve all the columns present in the employee and department table. Then we have used the CROSS JOIN keyword to perform the cross join operation on the employee and department table. Since there are five records in the employee and four records in the department table, after performing the cross join operation, we will get 20 rows.

After executing this query, you will find the following result:

EmployeeID	Employee_Name	Employee_Salary	DepartmentID	Department_Name	Employee_ID
1	Arun Tiwari	50000	1	Production	1
1	Arun Tiwari	50000	2	Sales	3
1	Arun Tiwari	50000	3	Marketing	4
1	Arun Tiwari	50000	4	Accounts	5
2	Sachin Rathi	64000	1	Production	1
2	Sachin Rathi	64000	2	Sales	3
2	Sachin Rathi	64000	3	Marketing	4
2	Sachin Rathi	64000	4	Accounts	5
3	Harshal Pathak	48000	1	Production	1
3	Harshal Pathak	48000	2	Sales	3
3	Harshal Pathak	48000	3	Marketing	4
3	Harshal Pathak	48000	4	Accounts	5
4	Arjun Kuwar	46000	1	Production	1
4	Arjun Kuwar	46000	2	Sales	3
4	Arjun Kuwar	46000	3	Marketing	4
4	Arjun Kuwar	46000	4	Accounts	5
5	Sarthak Gada	62000	1	Production	1
5	Sarthak Gada	62000	2	Sales	3
5	Sarthak Gada	62000	3	Marketing	4
5	Sarthak Gada	62000	4	Accounts	5

Each row from the employee table is combined with each row of the department table. Since there are five records in the employee table and four records in the department table, we have got 20 rows in the final table after performing the cross join operation.

Example 3:

Write a query to perform the cross join operation considering the loan table as the left table and the borrower table as the right table.

Query:

mysql> SELECT *FROM loan CROSS JOIN borrower;  

We have used the SELECT command with the asterisk to retrieve all the columns present in the loan and the borrower table. Then we have used the CROSS JOIN keyword to perform the cross join operation on the loan and the borrower table. Since there are four records in the loan table and four records in the borrower table, after performing the cross join operation, we will get 16 rows.

After executing this query, you will find the following result:

LoanID	Branch	Amount	CustID	CustName	LoanID
1	B1	15000	1	Sonakshi Dixit	1
2	B2	10000	1	Sonakshi Dixit	1
3	B3	20000	1	Sonakshi Dixit	1
4	B4	100000	1	Sonakshi Dixit	1
1	B1	15000	2	Shital Garg	4
2	B2	10000	2	Shital Garg	4
3	B3	20000	2	Shital Garg	4
4	B4	100000	2	Shital Garg	4
1	B1	15000	3	Swara Joshi	5
2	B2	10000	3	Swara Joshi	5
3	B3	20000	3	Swara Joshi	5
4	B4	100000	3	Swara Joshi	5
1	B1	15000	4	Isha Deshmukh	2
2	B2	10000	4	Isha Deshmukh	2
3	B3	20000	4	Isha Deshmukh	2
4	B4	100000	4	Isha Deshmukh	2

Each row from the loan table is combined with each row of the borrower table. Since there are four records in the loan table and four records in the borrower table, after performing the cross join operation, we have got 16 rows.

Example 4:

Write a query to perform the cross join operation considering the customer table as the left table and the orders table as the right table.

Query:

mysql> SELECT *FROM customer CROSS JOIN orders;  

We have used the SELECT command with the asterisk to retrieve all the columns present in the customer and orders table. Then we have used the CROSS JOIN keyword to perform the cross join operation on the customer table and the orders table. Since there are three records in the loan table and three records in the orders table, after performing the cross join operation, we will get 9 rows.

After executing this query, you will find the following result:

Customer_ID	Name	Age	Salary	Order_ID	Order_Date	Customer_ID	Amount
1	Aryan Jain	51	56000	1	2012-01-20	2	3000
2	Arohi Dixit	21	25000	1	2012-01-20	2	3000
3	Vineet Garg	24	31000	1	2012-01-20	2	3000
1	Aryan Jain	51	56000	2	2012-05-18	2	2000
2	Arohi Dixit	21	25000	2	2012-05-18	2	2000
3	Vineet Garg	24	31000	2	2012-05-18	2	2000
1	Aryan Jain	51	56000	3	2012-06-28	3	4000
2	Arohi Dixit	21	25000	3	2012-06-28	3	4000
3	Vineet Garg	24	31000	3	2012-06-28	3	4000

Each row from the customers’ table is combined with each row of the orders table. Since there are three records in the loan table and three records in the orders table, after performing the cross join operation, we will get 9 rows.