The JOIN operation in SQL Server is probably one of the most widely used querying tools available to us in Microsoft SQL Server. It is very important that you how JOINs work and the differences between the different types of JOINs.
One type of JOIN is the RIGHT JOIN. In this very brief tutorial, we’ll discuss what the RIGHT JOIN is and talk about how it can be useful when querying data in our databases.
The RIGHT JOIN operation is discussed 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!
We’ll discuss these topics in this tutorial:
- What is a JOIN anyway?
- How the RIGHT JOIN works
- The thing about the RIGHT JOIN operation
- Tips and tricks
Let’s get into it.
1. What is a JOIN anyway?
In order to understand how the RIGHT JOIN works, you really need to understand how the regular INNER JOIN operation works first. It will be much easier for you to understand the RIGHT JOIN if you already understand the INNER JOIN.
The INNER JOIN basically returns rows where values in our columns specified in the ON clause have a match in both tables.
Take a look at the full beginner-friendly tutorial on the INNER JOIN operation to learn more:
INNER JOIN in SQL Server: The Ultimate Guide for Beginners
Setting up some data
We need to set up some quick data to demonstrate. Take a look at the following Students and CollegeMajors tables:
Here are the CREATE TABLE and INSERT statements so you can create this data in your environment:
CREATE TABLE CollegeMajors
(
MajorID INT IDENTITY PRIMARY KEY,
MajorName VARCHAR(30) NOT NULL
)
INSERT INTO CollegeMajors (MajorName)
VALUES
('Computer Science'),
('Information Technology'),
('Snowman Engineering'),
('Graphic Design'),
('Computer Criminology'),
('Fitted Sheet Folding'),
('Left Handed Knitting'),
('Biology'),
('Chemistry'),
('Backflip Theory')
CREATE TABLE Students
(
StudentID INT IDENTITY(100,5) PRIMARY KEY,
FirstName VARCHAR(15),
LastName VARCHAR(15),
DeclaredMajor INT NOT NULL
)
INSERT INTO Students (FirstName, LastName, DeclaredMajor)
VALUES
('Jeff','Alanzo', 2),
('Amy','Bell', 1),
('Marla','Bridge', 2),
('Andrew','Bucks', 8),
('Sara','Carlton', 5),
('Kimberly','Cutter', 8),
('Jack','Faun', 4),
('Sam','Green', 1),
('Max','Mills', 9)
Notice the Students table has a column called DeclaredMajor. This column is basically a foreign key link to the CollegeMajors table. This is how we know what major a student has decided to pursue.
In a single query, if we wanted to list out all our students and the name of the major they have declared, that would be an easy INNER JOIN:
Notice there are some majors that are simply not referenced in this result set. For example, there are no students who have declared “Left Handed Knitting” as their major.
Strange, I know.
2. How the RIGHT JOIN works
What if we wanted to see all majors in our final result set, regardless of if there are any students who have declared them? Maybe we want to see which majors are actively being pursued and which majors are not being pursued, all in a single result set.
This is precisely where the RIGHT JOIN can come in handy.
There is a very helpful diagram you’ll see when it comes to the RIGHT JOIN operation. Here it is:
So again, the RIGHT JOIN will pull everything from the right table. From the left table, we only pull rows where there is a match between the two tables.
Going back to our example, the LEFT table is the table in the FROM clause, which is the Students table. The RIGHT table is of course the table in the RIGHT JOIN clause, which is the CollegeMajors table. The RIGHT JOIN operation will pull all content from the CollegeMajors table, and only content from the Students table where their DeclaredMajor value has a match in the CollegeMajors.MajorID column.
If we think about it, all the DeclaredMajor values ought to have a match in the CollegeMajors.MajorID column. When students declare a major, they can only pick from the list of majors created by the college. It would be strange to have a student who has declared a major that doesn’t exist!
An example of a RIGHT JOIN
Without further ado, here is what a RIGHT JOIN query looks like:
Let’s talk about what we’re seeing here. It’s easiest if we go down the list of MajorNames. Notice this is the only column pulled from our CollegeMajors table.
The first major is “Computer Science“. For this major, we display the Student details for everyone who has declared this major. Looks like that’s Amy Bell and Sam Green. Good for them.
The next major is “Information Technology“. Again, we display information for the students who have declared this major. Looks like it’s Jeff Alanzo and Marla Bridge.
But what about the next major, “Snowman Engineering“? Remember folks, the RIGHT JOIN will pull all rows from the right table regardless of if there is a match in the left table. This major is exactly one of those situations where the row exists in the right table and does not have any matches in the left table.
Since we know there are no matching rows in the left table for this major, what is SQL Server supposed to use as the values for the left columns in our query? Those columns are the FirstName, LastName, and DeclaredMajor columns.
Well, NULL represents the absence of information, which is exactly what we have here. For majors where there are no students, NULL is used in the Student columns.
Using IS NULL
What if we wanted to see only the majors that have not been declared by anyone? Maybe our college is looking to downsize and we want to know what majors are a waste of time and money!
Well, we could run the same query from earlier, but add a filter to pull rows where the DeclaredMajor column (or any column from the left table) is specifically NULL.
We already know that if a major does not have any students, the Student columns (FirstName, LastName, DeclaredMajor) will all be NULL. We can use that as a filter. Check it out:
Nice. Now we can very easily tell that those 4 majors are not linked to any students.
One thing I want to point out is that you don’t need to have any left columns in your column list. This query works just fine:
This is much cleaner in my opinion. Now we’re not seeing a bunch of ugly NULLs.
By the way, I’m not surprised that nobody has declared Fitted Sheet Folding as their major. That degree is super hard. I found a great tutorial on YouTube that goes over how it’s done. Watch this video and I’ll go ahead and give you an Honorary Degree in Fitted Sheet Folding:
3. The thing about the RIGHT JOIN operation
Ok, here’s the thing about the RIGHT JOIN: You don’t see it that much.
If you think about it, you could achieve the same result by using a LEFT JOIN and just flipping the tables around.
Here’s our RIGHT JOIN again:
And here’s the same query written as a LEFT JOIN with the tables flipped around:
It’s the same thing!
So if you need to write your query as a RIGHT JOIN, that’s fine. But understand most of the time it’s not necessary. You could just write it as a LEFT JOIN and flip the tables!
4. Tips and tricks
Here is a list of some simple tips and tricks you should know about the RIGHT JOIN operation:
Tip # 1: RIGHT JOIN and RIGHT OUTER JOIN are the same thing
The full name of a RIGHT JOIN is technically RIGHT OUTER JOIN. If you wanted to be explicit, you could include the word ‘OUTER’ in your query, but understand it’s not necessary. The word ‘OUTER’ is optional.
Tip # 2: As discussed earlier, RIGHT JOIN is less common than LEFT JOIN
We have already discussed how RIGHT JOIN queries can be written as LEFT JOINs usually. This is why you don’t see then often in the wild. But at least now you understand how they work.
Next Steps:
Leave a comment if you found this tutorial helpful!
Don’t forget to 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 how to write an UPDATE statement with a JOIN? Check out the tutorial to learn how!
If you need a full rundown on the first type of JOIN, which is the INNER JOIN, take a look at the full beginner-friendly tutorial here:
INNER JOIN in SQL Server: The Ultimate Guide for Beginners
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!
