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