13,828,691 members
Technical Blog
alternative version

#### Stats

4.5K views
7 bookmarked
Posted 29 Dec 2016
Licenced MIT

# Cross Join Introduction – Create Row Combinations

, 29 Dec 2016
Introduction to cross join - 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 JOIN`S 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 `employee`s are employed within each title, even those which contain no `employee`s.

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 `employee`s 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 `employee`s 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

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

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.

## Share

 Easy Computer Academy, LLC United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

## You may also be interested in...

 Pro

 First Prev Next
 Good simple to understand examples Jan Zumwalt4-Jan-17 8:16 Jan Zumwalt 4-Jan-17 8:16
 Last Visit: 15-Jan-19 19:58     Last Update: 15-Jan-19 19:58 Refresh 1