Understanding SQL CROSS JOIN with Examples

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

Departments

Query:

SELECT * FROM MatchScore 
CROSS JOIN Departments;

Output:

After executing this query, the result will contain 4 × 3 = 12 rows:

Each row from MatchScore is combined with every row from Departments.

Example 2: CROSS JOIN Between Employee and Department Tables

Given Tables:

Employee

Department

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

Borrower

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

Orders

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