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!

2 thoughts on “Combinations with Cross Join”

  1. Virtually everything you posted is fundamentally wrong. By definition, not as a personal option, the table must have a key. The identity table property makes absolutely no sense; if I pick up a pair of pants, I really find an identity hanging on? Color of the pants and the color of the Jersey are totally independent and unrelated? A 20 character color name? Here’s what you probably should have posted. You really have only one Jersey as you shown by your singular table name?

    CREATE TABLE Jerseys
    (jersey_color VARCHAR(20) NOT NULL PRIMARY KEY
    REFERENCES Garment_Colors (garment_color));

    CREATE TABLE Pants
    (pants_colors VARCHAR(20) NOT NULL PRIMARY KEY
    REFERENCES Garment_Colors (garment_color));

    See how the tables have to have primary key? See how the reference clause ties altogether into an RDBMS. Instead of what you had; two decks of punch cards in sorted order.

    CREATE TABLE Garment_Colors
    (garment_color VARCHAR(20) NOT NULL PRIMARY KEY);

    INSERT INTO Garment_Colors
    VALUES (‘Blue’), (‘Alt Blue’), (‘Retro Red’), (‘White’);

    Liked by 1 person

Leave a Reply to Joe F Celko Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s