SQL 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:
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:
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:
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:
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.
