Understanding SQL CROSS JOIN with Examples

CROSS JOIN in SQL

Cross Join in SQL! What exactly is it..? CROSS JOIN in SQL is an important topic that you should never skip off when you are learning JOIN in SQL. A CROSS JOIN in SQL is a process that combines every row from one table with every record from another table. Every entry from the first table links with every entry from the second table, producing a fully mixed set of data. It's a helpful tool for examining connections between various information sets.

In this SQL Server tutorial, you will get the chance to learn what is CROSS JOIN in SQL, including the syntax of CROSS JOIN in SQL, how does CROSS JOIN in SQL works, when should we use CROSS JOIN in SQL, CROSS JOIN in SQL example, and many more.

What is a CROSS JOIN?

CROSS JOIN in SQL produces all possible pairings by combining each row from one table with each row from another. Unlike other joins, it doesn't need any matching requirements. A CROSS JOIN, for instance, will list every student and every subject in a database, including students and subjects.

CROSS JOIN in SQL Syntax

We can declare the Syntax of CROSS JOIN in two ways that are:

1. Explicit Syntax

SELECT *
FROM TableA
CROSS JOIN TableB;

2. Implicit Syntax

SELECT *
FROM TableA, TableB;

In this query, every row from TableA will be combined with every row from TableB.

How does CROSS JOIN work in SQL?

To understand how CROSS JOIN works in SQL, we should examine an example where we have created two tables that are 'Students'and 'Subjects', and performing the CROSS JOIN operation:

Table: Students

Table: Subjects

When we perform the CROSS JOIN operation between these two tables:

SELECT Students.Name, Subjects.SubjectName
FROM Students
CROSS JOIN Subjects;

The result will look like this:

Key Points to Remember While Using CROSS JOIN in SQL

We should remember some key points when we are using CROSS JOIN operation in SQL :

1. No Condition Required: Unlike other joins, a CROSS JOIN doesn’t need any matching condition or ON clause between the two tables. It combines every row from both tables automatically.
2. Cartesian Product: It creates all possible combinations of rows. So, if one table has "m" rows and the other has "n" rows, the result will have m * n rows.
3. Useful in Specific Scenarios: Even though it can seem like a lot of data, CROSS JOIN is helpful when you need to make combinations from two different sets of rows.

When should you use CROSS JOIN in SQL?

We should use CRROSS JOIN in several requirements that are:

• Generating Test Data: To produce a huge dataset for testing reasons, utilize CROSS JOIN if you need to test scenarios containing all possible item combinations..
• Combinations and Pairing: It is helpful when pairing elements from two different sets, such as creating a report of every employee in every department or every student with every possible course.

CROSS JOIN vs. INNER JOIN

Here, we have explained the major difference between CROSS JOIN and INNER JOIN:

• CROSS JOIN: Without the need for a matching condition, a CROSS JOIN generates a Cartesian product by returning every conceivable row combination between two databases.
• INNER JOIN: Based on a given criterion, an INNER JOIN returns just the rows that, after removing mismatched rows, have a matching value in both tables.

For example, if we had added a column like PreferredSubject to the Students table, we could use an INNER JOIN to get only the students who prefer certain subjects rather than pairing every student with every subject, like CROSS JOIN does.

Examples of CROSS JOIN in Real-World Scenarios

Imagine a clothing store that sells shirts and pants. The store wants to list all possible outfit combinations for customers.

Shirts Table

Pants Table

Using a CROSS JOIN, the store can generate a list of all possible shirt and pant combinations.

SELECT ShirtColor, PantsColor
FROM Shirts
CROSS JOIN Pants;

Use CROSS JOIN to join three tables in SQL Server

You may just combine each table in the query to utilize a CROSS link to link three tables in SQL Server. Here's how to go about it:

Example Tables

Students Table:

Subjects Table:

Classes Table:

SQL Query Using CROSS JOIN

SELECT S.StudentName, Sub.SubjectName, C.ClassName
FROM Students S
CROSS JOIN Subjects Sub
CROSS JOIN Classes C;

Result:

Use CROSS JOIN to Generate a Report

You may use a CROSS JOIN in SQL to create a report that displays every possible combination of Employees, Projects, and Departments.

Example

Employees Table:

Projects Table:

Departments Table:

SQL Query

SELECT 
    E.EmployeeName,
    P.ProjectName,
    D.DepartmentName
FROM 
    Employees E
CROSS JOIN 
    Projects P
CROSS JOIN 
    Departments D;

Result

This report provides all possible combinations of employees, projects, and departments for analysis.

Performance Considerations with CROSS JOIN

When utilizing CROSS JOIN with big tables, exercise caution because it generates a huge result set (the Cartesian product of two tables). Your searches may execute more slowly if both tables contain a lot of records since the resultant dataset might increase very fast.

Your database might not be able to manage a CROSS JOIN, for example, if TableA has 10,000 rows and TableB has 5,000 rows. This would result in 10,000 * 5,000 = 50,000,000 records.

Read More: Top 50 SQL Server Interview Questions and Answers

Conclusion

In conclusion, a CROSS JOIN in SQL creates a Cartesian product by combining every row from two tables, resulting in all possible row combinations. It does not require any matching condition, making it useful in scenarios where you need every combination of data from two datasets. However, due to the large number of combinations, it should be used carefully to avoid generating excessively large result sets.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE