SQL Server - Using CROSS JOIN (Cartesian Product) to Create Rows in Query Result

Views: 3.547
Reading Time: 4 minutes

Hey guys!
In this post I would like to share with you the operation of an operator that I rarely see anyone using, which is CROSS JOIN, and which in certain scenarios may be useful.

What is CROSS JOIN?

CROSS JOIN returns the combination of each row between the tables involved, also known as Cartesian Product, which I will demonstrate below for ease of understanding.

1 Example

In this example I will create 2 tables and cross data from the two tables to create a Cartesian product by combining each row of the #Sorbet table with each row of the #Complement table (yes, I feel like eating ice cream).

Script for data generation from this example:

Result:

Well, now let's create the Cartesian product between the two tables:

The only way to create a Cartesian product is by using CROSS JOIN?

As we have seen, the Cartesian product creates all possible combinations between the data involved. Since in our example it was a table of 3 records in a CROSS JOIN operation with another table of 4 records, our final result was 12 rows (3 x 4).

If each table had 1.000 records, the end result would be 1 million records (1.000 x 1.000), so the Cartesian product is generally not desirable in a JOIN operation, and can cause performance issues if it occurs unwanted. (as in a wrong join clause, for example)

2 Example

In this example, I will demonstrate a practical scenario where the Cartesian product justifies its use, using the organizers and speakers of the 1 SQL Day ES.

Script for data generation from this example:

Now, I want to show you how to create a report that shows me gender, state, and how many people we have in this grouping:

Result:

As you may have noticed, only the existing records in the table are returned. For example, in the state of “DF” only returned results with the sex “F”, because in our table there are only people with the female gender in the state DF.

But what if I want to show also genres that do not have people registered in the table? How do I do? Well, for starters, let's use CTE (Common Table Expressions) to match gender with cities and states and create all possible combinations:

Now, let's modify this script to get us the number of people from each of the combinations, which is what we want to show in the result of our query:

Result:

Conclusion

Well guys, I hope I was able to demonstrate that the Cartesian product can indeed have its usefulness in developing T-SQL queries. To create the 2 example without using it, we would have to create cursor or while loop, which has a bad performance and the routine ends up being much more complex.

Although I have shown good use of this feature, be very careful when using it, especially with large data, as CROSS JOIN or Cartesian product creates all possible combinations between 2 or more ResultSets, crossing each table row. A with each row of table B.

We often see poorly constructed JOIN's (INNER, LEFT, RIGHT) that end up making an unintended Cartesian product, generating many unwanted lines and causing a query performance issue.

That said, I close this post.
A big hug to you and until next time.