SQL Server Right Outer Join

SQL Server Right Outer Join with real-time Examples

In this article, I am going to discuss the SQL Server Right Outer Join with one real-time example. Please read our previous article where we discussed the SQL Server Left Outer Join with an example. The SQL Server 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 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 SQL Server 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 to 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 SQL Server 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 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 will discuss Full Outer Join in SQL Server with one real-time example.

SUMMARY

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 *