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