Home » Blog » A Join A Day – The Right Outer Join

A Join A Day – The Right Outer Join

2012-12-06 - A Join A Day, Fundamentals, General, Series, T-SQL Statements

Introduction

This is the sixth post in my A Join A Day series about SQL Server Joins. Make sure to let me know how I am doing or ask your burning join related questions by leaving a comment below.

The RIGHT OUTER JOIN is very similar to the LEFT OUTER JOIN. As we have seen in the last post, the left outer join will include all rows of the first or left row source and only matching rows of the second or right row source. The right outer join does the same but with switched roles: It returns only matching rows of the first row source but includes all rows of the second row source.

RIGHT OUTER JOIN Example

Let's look at the same query that we used in the previous post but switch the order in which the tables are mentioned. If we now want the same results we need to change the LEFT OUTER JOIN to a RIGHT OUTER JOIN like this:

[sql] SELECT at.Name AS AddressTypeName, bea.BusinessEntityID, bea.AddressID
FROM Person.BusinessEntityAddress AS bea
RIGHT OUTER JOIN Person.AddressType AS at
ON at.AddressTypeID = bea.AddressTypeID
ORDER BY at.AddressTypeID, bea.BusinessEntityID;
[/sql]

The result is the same as it was in yesterday's example:

So, TableA LEFT OUTER JOIN TableB will produce the same results as TableB RIGHT OUTER JOIN TableA assuming the same condition was supplied.

Just for completeness we are going to use the TblA - TblB example again. The values in both tables are the same as before. They are shown below:

The query this time is joining TblA with a right outer join to TblB:

[sql] SELECT *
FROM dbo.TblA AS A
RIGHT OUTER JOIN dbo.TblB AS B
ON A.TblA_Val = B.TblB_Val;
[/sql]

This query returns the following result:

Again, rows for which matches on both sides exists are treated exactly like they would be treated in an inner join query. This time however rows from the right side that do not have a match on the left side are returned additionally. In such a case the left side columns are all valued NULL. The row for TblB_Val = 6 in above image shows an example of this.

RIGHT OUTER JOIN Syntax

As is the case with the LEFT JOIN, the OUTER keyword is optional and most people just write RIGHT JOIN. The same cautionary measures have to be taken to get the ON clause right to not inadvertently turn the outer join into an inner join. For more details check out yesterday's post: A Join A Day – The Left Outer Join

RIGHT OUTER JOIN Operator

The interesting thing about the right outer join is that SQL Server usually tries to avoid it. In fact, the execution plan of above example query is exactly the same as the one the left outer join query produced:

The operator is the same Nested Loops (Left Outer Join) and the order of the two tables in the execution plan is the same as well. That shows that SQL Server assumes that it is better to switch the order of the tables and execute a left outer join than to leave the order alone and execute a right outer join.

However, there actually is such a thing as a right outer join operator. It just requires special circumstances for you to come across one.

To show you the right outer join operator I had to create two new tables in the AdventureWorks2008R2 database:

[sql] CREATE TABLE dbo.Parent(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, val INT DEFAULT CHECKSUM(NEWID()));
CREATE TABLE dbo.Child(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,ParentId INT REFERENCES dbo.Parent(Id) DEFAULT (ABS(CHECKSUM(NEWID())%10)+1), val INT DEFAULT CHECKSUM(NEWID()));
GO
MERGE dbo.Parent
USING (SELECT TOP(1000000) 1 X FROM sys.columns x1 CROSS JOIN sys.columns x2 CROSS JOIN sys.columns x3 CROSS JOIN sys.columns x4)X
ON 1=0
WHEN NOT MATCHED THEN INSERT DEFAULT VALUES;
GO
MERGE dbo.Child
USING (SELECT TOP(1000) 1 X FROM sys.columns x1 CROSS JOIN sys.columns x2 CROSS JOIN sys.columns x3 CROSS JOIN sys.columns x4)X
ON 1=0
WHEN NOT MATCHED THEN INSERT DEFAULT VALUES;
[/sql]

The tables are dbo.Parent and dbo.Child with a foreign key relationship defined between them. After executing the above coed, there are one million records in the parent table. The Child table contains one thousand records that all reference one of the first ten Parent records only. The MERGE statement in above script allows me to quickly insert x number of rows using only default values.

Now let's try to run this query:

[sql] SELECT *
FROM dbo.Child AS c
RIGHT OUTER JOIN dbo.Parent AS p
ON c.ParentId = p.Id
ORDER BY c.val,p.val;
[/sql]

It asks for all Parent records and only matching Child records. The output looks like this:

And this time we actually get a right outer join operator in the execution plan:

The yellow double arrows indicate that this query was executed utilizing parallelism. The algorithm used this time is a Hash Match (Right Outer Join). For an explanation of the differences between the algorithms I will ask you to wait a few more days. At that time we will also discover that the Nested Loops operator is actually not able to process a right outer join. Make sure you don't miss that post.

Summary

A right outer join and a left outer join are basically the same operation with switched roles of the two tables. Usually SQL Server will rearrange the order of the tables and use a left outer join operator, even if a RIGHT OUTER JOIN command was used in the query. In some cases however a right outer join operator will be used.

A Join A Day

This post is part of my December 2012 "A Join A Day" blog post series. You can find the table of contents with all posts published so far in the introductory post: A Join A Day – Introduction. Check back there frequently throughout the month.