Right Outer Join in SQL Server

Right Outer Join in SQL Server with Examples

In this article, I am going to discuss the Right Outer Join in SQL Server with examples. Please read our previous article where we discussed the SQL Server Left Outer Join with an example. At the end of this article, you will understand the following pointers.

  1. What is Right Outer Join?
  2. How to implement Right Join in SQL Server?
  3. When do we need to use the Right JOIN?
  4. Example to Understand SQL Server Right Outer Join
  5. How to retrieve only the non-matching rows from the right table?
  6. Joining three Tables in SQL Server using Right Join
What is Right Outer Join?

The Right Outer Join is used to retrieve all the matching records from both the tables as well as all the non-matching records from the right-hand side table. In that case, the un-matching data will take the null value. The following diagram shows Right Outer Join in SQL Server.

Right Outer Join in SQL Server

How to implement Right Outer Join in SQL Server?

Please have a look at the following image which shows the syntax of Right Outer Join. Here, you can use either the Right Outer Join or Right JOIN keyword to perform Right Join. If you use only the Right JOIN keyword, then it is also going to perform Right Outer Join.

Right Outer Join Syntax in SQL Server

When do we need to use the Right JOIN in SQL Server?

If you want to select all the records from the Right side table including the records that have a null foreign key value then you need to use the SQL Server Right Outer Join. To simplify this we can say that if you want to retrieve all the matching rows from both the tables as well as the non-matching rows from the right side table then you need to use Right Join in SQL Server. 

Example to Understand SQL Server Right Outer Join.

Let us understand how to implement Right Outer Join in SQL Server with Examples. To understand Right Outer Join, we are going to use the following Company and Candidate tables.

Example to Understand SQL Server Right Outer Join

Please use the following SQL script to create and populate the Company and Candidate tables with some test data. Please note that here the CompanyId column of the Candidate Table is the foreign key column referencing the CompanyId column of the Company Table.

CREATE TABLE Company
(
    CompanyId TinyInt Identity Primary Key,
    CompanyName Nvarchar(50) NULL
)
GO
INSERT Company VALUES('DELL')
INSERT Company VALUES('HP')
INSERT Company VALUES('IBM')
INSERT Company VALUES('Microsoft')
GO

CREATE TABLE Candidate
(
    CandidateId tinyint identity primary key,
    FullName nvarchar(50) NULL,
    CompanyId tinyint REFERENCES Company(CompanyId)
)
GO
INSERT Candidate VALUES('Ron',1)
INSERT Candidate VALUES('Pete',2)
INSERT Candidate VALUES('Steve',3)
INSERT Candidate VALUES('Steve',NULL)
INSERT Candidate VALUES('Ravi',1)
INSERT Candidate VALUES('Raj',3)
INSERT Candidate VALUES('Kiran',NULL)
GO

Write a query, to retrieve CandidateId, FullName, CompanyId, and CompanyName from Company and Candidate tables. The output of the query should be as shown below.

SQL Server Right Join Example

Following is the Query which is an example of Right Outer Join that is joining the Company and Candidate tables and will give the results shown in the above image.

SELECT	Cand.CandidateId, 
 Cand.FullName, 
 Cand.CompanyId, 
 Comp.CompanyId, 
 Comp.CompanyName 
FROM	Candidate Cand 
RIGHT OUTER JOIN Company Comp 
ON Cand.CompanyId = Comp.CompanyId

If you look at the above, we got 6 rows. We got all the matching rows from both the tables as well as non-matching rows from the right side table i.e. the Company Table. Instead of using the Right Outer Join keyword, you can also use the Right Join keyword. This will also work as expected as Right Outer Join. That means the SQL Server Left Outer Join or Left Join means the same. The following example uses the Right Join keyword and we also get the same output as the previous example.

SELECT	Cand.CandidateId, 
 Cand.FullName, 
 Cand.CompanyId, 
 Comp.CompanyId, 
 Comp.CompanyName 
FROM	Candidate Cand 
RIGHT  JOIN Company Comp 
ON Cand.CompanyId = Comp.CompanyId

In short, we can say that Right Outer Join will return all the matching records from both the tables as well as all the non-matching records from the right-hand side table. 

How to retrieve only the non-matching rows from the right table?

What we want is, we want to retrieve only the non-matching records from the right table. For example, we want the following result sets when we join the Candidate and Company tables. If you further notice, here we want to retrieve those rows from the Candidate table whose does not have any reference in the Company table.

How to retrieve only the non-matching rows from the right table?

The following Right Join Query with the where clause does the same.

SELECT Cand.CandidateId, 
       Cand.FullName, 
       Cand.CompanyId, 
       Comp.CompanyId, 
       Comp.CompanyName 
FROM Candidate Cand 
Right JOIN Company Comp 
ON Cand.CompanyId = Comp.CompanyId
WHERE Cand.CompanyId IS NULL;
Joining three Tables in SQL Server using Right Join:

Now we will see how to Left Join three tables in SQL Server. Already we have the Company and Candidate tables. Let us introduce the third table i.e. the Address table. So, we are going to use the following Address table along with the Company and Candidate tables to perform joining operations between three tables.

Joining three Tables using Right Join

Please use the following SQL Script to create and populate the Address table with the required test data.

CREATE TABLE Address
(
    AddressId INT IDENTITY PRIMARY KEY,
    CandidateId tinyint REFERENCES Company(CompanyId),
    Country VARCHAR(50),
    State VARCHAR(50),
    City VARCHAR(50),
)
GO

INSERT INTO Address Values (1, 'India', 'Odisha', 'BBSR');
INSERT INTO Address Values (2, 'India', 'Maharashtra', 'Mumbai');
INSERT INTO Address Values (3, 'India', 'Maharashtra', 'Pune');
INSERT INTO Address Values (4, 'India', 'Odisha', 'Cuttack');
GO

The following is the syntax to join three tables in SQL Server.

Joining three Tables using Right Join Syntax

Example: Right Joining Candidate, Company, and Address tables

The following Right Join SQL Query Joining Candidate, Company, and Address tables.

SELECT Cand.CandidateId, 
       Cand.FullName, 
       Cand.CompanyId, 
       Comp.CompanyName, 
       Addr.Country, 
       Addr.State, 
       Addr.City 
FROM Candidate Cand 
RIGHT JOIN Company Comp 
ON Cand.CompanyId = Comp.CompanyId 
RIGHT JOIN Address Addr 
ON Addr.CandidateId = Cand.CandidateId;

When you execute the above Right Join SQL Query, you will get the following output. 

Right Outer Join in SQL Server with Examples

In the next article, I am going to discuss Full Outer Join in SQL Server with Examples. Here, in this article, I try to explain the SQL Server Right Outer Join with a real-time example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *