A Join A Day – The Cross Join

Introduction

This is the fourth post in my A Join A Day series about SQL Server Joins. Make sure to let me know how I am doing or ask your burning join related questions by leaving a comment below.

The cross join is probably the simplest of all joins. It takes each row from one row source and combines it with each row of another row source building a complete Cartesian product.

CROSS JOIN Example

Assume you want to generate a result set that contains a row for each combination of SalesTerritory and SalesReason from the AdventureWorks2008R2 database. Cross joining the two tables together will do just that:

This query will return the following output:

Each of the two tables contains ten rows. That means combining each row in the one with each row in the other will result in 100 rows. That is exactly the number you can see in the total row count in above image.

CROSS JOIN Syntax

The cross join syntax is straight forward: Table1 CROSS JOIN Table2. For the complete details go to http://msdn.microsoft.com/en-us/library/ms177634.aspx.

As with the inner join, changing the order of the two row sources of a cross join does not change the result. Because of that, SQL Server might switch the order during compilation for performance reasons.

CROSS JOIN Operator

There is actually no separate operator for a cross join. The execution plan for above query just shows an inner join:

However, there is a warning triangle on that operator. If you hover over it you will get the common operator properties pop-up:

All the way at the bottom it contains a warning section where it complains about the join not having a predicate. What a predicate is exactly we will cover later in this series. For now you can just think of it as the join condition. So this join does not have a condition. But that is exactly what we asked for. We want every row from the one side to be combined with every row from the other side. So if you actually meant to use a cross join, you can ignore this warning. The reason SQL Server warns us here is that this operation can produce a lot of rows making the query potentially very expensive.

Other than for the inner join however, the cross join can only be processed by the Nested Loops operator. As we will see later, the other two algorithms are not suited for this task.

Summary

Today we looked at the cross join. A cross join takes every row from one row source and combines it with every row from a second row source. A cross join is executed by the same Nested Loops operator that is responsible for inner joins as well. When looking at the execution plan you will see a warning that this join does not have a join condition. However, that is what defines a cross join so you can ignore that warning.

A Join A Day

This post is part of my December 2012 "A Join A Day" blog post series. You can find the table of contents with all posts published so far in the introductory post: A Join A Day – Introduction. Check back there frequently throughout the month.