Learn How to Combine Data with a CROSS JOIN
A cross join is used when you wish to create 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 create 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 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 basic, 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 CTE’s (Common Table Expressions) to create this query. If you not familiar with CTE’s, think of them as views for now. Note: you can read more about CTE’s in my article Introduction to Common Table Expressions.
We create two CTE’s 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. A 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:
- Get a distinct list of JobTitles. This happens in a CTE.
- Get a distinct list of Genders. This happens in a CTE.
- Create all possible combinations of Job Titles and Genders using CROSS JOIN.
- Compute a summary count of employees by JobTitle and Gender.
- 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 CTE’s 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.
Also notice we use the SQL COALESCE function to replace NULL with a zero value.
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, no necessarily how to do it. When we provide the DBMS with our query the optimizer put together the best plan. In most cases it will be the same for equivalent statements.