Learning SQL CROSS JOIN with Examples
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.
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.
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
View all my tips
Article Last Updated: 2021-10-28