A Join A Day – The Outer Apply

Introduction

This is the ninth 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 OUTER APPLY command is an extension to the CROSS APPLY command (see A Join A Day – The Cross Apply) that allows us – just like a left outer join – to include non-matched rows from the left side in the result of a query.

OUTER APPLY Example

The example I used in yesterday's post called the ufnGetContactInformation for each row in the person table. A person in the AdventureWorks2008R2 database is considered a business entity and there is a BusinessEntity table that includes all persons as well as a few other entities. The above function when used on a non-person entity does not return a row.

Let's assume we want to use the BusinessEntity table in a query and return the contact information for all entities that are indeed a person. But we also want a row for each non-person in the result. That is just the functionality that the OUTER APPLY statement can provide:

The result of the above query includes all 19683 persons as well as all other entities resulting in a total of 20777 returned rows.

The right side columns of all rows for which the right side did not actually return a row, are valued NULL. This is the same behavior that we have seen with the left outer join (see A Join A Day – The Left Outer Join).

Just for completeness let's look at yesterday's second example again as well, replacing the CROSS APPLY with an OUTER APPLY. Remember, the XML_data table contains these rows:

The cross apply query did not return any row for Id = 4. So let's see what happens when using an outer apply instead.

This query returns the same 6 rows for Id 1, 2 and 3 as yesterday's cross apply query. However, as we expected, it returns one additional row for Id = 4.

OUTER APPLY Syntax

The only syntax difference between an outer apply and a cross apply statement is the OUTER keyword. Everything else stays the same. That means that the order of the two row sources is important for the outer apply too, as the second row source can reference attributes of the first row source.

Other than with outer joins, in an outer apply the OUTER keyword is not optional. So the syntax is always Table1 OUTER APPLY Table2 where Table1 and Table2 can be tables or other valid row sources.

OUTER APPLY Operator

The outer apply is handled by the nested loops operator and like the cross apply does not have its own. However, as you might have guessed, it is not the Nested Loops (Inner Join) operator that is responsible for cross apply queries. Instead it is the Nested Loops (Left Outer Join) operator:

Noteworthy is that it is always a left outer join operator and not a full or even right outer join. But that makes sense, as the right side is always executed in the context of the current left side row. That means that there cannot be a row on the right side without a matching current row on the left side.

Summary

Outer apply and cross apply are very similar in that they both allow the right side to reference attributes of the left side. They both query the right side for each left side row. Each left side row is joined with all rows resulting from its execution of the right side. The only difference is that the outer apply also returns left side rows for which the right side row source returned an empty result set.

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.