SQL CROSS JOIN
The JOIN operation in SQL allows us to combine multiple tables into a single dataset. One of the simplest types of joins is the CROSS JOIN, also known as a Cartesian Join. This operation returns the Cartesian product of the sets of rows from the joined tables. This means that each row from the first table is combined with every row from the second table, leading to a final table whose total number of rows is equal to the product of the number of rows in each table.
For example, if Table 1 has 2 rows and Table 2 has 3 rows, performing a CROSS JOIN will result in 2 × 3 = 6 rows in the final dataset.
Complete Python Course with Advance topics:-Click Here
SQL Tutorial :-Click Here
SQL Syntax for CROSS JOIN
SELECT TableName1.columnName1, TableName2.columnName2
FROM TableName1
CROSS JOIN TableName2;
This basic syntax retrieves all possible row combinations between the two tables.
Now, let’s explore CROSS JOIN in detail using MySQL examples.
Example 1: CROSS JOIN Between MatchScore and Departments Tables
Given Tables:
MatchScore
| Player | Department_id | Goals |
|---|---|---|
| Franklin | 1 | 2 |
| Alan | 1 | 3 |
| Priyanka | 2 | 2 |
| Rajesh | 3 | 5 |
Departments
| Department_id | Department_name |
|---|---|
| 1 | IT |
| 2 | HR |
| 3 | Marketing |
Query:
SELECT * FROM MatchScore
CROSS JOIN Departments;
Output:
After executing this query, the result will contain 4 × 3 = 12 rows:
| Player | Department_id | Goals | Department_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 |
| … | … | … | … | … |
Each row from MatchScore is combined with every row from Departments.
Example 2: CROSS JOIN Between Employee and Department Tables
Given Tables:
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 |
Department
| DepartmentID | Department_Name |
|---|---|
| 1 | Production |
| 2 | Sales |
| 3 | Marketing |
| 4 | Accounts |
Query:
SELECT * FROM Employee
CROSS JOIN Department;
Output:
After execution, we get 5 × 4 = 20 rows combining each employee with each department.
Example 3: CROSS JOIN Between Loan and Borrower Tables
Given Tables:
Loan
| LoanID | Branch | Amount |
|---|---|---|
| 1 | B1 | 15000 |
| 2 | B2 | 10000 |
| 3 | B3 | 20000 |
| 4 | B4 | 100000 |
Borrower
| CustID | CustName |
|---|---|
| 1 | Sonakshi Dixit |
| 2 | Shital Garg |
| 3 | Swara Joshi |
| 4 | Isha Deshmukh |
Query:
SELECT * FROM Loan
CROSS JOIN Borrower;
Output:
Since there are 4 loans and 4 borrowers, the resulting dataset contains 4 × 4 = 16 rows.
Example 4: CROSS JOIN Between Customer and Orders Tables
Given Tables:
Customer
| Customer_ID | Name | Age | Salary |
|---|---|---|---|
| 1 | Aryan Jain | 51 | 56000 |
| 2 | Arohi Dixit | 21 | 25000 |
| 3 | Vineet Garg | 24 | 31000 |
Orders
| Order_ID | Order_Date | Amount |
|---|---|---|
| 1 | 2012-01-20 | 3000 |
| 2 | 2012-05-18 | 2000 |
| 3 | 2012-06-28 | 4000 |
Query:
SELECT * FROM Customer
CROSS JOIN Orders;
Output:
Since there are 3 customers and 3 orders, we get 3 × 3 = 9 rows.
Download New Real Time Projects :-Click here
Complete Advance AI topics:- CLICK HERE
Conclusion
SQL’s CROSS JOIN function combines every row of one table with every row of another table to produce a Cartesian product.
The product of the number of rows in the two tables determines the total number of rows in the result set.
Although helpful in certain situations, CROSS JOIN should be used with caution because it can generate enormous databases.
You may take advantage of CROSS JOIN’s capabilities in SQL queries while keeping performance issues in mind if you understand it.
sql cross join vs full join
sql scross join example
sql cross join in sql example
self join in sql
sql cross join vs inner join
sql cross join syntax
sql cross join in oracle
sql cross join in mysql
sql joins
cross join
sql cross join example
sql cross join w3schools
sql cross join multiple columns
