Cross Join Introduction – Create Row Combinations

A cross join is used when you wish to create a combination of every row from two tables. All row combinations are included in the result; this is commonly called cross product join. A common use for a cross join is to obtain all combinations of items, such as colors and sizes.

Note: The series starts with the article Introduction to Database Joins. All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server. In this article, we are going to cover inner joins.

What is a CROSS JOIN?

Cross joins are used to return every combination of rows from two tables, this sometimes called a Cartesian product.

In SQL Server, you can use the CROSS JOIN keywords to define a cross join.

Simple CROSS JOIN Example

Below is an example of a simple select statement with a CROSS JOIN clause.

SELECT columnlist
FROM   maintable
       CROSS JOIN secondtable

Suppose you wanted to return a report showing every combination of color and size as shown in our sample data below:

To do this in SQL, you could write:

SELECT c.Color, 
       s.Size
FROM   Color c
       CROSS JOIN Size s

You see that the result contains every combination of color and size. Since there are two rows in Color and four in Size, the final result is eight rows (2 x 4 = 8).

This combinatoric effect can make cross joins extremely dangerous! If you CROSS JOIN a table of 1,000 rows with another of 1,000, you end up with 1,000,000 in the result.

Now that you know the basics, let’s look at a really good example where CROSS JOINS help create better results.

Use CROSS JOIN to Create Reports

Let’s consider an example where the president of Adventure Works would like to know by gender, how many employees are employed within each title, even those which contain no employees.

This request on the surface seems easy enough, we can use the GROUP BY and COUNT to create the result! Here is the SQL:

SELECT   JobTitle,
         Gender,
         COUNT(1) as NumberEmployees
FROM     HumanResources.Employee
GROUP BY JobTitle, Gender
ORDER BY JobTitle, Gender

But notice that there are some missing combinations. For instance, even though there is a Female (F) Benefits Specialist, there isn’t one that is Male (M).

So how do you get combinations of JobTitle and Gender when the count is zero?

We can use a cross join. The idea is to first do a cross join on distinct values of gender and title. These results can then be outer joined back to the employee table to obtain the account.

So what does this query really do?

To make it simpler to write and read, I used CTEs (Common Table Expressions) to create this query. If you are not familiar with CTEs, think of them as views for now.

Note: You can read more about CTEs in my article Introduction to Common Table Expressions.

We create two CTEs of distinct JobTitle and Gender values from the Employee table. By cross joining these tables, we can display all possible combinations of Job Titles and Gender.

Here is the query we can use to create the distinct combinations:

WITH cteJobTitle (JobTitle)
AS  (SELECT DISTINCT JobTitle
     FROM   HumanResources.Employee),

cteGender (Gender)
AS (SELECT DISTINCT Gender
    FROM   HumanResources.Employee)

SELECT   J.JobTitle,
         G.Gender
FROM     cteJobTitle AS J
         CROSS JOIN cteGender AS G
ORDER BY J.JobTitle

Once you study the above query, you’ll see the CROSS JOIN is just creating combinations from two separate distinct valued lists, color coded blue and red, whose results are:

Now all we need to do is take the results and combine them with the summarized data we obtain by grouping the data. Again, a CTE is used to collect the summarized data. An OUTER JOIN is then used to combine this with all combinations of JobTitle and Gender.

The reason this works is that we are using an OUTER JOIN to return all results from one table regardless of whether they match another. This is important as we want to include all results from the cross join, which is all the combinations of gender and title, regardless of whether there is actually summarized data.

To summarize, here are the steps we are taking to create this result:

1. Get a distinct list of JobTitles. This happens in a CTE.
2. Get a distinct list of Genders. This happens in a CTE.
3. Create all possible combinations of JobTitles and Genders using CROSS JOIN.
4. Compute a summary count of employees by JobTitle and Gender.
5. Match the computed summary count with the distinct list.

Here is the final query which accomplishes these steps:

WITH     cteJobTitle (JobTitle)
AS       (SELECT DISTINCT JobTitle
          FROM   HumanResources.Employee),

         cteGender (Gender)
AS       (SELECT DISTINCT Gender
          FROM   HumanResources.Employee),

         cteCounts (JobTitle, Gender, NumberEmployees)
AS       (SELECT   JobTitle,
                   Gender,
                   COUNT(1) AS NumberEmployees
          FROM     HumanResources.Employee
          GROUP BY JobTitle, Gender)

SELECT   J.JobTitle,
         G.Gender,
         COALESCE (C.NumberEmployees, 0) as NumberEmployees
FROM     cteJobTitle AS J
         CROSS JOIN cteGender AS G
         LEFT OUTER JOIN cteCounts AS C
         ON C.JobTitle = J.JobTitle
            AND C.Gender = G.Gender
ORDER BY J.JobTitle, G.Gender;

To make it easier to read, the two CTEs and CROSS JOIN to create all combinations of JobTitle and Gender are colored blue and red. The CTE to summarize the data is colored green.

Here is the result of the query:

INNER JOIN as CROSS JOIN

As you get to know SQL, you realize there is usually more than one way to write a query. For instance, there is a way using the WHERE clause to have a CROSS JOIN behave like an INNER JOIN.

Let’s take these two tables:

Suppose we wish to query all employees and show their birth date and last names. To do this, we have to relate the Employee table to Person.

As we have seen, we can write a cross join to combine rows as so:

SELECT P.LastName,
       E.BirthDate
FROM   HumanResources.Employee E
       CROSS JOIN Person.Person P

But this query isn’t too useful in this case, as it returns 5,791,880 rows!

To limit the row combinations so the person records are properly matched to the employee rows, we can use a WHERE clause. Here is the final query:

SELECT P.LastName,
       E.BirthDate
FROM   HumanResources.Employee E
       CROSS JOIN Person.Person P
WHERE  P.BusinessEntityID = E.BusinessEntityID

Here are the first 14 rows of 290:

This query returns the same results as one written with an INNER JOIN. The query using an inner join is:

SELECT P.LastName,
       E.BirthDate
FROM   HumanResources.Employee E
       INNER JOIN Person.Person P
       ON P.BusinessEntityID = E.BusinessEntityID

Which query is better? If you look at the query plans, you’ll see they are very similar.

Here is the plan for the cross join…

Here is the plan for the inner join…

As you see they are identical. The reason they are so is that SQL is a declarative language, meaning we tell the DB what result we want, not necessarily how to do it. When we provide the DBMS with our query, the optimizer puts together the best plan. In most cases, it will be the same for equivalent statements.

The post Cross Join Introduction – Create Row Combinations appeared first on Essential SQL.