table joins, sql join

SQL Table Joins – RIGHT OUTER JOIN

What is the RIGHT OUTER JOIN

The RIGHT OUTER JOIN is nearly identical to the LEFT OUTER JOIN. The only difference between the two types of OUTER JOINs is the order in which SQL evaluates them. The LEFT OUTER JOIN returns all rows from the first table and the rows that match from second table. The RIGHT OUTER JOIN, on the other hand, returns all rows from the second table and then rows that match from the first table.

The Venn diagram below visualizes the RIGHT OUTER JOIN:

SQL RIGHT OUTER JOIN

Suppose that we wanted to find the FirstName and LastName of each employee listed in the HumanResources.Employee table. We also want to view their JobTitle. We could use the RIGHT OUTER JOIN to complete this request:

SELECT P.FirstName, P.LastName, E.JobTitle
FROM Person.Person P
RIGHT OUTER JOIN HumanResources.Employee E
ON E.BusinessEntityID = P.BusinessEntityID

The only rows that are returned are the 290 rows from HumanResources.Employee and then the FirstName and LastName columns from Person.Person if they exist for that BusinessEntityID.

Put simply, the RIGHT OUTER JOIN is nearly identical to the LEFT OUTER JOIN; the only difference is the order. The previous query is functionally identical to the query:

SELECT P.FirstName, P.LastName, E.JobTitle
FROM HumanResources.Employee E
LEFT OUTER JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID

To reiterate, the ONLY difference is the order in which the tables appear in the SELECT statement.

To practice using the RIGHT OUTER JOIN, head back to our post on the LEFT OUTER JOIN and try each of those problems again. This time, however, use the RIGHT OUTER JOIN instead of the LEFT OUTER JOIN to complete the query.

Posted in SQL Lessons, SQL Tips.

Leave a Reply

Your email address will not be published. Required fields are marked *