November 8, 2021

The SQL RIGHT JOIN Includes all rows from the Right table and those that match from the left.  For that doesn’t match from the right, NULL is returned in the corresponding columns. 

This is the key difference between a SQL RIGHT JOIN and INNER JOIN.  Where an inner join only returns rows matching the join condition, in a Right outer join, it’s guaranteed all the Right tables rows are included in the result. 

Checkout this illustration:

SQL RIGHT JOIN example

Check out the results for Fred and notice there is no matching Product, it is NULL.  Also, the isn’t included in the result. 

Another name for  RIGHT JOIN is  RIGHT OUTER JOIN. 

The RIGHT JOIN general form is: 

SELECT table1.column1, table2.column2, … 
FROM   table1 
RIGHT JOIN table2 ON table1.commonColumn = table2.commonColumn 

Notes: 

  • In this example table2 is the Right table and table1 the left. 
  • If there is no match between the commonColumn values, table1.column1 returns NULL 

SQL RIGHT JOIN Example 

Let’s take another look at the diagram, but this time we are doing a SQL right join.  As you may have guessed, there isn’t too much difference in the SQL statement between a left join and a SQL right join.  The basic syntax for a right join is:

SELECT columnlist
FROM   table
RIGHT OUTER JOIN othertable ON join condition

Below is our sample query written as a right outer join:

SELECT person.Person.BusinessEntityID,
       Person.Person.LastName,
       HumanResources.Employee.NationalIDNumber,
       HumanResources.Employee.JobTitle
FROM   person.Person
RIGHT OUTER JOIN
      HumanResources.Employee
      ON person.BusinessEntityID = Employee.BusinessEntityID

The key difference is that now we are going to return all records from the Employee table, which is the table to the right of the join keyword.  If a matching Employee record isn’t found, then NULL will be returned for BusinessEntityID and LastName.

Here are the results from the query.

Right Outer Join REsults

I scrolled through all the results and was surprised to not see any null values.

Do you know why?

The answer lies in the data model.  There is a 0..1 to 1 relationship between Employee and Person.  This means that for every Employee there is one Person.  Given this, for the right join there won’t exist any non-matching rows.  With this type of relationship you could have also used an inner join.

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>