CROSS JOIN is in the spotlight. This article finishes our small series of SQL JOIN-related publications. If you missed the previous two articles, refer to them as follows:
- Your Ultimate Guide to SQL Join – Part 1: INNER JOIN
- Your Ultimate Guide to SQL Join – Part 2: OUTER JOIN
SQL Server CROSS JOIN is the simplest of all joins. It implements a combination of 2 tables without a join condition. If you have 5 rows in one table and 3 rows in another, you get 15 combinations. Another definition is a Cartesian Product.
Now, why would you want to combine tables without a join condition? Hang on a bit because we are getting there. First, let’s refer to the syntax.
SQL CROSS JOIN Syntax
Like with INNER JOIN, you can have a CROSS JOIN from 2 standards, SQL-92 and SQL-89. T-SQL supports both syntaxes, but I prefer SQL-92. Check part 1 dedicated to INNER JOIN if you want to know why.
SQL-92 Syntax
SELECT
a.column1
,b.column2
FROM Table1 a
CROSS JOIN Table2 b
SQL-89
SELECT
a.column1
,b.column2
FROM Table1 a, Table2 b
Much like SQL-89 – INNER JOIN without the join condition.
5 Examples of Using SQL Server CROSS JOIN
You may wonder when you can use SQL CROSS JOIN. Of course, it’s useful for shaping value combinations. What else?
1. Test Data
If you need a large amount of data, a CROSS JOIN will help you. For example, you have a table of vendors and products. Another table contains products that the vendor offers. If it is empty and you need data quickly, here’s what you can do:
SELECT
P.ProductID
,v.BusinessEntityID AS VendorID
FROM
Production.Product p
CROSS JOIN Purchasing.Vendor v
It generated 52,416 records in my copy of AdventureWorks. That’s enough to test apps and performance. However, if you present your app to users, use your source instead of this data from our example.
2. Getting Results from Missing Combinations
In the previous article, we illustrated the usage of the OUTER JOIN for getting results from missing values. This time, we will use missing combinations. Let’s try getting the products where Store 294 didn’t make money.
-- get store 294 ('Professional Sales and Service') list of products without sales orders for January 2014
SELECT DISTINCT
b.Name AS Product
FROM Sales.Store a
CROSS JOIN Production.Product b
LEFT JOIN (SELECT
c.StoreID
,a.ProductID
,SUM(a.LineTotal) AS OrderTotal
FROM Sales.SalesOrderDetail a
INNER JOIN Sales.SalesOrderHeader b ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Sales.Customer c ON b.CustomerID = c.CustomerID
WHERE c.StoreID = 294 AND
b.OrderDate BETWEEN '01/01/2014' AND '01/31/2014'
GROUP BY c.StoreID, a.ProductID) d ON a.BusinessEntityID = d.StoreID
AND b.ProductID = d.ProductID
WHERE d.OrderTotal IS NULL
AND a.BusinessEntityID = 294
ORDER BY b.Name
As you can see, we first need all product and store combinations – we use a CROSS JOIN. Then, we need a list of products sold for the January 2014 period. Finally, apply LEFT JOIN to that list and use the WHERE clause to get only the products without sales. Then, we get information about products that were not sold.
3. Forming Words from Letter Combinations
If you’re into word games with letter combinations, you can use CROSS JOIN with a self-join. Here’s a sample using 3 letters ‘D’, ‘O’, and ‘G’.
DECLARE @table TABLE(letter CHAR(1) NOT NULL)
INSERT INTO @table
VALUES ('D'),('O'),('G')
SELECT
a.letter
,b.letter
,c.letter
FROM @table a
CROSS JOIN @table b
CROSS JOIN @table c
WHERE a.letter + b.letter + c.letter LIKE '%O%'
A similar code without the WHERE clause will generate 27 records. The WHERE clause above helped eliminate combinations of 3 similar letter combinations like ‘DDD’ or ‘GGG’. Below is the result.
Of course, since I didn’t put much intelligence into the query, most of the results are non-words. Still, it helps in the thinking part of the game.
4. Meal Suggestions
We all love food, but it can be hard to choose the right combinations. As in the previous example concepts, here is how to get possible meal combinations:
DECLARE @FoodMenu TABLE(FoodItem VARCHAR(50) NOT NULL, ItemType CHAR(1) NOT NULL)
-- main course
INSERT INTO @FoodMenu
VALUES
('Spaghetti with Meatballs','M'),
('Spaghetti with Fried Chicken','M'),
('Rice with Roasted Chicken','M')
-- side dish
INSERT INTO @FoodMenu
VALUES
('Buttered Corn and Carrots','S'),
('French Fries','S'),
('Vegetable Coleslaw','S')
-- drinks
INSERT INTO @FoodMenu
VALUES
('Orange Juice','D'),
('Pineapple Juice','D'),
('Soda','D')
SELECT
a.FoodItem AS MainCourse
,b.FoodItem AS SideDish
,c.FoodItem AS Drinks
FROM @FoodMenu a
CROSS JOIN @FoodMenu b
CROSS JOIN @FoodMenu c
WHERE a.ItemType = 'M' AND
b.ItemType = 'S' AND
c.ItemType = 'D'
The result:
Some of them are desirable. Some are like, ‘forget it!’ It depends on your taste.
5. T-shirt Design Selection
Another possible use of CROSS JOIN is getting design combinations for shirts. Here’s a sample code:
DECLARE @tshirts TABLE(attributeType CHAR(1) NOT NULL, Attribute VARCHAR(15))
--size
INSERT INTO @tshirts
VALUES
('S','Small'),
('S','Medium'),
('S','Large')
--color
INSERT INTO @tshirts
VALUES
('C','Red'),
('C','Blue'),
('C','Green'),
('C','Black'),
('C','Purple'),
('C','Yellow'),
('C','White')
--design
INSERT INTO @tshirts
VALUES
('D','Plain'),
('D','Printed')
SELECT
a.Attribute AS Size
,b.Attribute AS Color
,c.Attribute AS Design
FROM @tshirts a
CROSS JOIN @tshirts b
CROSS JOIN @tshirts c
WHERE a.attributeType = 'S' AND
b.attributeType = 'C' AND
c.attributeType = 'D'
And the results? Take a look at its portion in Figure 3:
Can you think of some more examples?
SQL Server CROSS JOIN Performance
What’s the catch in using CROSS JOIN? For what it’s worth, CROSS JOIN can cause performance issues if you are not careful. The scariest part is, it forms a product of 2 sets. Thus, without limiting results in a WHERE clause, Table1 with 1000 records CROSS JOIN with Table2 with 1,000,000 records will become 1,000,000,000 records. Consequently, that’s a lot of pages to read by SQL Server.
As an example, consider pairing male and female employees in AdventureWorks.
USE AdventureWorks
GO
SELECT
P.LastName + ISNULL(' ' + p.Suffix,'') + ', ' + P.FirstName + ISNULL(' ' + P.MiddleName,'') AS Male
,P1.LastName + ISNULL(' ' + p1.Suffix,'') + ', ' + P1.FirstName + ISNULL(' ' + P1.MiddleName,'') AS Female
FROM HumanResources.Employee e
INNER JOIN Person.Person p ON e.BusinessEntityID = P.BusinessEntityID
CROSS JOIN HumanResources.Employee e1
INNER JOIN Person.Person p1 ON e1.BusinessEntityID = p1.BusinessEntityID
WHERE e.Gender = 'M'
AND e1.Gender = 'F'
The above code gives you all possible pairs of male and female employees. I got only 17,304 records, but look at the logical reads in Figure 4:
Did you see the logical reads of the Person table? That’s reading 53,268 x 8KB pages! Not to mention the WorkTable logical reads are done in tempdb.
The conclusion? Check the STATISTICS IO, and if it troubles you to see big logical reads, express a query differently. Additional conditions in the WHERE clause or a divide-and-conquer approach can help.
When a CROSS JOIN Becomes an INNER JOIN
Yes, that’s right. SQL Server can process a CROSS JOIN as an INNER JOIN. We mentioned before that when a RIGHT JOIN is processed as a LEFT JOIN, it can apply to CROSS JOIN. Have a look at the code below:
SELECT
c.CustomerID
,c.AccountNumber
,P.BusinessEntityID
,P.LastName
,P.FirstName
FROM Sales.Customer c
CROSS JOIN Person.Person p
WHERE c.PersonID = P.BusinessEntityID
Before we check out the Execution Plan, let’s have the INNER JOIN equivalent.
SELECT
c.CustomerID
,c.AccountNumber
,P.BusinessEntityID
,P.LastName
,P.FirstName
FROM Sales.Customer c
INNER JOIN Person.Person p ON c.PersonID = P.BusinessEntityID
Now, check the Execution Plan below.
The top plan is the query using a CROSS JOIN. The bottom plan is the query using an INNER JOIN. They have the same QueryHashPlan.
Did you notice the Hash Match operator of the top plan? It’s an INNER JOIN. But we used a CROSS JOIN in code. The WHERE clause in the first query (WHERE c.PersonID = P.BusinessEntityID) forced the results to have combinations only with equal keys. So, logically it’s an INNER JOIN.
Which one is better? It is your personal choice. I prefer INNER JOIN because the intent is to join 2 tables with equal keys. Using INNER JOIN makes it very clear. But that’s just me.
Conclusion
CROSS JOIN is good for giving you all possible value combinations. You’ve been warned, however, that it can cause some kind of data “explosion.” Use this JOIN carefully. Limit results as much as you can. Also, you can write a CROSS JOIN that is functionally equivalent to INNER JOIN.
This article ends the series about SQL JOINs. For future references, you can bookmark this article. Or, add it to your collection in a browser.
And don’t forget to share this article on your favorite social media ?
Tags: cross join, inner join, outer join, sql join, t-sql statements Last modified: September 18, 2021
