SQL Right Join Tutorial

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 

Not every person in the AdventureWorks database is an employee.  Let’s list every person, and some of their employment data, if it exists.  We’ll use a RIGHT JOIN to do so.   

Keep in mind it is a RIGHT JOIN, as the person table, which has every row we wish to return, is on the Right side of the JOIN operator. 

–List all People and any employment data, if it exists. –Use Outer Join to do so… SELECT P.FirstName , P.LastName , E.HireDate , E.JobTitle FROM HumanResources.Employee AS E RIGHT JOIN Person.Person AS P ON P.BusinessEntityID = E.BusinessEntityID; 
--List all People and any employment data, if it exists. 
--Use Outer Join to do so... 
SELECT P.FirstName 
 , P.LastName 
 , E.HireDate 
 , E.JobTitle 
FROM HumanResources.Employee AS E 
     RIGHT JOIN Person.Person AS P ON P.BusinessEntityID = E.BusinessEntityID;

Look at the results and notice there are NULL’s for some people.  This is because there are no corresponding entries in the Employee table.  The RIGHT JOIN is working as advertised, return all matches, and a NULL where none exist. 

Important Points: 

When working with RIGHT JOINS keep in mind your match from one table to another may match multiple rows.  Meaning, your result may have more rows in the result that you have in either table. 

When columns don’t match, the query the right row, and NULL for the left table columns.. 

Additional SQL Join Resources 

To learn more about the joins, check out these useful resources: 

{"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*
Sign Up
>