SQL CROSS JOIN to get Every Combination of Records

By: Jim Evans | Updated: 2021-10-28 | Comments (1) | Related: > JOIN Tables

Problem

In SQL there are many types of joins (INNER JOIN, OUTER JOIN, SELF JOIN, Natural JOIN, etc.), several that have been covered in previous articles, referenced below. But how do you join tables showing every possible combination of records? In this tutorial I will cover the Cross Join in a Microsoft SQL Server relational database and explain what it is and provide examples on when to use it.

Solution

The Cartesian Product as defined by Mathstopia is the multiplication of two sets to form the set of all ordered pairs. If you have a table with 10 rows and another table with 5 rows and you use a Cross Join you will get 5 x 10 or 50 rows in your result set also known as the Cartesian Product or Cartesian Join. In this article I show how to do this. For a good example of Cartesian Product visit: https://www.geeksforgeeks.org/cartesian-product-of-sets/.

SQL Server CROSS JOIN Tutorial

The CROSS JOIN is used to show every possible combination between two or more sets of data. You can do a cross join with more than 2 sets of data. Cross Joins are typically done without join criteria.

When to use a SQL CROSS JOIN

Cross Joins are rarely used in T-SQL code however, but they have their place. If you are trying to apply all rows from one table to all rows of another table, you will use a Cross Join. You might use a Cross Join to generate a Price List for a set of customers that include all your products. Also, a Cross Join might be used to generate a lot of test data. Occasionally, you may see a Cross Join that is a mistake of a forgotten join criteria in a SQL query.

SQL CROSS JOIN Syntax Example 1 - Various syntax

In this example I will show 3 SQL statements to perform a CROSS JOIN. The last being the preferred way. Basically, what these have in common is no join criteria. For the remainder of the tutorial, I will use the preferred CROSS JOIN syntax! Also, for the following examples I will be using Microsoft’s AdventureWorks sample database.

--Example 1: 3 ways to code Cross Join Condition
 
--1. Old comma syntax with a SELECT statement.
SELECT d.Name as 'Department', s.Name, s.StartTime, s.EndTime
FROM [HumanResources].[Department] d,
     [HumanResources].[Shift] s 

--2. Regular Join with require fake join predicate.
SELECT d.Name as 'Department', s.Name, s.StartTime, s.EndTime
FROM [HumanResources].[Department] d
JOIN [HumanResources].[Shift] s on 1=1 --Requires a join criteria
 
--3. The Preferred way!
SELECT d.Name as 'Department', s.Name, s.StartTime, s.EndTime
FROM [HumanResources].[Department] d
CROSS JOIN [HumanResources].[Shift] s;
GO

SQL CROSS JOIN Example 2 – Three Table Cross Join Clause

For this example, I will demo a 3 table Cross Join. The first table [HumanResources].[Shift] has 3 rows; the second table [HumanResources].[Department] has 16 rows; and the third table [HumanResources].[EmployeeDepartmentHistory] has 296 rows. We can expect a result set of 14,208, every combination of rows (3 x 16 x 296 = 14,208 number of rows).

 --Example 2:  3 table Cross Join
SELECT d.Name as 'Department', s.Name, h.StartDate, s.StartTime, s.EndTime
FROM [HumanResources].[Shift] s 
CROSS JOIN [HumanResources].[Department] d 
CROSS JOIN [HumanResources].[EmployeeDepartmentHistory] h
ORDER BY d.Name, h.StartDate, s.StartTime
GO

Example 2 Results

In this query, using a column from each of the three tables you can see that the results are every combination of the rows in each of the 3 tables. A partial result set is shown.

SQL CROSS JOIN Example 3 – Generate a Report

For this example, I will generate a report that shows Each Shift with a possible Start and End time for each department. Also, I show the same query but with a filter, limiting the results to just the Finance Department.

 --Example 3: Report that shows Each Shifts with possible Start and End time for the Finance Department
SELECT d.Name as 'Department', s.Name, s.StartTime, s.EndTime
FROM [HumanResources].[Shift] s 
CROSS JOIN [HumanResources].[Department] d 
ORDER BY d.Name , s.StartTime
 
-- Or you can add a filter via a WHERE clause to the Cross Join query
SELECT d.Name as 'Department', s.Name, s.StartTime, s.EndTime
FROM [HumanResources].[Shift] s 
CROSS JOIN [HumanResources].[Department] d 
WHERE d.Name = 'Finance'
ORDER BY d.Name , s.StartTime
GO

Example 3 Results

3a) Results show all possible combination of Shifts per Department. A partial result set is shown.

3b) Limited to the Finance department, but show every combination of possible Shifts.

SQL CROSS JOIN Example 4 – Generate a lot of data

For the final example I will show a how a Cross Join between 2 large tables can produce a very large results set. I will Cross Join the [Production].[Product] table which has 504 rows with the [Sales].[vIndividualCustomer] view which has 18,508 customer records. I’m inserting the results to a temp table #Temp1 which will have 9,328,032 rows. (504 x 18508 = 9,328,032).

 --Example 4: Generate a lot of Test data!!
SELECT c.BusinessEntityID, c.LastName, p.ProductID, p.ProductNumber, p.Name, p.ListPrice
into #Temp1
FROM [Production].[Product] p
CROSS JOIN [Sales].[vIndividualCustomer] c 
 

--(9328032 rows affected)

Example 4 Results

I will hold off on showing this large result set, but feel free to try this on your own.

Next Steps

I hope you found this tip on Cross Joins interesting. I’ve rarely used Cross Joins and only occasionally came across Cross Joins in code. However, they can be useful at times. Next, check out some other tips on other SQL Joins.

Getting Started with SQL INNER JOIN
Learn about Right Join Examples
Read more about JOINS: SQL LEFT JOIN Examples
Read about other SQL Server Join Example
Learn about SQL FULL OUTER JOIN with Examples
Learn about SQL Server Subquery Example
Learn about Join SQL Server tables where columns include NULL values
Visit Microsoft to find out how to download sample databases

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-10-28

Learning SQL CROSS JOIN with Examples