SQL Right Join

By: Kris Wenzel   |   Updated: March 6, 2022  
Works With: 

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:


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 


  • 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 


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,
FROM   person.Person
      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.

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