Understanding SQL CROSS JOIN with Examples

SQL CROSS JOIN

SQL JOIN operations are used to combine data from multiple tables into a single result set. Among the different types of joins, CROSS JOIN, also called a Cartesian Join, is one of the simplest because it combines every row from the first table with every row from the second table.

The total number of rows produced by a CROSS JOIN is calculated by multiplying the number of rows in both tables. For example, if Table 1 contains 2 rows and Table 2 contains 3 rows, the CROSS JOIN produces 2 × 3 = 6 rows.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

SQL Syntax for CROSS JOIN

SELECT TableName1.columnName1, TableName2.columnName2
FROM TableName1
CROSS JOIN TableName2;

This syntax generates all possible combinations of rows between the two specified tables.

Let’s understand the CROSS JOIN operation in more detail with the following 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:

The MatchScore table contains 4 rows, while Departments contains 3 rows. Therefore, the CROSS JOIN produces 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
…	…	…	…	…

As shown above, every record from MatchScore is paired with every record 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:

There are 5 employees and 4 departments. The CROSS JOIN therefore creates 5 × 4 = 20 rows, pairing every employee with every 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:

The Loan table contains 4 records and the Borrower table also contains 4 records. Consequently, the CROSS JOIN produces 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:

Because there are 3 customers and 3 orders, the resulting CROSS JOIN contains 3 × 3 = 9 rows.

Download New Real Time Projects :- Click here

Conclusion

The SQL CROSS JOIN combines every row from one table with every row from another table, producing a Cartesian product. The total number of rows in the result is determined by multiplying the number of rows in the two source tables.

Although CROSS JOIN can be useful in specific situations, it should be used carefully because combining large tables can produce a very large result set. Understanding how the Cartesian product works allows you to use CROSS JOIN effectively while keeping potential performance concerns in mind.

---

SEO Keywords

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