A Join A Day – The Full Outer Join

Introduction

This is the seventh 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 full outer join combines the functionality of the left outer join and the right outer join. It always returns all rows of both row sources. If there are matching rows, the behavior is identical to an inner join. Additionally, each row on from either side that does not have a match on the other side will get returned with the other side's columns valued NULL.

FULL OUTER JOIN Example

I had a hard time to find a natural example for a full outer join with the data in AdventureWorks2008R2. For that reason I am just going to show the TblA – TblB example, using the same two tables that were introduced in the A Join A Day – The Inner Join post:

The full outer join query for this example is as follows:

Execution this query leads to this result:

Again, all rows that have a match are handled the same way as they would in an inner join. The rows with TblA_Val = 1 and TblB_Val = 6 show that rows without matches will be included independent of which side they are from. And, identically to the behavior seen for the other two outer joins, the non-matching side's columns are valued NULL.

FULL OUTER JOIN Syntax

The same comments made for the left outer join and the right outer join hold true for the full outer join as well. First, the OUTER keyword is optional. However, most people include it anyway. Second, make sure to place the complete join condition in the ON clause as you might inadvertently turn the query into an inner join otherwise. Check the : A Join A Day – The Left Outer Join post for details on this.

Like with the inner join, the order of row sources in a full outer join does not matter, the full outer join operator is fully symmetric.

FULL OUTER JOIN Operator

Looking at the execution plan of the example query above reveals a surprise:

This is an unexpectedly complex execution plan for a simple query like this. What happened here? As I had mentioned before, the loop join operator is not capable of handling a right outer join. That means that it is also not capable of handling a full outer join as part of the process is to include those unmatched right side rows. To deal with this restriction, SQL Server unions the output of a left outer join and a left anti semi join together. Hence the complex execution plan. A more detailed explanation for this behavior has to wait until we have explored the left anti semi join and looked closer at the three join algorithms available to SQL Server. Stay tuned.

To actually get to see a full outer join operator for above query we need to use a small trick: Let's create a clustered (non-unique) index on both tables:

Rerunning the example query will now result in this execution plan:

Adding those indexes made SQL Server chose a Merge Join (Full Outer Join) operator. The merge join, as we will see later, is actually the fastest of the three. However, it requires the inputs to be sorted identically. Adding the clustered indexes provided the data pre-sorted so that SQL Server was able to choose the merge join operator in this case.

Summary

The full outer join combines the left and right outer joins. For rows with a match on both sides it works like an inner join. Rows without a match will be returned the same way as they are for a left outer join or a right outer join. For this it does not matter which side the row came from.

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.