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. 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. 

SQL Server Right Outer Join with real-time Examples
Let us understand SQL Server Right Outer Join with an example.

To understand the Right Outer Join, we are going to use the following Company and Candidate database tables.

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
Example:

The following is an example of Right Outer Join which will join the Candidate and Company Table.

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

When we execute the above query it will produce the below output.

The Right Outer Join in SQL Server

If you look at the above output, 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.

Note:

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. 

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 SQL Server 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. 

In the next article, I am going to discuss Full Outer Join in SQL Server with one real-time example. 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 *