Click here to Skip to main content
13,150,178 members (59,476 online)
Click here to Skip to main content
Add your own
alternative version

Stats

2.6K views
7 bookmarked
Posted 29 Dec 2016
MIT

Cross Join Introduction – Create Row Combinations

, 29 Dec 2016
Rate this:
Please Sign up or sign in to vote.
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:

Cross Join Two Tables to Get Combinations

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).

Missing Group in Report

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:
Combinations of Job Titles and Gender using CROSS JOIN

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:

Cross Join Reporting Trick

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:

Table Relationships

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:

Cross Join Results

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

Cross Join Query Plan

Here is the plan for the inner join…

INNER JOIN Query Plan

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.

License

This article, along with any associated source code and files, is licensed under The MIT License

Share

About the Author

essentialSQL
Easy Computer Academy, LLC
United States 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.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

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.

And now I want to help you get the same results.

The first step is simple, click here ==> http://www.essentialsql.com/get-started-with-sql-server/

You may also be interested in...

Pro
Pro

Comments and Discussions

 
PraiseGood simple to understand examples Pin
Jan Zumwalt4-Jan-17 7:16
memberJan Zumwalt4-Jan-17 7:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170924.2 | Last Updated 30 Dec 2016
Article Copyright 2016 by essentialSQL
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid