Combinations with Cross Join

Have you ever wanted to get the combination of each row in one table with each row in a second table? You may know this as a cartesian product in mathematics. In SQL Server, we can accomplish this by using CROSS JOIN.

Like the Way You Look

Team jersey and jersey combinations are very important to any sports team. Just as you have that favorite shirt in your closet or maybe even a good luck shirt that you break out for special occasions, so do many teams. I prefer the Alt Blue look for the Columbus Blue Jackets but what if I wanted to see each combination of jerseys and pants? Let’s take a look at how we can accomplish that in SQL Server.

We’ll create a couple tables to hold our apparel and colors:

CREATE TABLE Jersey
(
	J_ID INT IDENTITY(1,1),
	JerseyColor VARCHAR(20)
);
GO

CREATE TABLE Pants
(
	P_ID INT IDENTITY(1,1),
	PantsColor VARCHAR(20)
);
GO

And insert what could be worn in the 2021 season:

INSERT INTO Jersey
VALUES ('Blue'),('Alt Blue'),('Retro Red'),('White');

INSERT INTO Pants
VALUES ('Alt Blue'),('Red'),('Retro Blue');

One of Everything

If want to see every possible combination of jersey and pants, we can use CROSS JOIN:

SELECT J.JerseyColor, P.PantsColor
FROM Jersey J
CROSS JOIN Pants P;
GO

If we wanted to get more specific and only see combinations where the pants color is ‘Red’ we can add a WHERE clause:

SELECT J.JerseyColor, P.PantsColor
FROM Jersey J
CROSS JOIN Pants P
WHERE P.PantsColor = 'Red';
GO

You may have seen a CROSS JOIN before without realizing it due to it not needing to be explicitly stated in a query. Instead of using CROSS JOIN like in the previous query, we can separate the tables by a comma and get the same result:

SELECT J.JerseyColor, P.PantsColor
FROM Jersey J,Pants P
WHERE P.PantsColor = 'Red';
GO

The More the Merrier?

We can have multiple CROSS JOINS in a query. If we wanted to include socks to our choices:

CREATE TABLE Socks
(
	S_ID INT IDENTITY(1,1),
	SocksColor VARCHAR(20)
);
GO

INSERT INTO Socks
VALUES ('Blue'),('Alt Blue'),('Retro Red'),('White')

We can update our query to get more combinations:

SELECT J.JerseyColor, P.PantsColor, S.SocksColor
FROM Jersey J
CROSS JOIN Pants P
CROSS JOIN Socks S;
GO

Or narrowing down to red pants again without specifying CROSS JOIN in the query:

SELECT J.JerseyColor, P.PantsColor, S.SocksColor
FROM Jersey J,Pants P, Socks S
WHERE P.PantsColor = 'Red';
GO

Note that it doesn’t take many rows in each table to create a large total number of rows when using CROSS JOIN. Be mindful of how much data you’re going to return and what the cost will be.

Thanks for reading!