Knowing how JOIN operations work in SQL Server is extremely important. I would say most of the queries you will ever right in your career as a database professional will involve some kind of JOIN operation.
The INNER and LEFT JOINS are the most common, but it’s also important to understand how the lesser-used CROSS JOIN works. You never know when it will come in handy!
In this very brief tutorial, we’ll discuss everything you need to know about the CROSS JOIN operation in SQL Server. Here’s what we’ll discuss:
- What is the CROSS JOIN operation?
- An example of a CROSS JOIN
- Tips and tricks
Also, everything discussed in this tutorial can also be found in the following FREE Ebook:
FREE Ebook on SQL Server JOIN Operations!
This guide contains all the key points you need to understand about the different JOIN operations in Microsoft SQL Server. It will definitely be a great resource to keep and reference throughout your career as a data professional. Download it today!
1. What is the CROSS JOIN operation?
A CROSS JOIN will match all rows from table A with each row from table B. The final result set is basically a Cartesian product of the two tables.
If you don’t know what a Cartesian product is, don’t sweat it. It’s a mathematical term that basically says all rows from set A get matched with all rows from set B, which is exactly what the CROSS JOIN does.
Let’s take a look at an example.
2. An example of a CROSS JOIN
Take a look at the following Students and CollegeMajors tables:
Here are the CREATE and INSERT statements if you want to follow along:
--Students
CREATE TABLE Students
(
StudentID INT IDENTITY(100,5) PRIMARY KEY,
FirstName VARCHAR(15),
LastName VARCHAR(15),
)
INSERT INTO Students (FirstName, LastName)
VALUES
('Jeff','Alanzo'),
('Amy','Bell'),
('Marla','Bridge'),
('Andrew','Bucks')
--Majors
CREATE TABLE CollegeMajors
(
MajorID INT IDENTITY PRIMARY KEY,
MajorName VARCHAR(30) NOT NULL
)
INSERT INTO CollegeMajors (MajorName)
VALUES
('Computer Science'),
('Information Technology'),
('Graphic Design'),
('Computer Criminology')
What if we wanted to know all possible combinations of students and majors? That is, for each student, we want to know all possible majors they can study. We can use a CROSS JOIN to see that very easily:
For each student, we can see all possible majors they can study. Pretty simple!
We can flip the tables around and view the final result set differently:
It’s the same result set, just ordered differently.
Finally, we can certainly filter this result set if we wanted to. Let’s just see the Cartesian product for our student Amy Bell:
There’s nothin’ to it!
3. Tips and tricks
Here are just a few tips and tricks you should know:
Tip # 1: You can easily calculate the number of rows that will be in your final result set
The number of rows in your final result set can be calculated using this simple formula:
(number of rows in first result set) X (number of rows in second result set) = Number of rows in final result set
Think about our CROSS JOIN examples where there was not a WHERE clause. Since our first result set had 4 rows, and our second result set also had 4 rows, the number of rows in our final result set after the CROSS JOIN was 16.
If you had 10 rows in set A and 12 rows in set B, the final result set would have 120 rows.
You get the idea.
Tip # 2: You can CROSS JOIN on more than just two tables
In my examples, we performed a CROSS JOIN on only two tables. Understand you can do a CROSS JOIN on as many tables as you would like.
The number of rows in the final result set will increase, of course. If you join on 3 tables, for example, the final result set will have (set 1) x (set 2) x (set 3) number of rows.
To CROSS JOIN on a third table, you would just add another CROSS JOIN to the query. Like this:
Tip # 3: There is no ON clause
The INNER JOIN and LEFT JOIN both require an ON clause. The CROSS JOIN, however, does not, nor does it support one.
Next Steps:
Leave a comment if you found this tutorial helpful!
Make sure you download your FREE Ebook:
FREE Ebook on SQL Server JOIN Operations!
This guide contains all the key points you need to understand about the different JOIN operations in Microsoft SQL Server. It will definitely be a great resource to keep and reference throughout your career as a data professional. Download it today!
Do you know the answer to these common JOIN questions?:
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
