In SQL Server, there are various types of joins such as INNER JOIN, OUTER JOIN, SELF JOIN, and NATURAL JOIN. However, there is one join called the Cross Join that allows you to show every possible combination of records between two or more sets of data.

The Cross Join, also known as the Cartesian Join or Cartesian Product, is used to generate a result set that contains all possible combinations of rows from two or more tables. It is typically done without any join criteria.

When would you use a Cross Join? Here are a few scenarios:

• Applying all rows from one table to all rows of another table
• Generating a price list for a set of customers that includes all products
• Generating a large amount of test data
• Accidentally forgetting to include join criteria in a SQL query

Let’s take a look at some examples of how to use the Cross Join in SQL Server.

Example 1: Basic Cross Join

In this example, we have two tables: [HumanResources].[Department] and [HumanResources].[Shift]. We want to show every possible combination of records between these two tables.

Here are three ways to write the Cross Join query:

--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 a required 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;

Example 2: Three Table Cross Join

In this example, we have three tables: [HumanResources].[Shift], [HumanResources].[Department], and [HumanResources].[EmployeeDepartmentHistory]. We want to show every possible combination of records between these three tables.

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

Example 3: Generating a Report

In this example, we want to generate a report that shows each shift with a possible start and end time for each department. We can also add a filter to limit the results to a specific department, such as the Finance department.

--Report that shows each shift with possible start and end time for all departments
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
 
--Report that shows each shift 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 
WHERE d.Name = 'Finance'
ORDER BY d.Name , s.StartTime

Example 4: Generating a Large Amount of Data

In this final example, we want to generate a large amount of test data by performing a Cross Join between two large tables: [Production].[Product] and [Sales].[vIndividualCustomer].

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

These are just a few examples of how the Cross Join can be used in SQL Server. It is a powerful tool for generating combinations of records and can be handy in various scenarios.

Remember to use the Cross Join judiciously and ensure that it is the appropriate solution for your specific requirements.

That’s all for this tutorial on understanding SQL Server Cross Join. I hope you found it informative and helpful!

Article Last Updated: 2021-10-28