Home » Blog » A Join A Day – The Cross Apply

A Join A Day – The Cross Apply

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

Introduction

This is the eighth 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 CROSS APPLY command was introduced with SQL Server 2005. Other than the join commands we have seen so far it is not part of the SQL standard. It's behavior is very similar to that of the CROSS JOIN command that we visited on day four (A Join A Day – The Cross Join). It takes a left and a right row source and joins them together without a join condition. So, these two queries are functionally identical:

[sql] SELECT
st.Name AS TerritoryName,
st.CountryRegionCode,
sr.Name AS SalesReasonName,
st.TerritoryID,
sr.SalesReasonID
FROM Sales.SalesTerritory AS st
CROSS JOIN Sales.SalesReason AS sr
ORDER BY TerritoryName, SalesReasonName;

SELECT
st.Name AS TerritoryName,
st.CountryRegionCode,
sr.Name AS SalesReasonName,
st.TerritoryID,
sr.SalesReasonID
FROM Sales.SalesTerritory AS st
CROSS APPLY Sales.SalesReason AS sr
ORDER BY TerritoryName, SalesReasonName;
[/sql]

The big difference is, that the right side of a cross apply can access attributes of the left. Utilizing that, the right side can return a different set of rows for every row on the left side. That is not possible with a normal join.

CROSS APPLY Example

The cross apply functionality enables us to really tap into the power of table valued functions. In AdventureWorks2008R2 we have one TVF defined: ufnGetContactInformation. It provides detailed information about a person in the database.
Using cross apply we can now retrieve a list of persons from the Person table and include that additional information from the function in the same result set:

[sql] SELECT p.BusinessEntityID, p.PersonType, p.EmailPromotion,
ci.FirstName, ci.LastName, ci.JobTitle, ci.BusinessEntityType
FROM Person.Person AS p
CROSS APPLY dbo.ufnGetContactInformation(p.BusinessEntityID) AS ci;
[/sql]

The result of this query looks like this:

As you can see, for every one of the 19683 persons the function returned a unique result that the Person row was joined to.

On a side note, the AdventureWorks2008R2.dbo.ufnGetContactInformation function is a really good example of how you do not want to write your T-SQL code. I am not going into more detail here. I am just going to ask you to look at the time it took to return not even twenty thousand rows. Check out my Performance Comparisons of different types of Functions for one of the many reasons why this function is so slow.

As I did for the joins, I am going to also show another more technical example that demonstrates the exact behavior of the cross apply operator for differing numbers of rows. For that example I am going to create a table dbo.XML_Data like this:

[sql] CREATE TABLE dbo.XML_data(Id INT, val XML);
INSERT INTO dbo.XML_data(id, val)
VALUES (1,'<a></a>'),
(2,'<a></a>'),
(3,'<a></a>'),
(4,'<a></a>');
[/sql]

Together with the XML data type SQL Server introduced the nodes() function. The nodes function takes an X-Query expression and returns all nodes out of a give XML value that match that expression. Each node found will be returned in a separate row. You can think of the nodes function as a powerful find function.

I am going to use this function to find all the tags in above table:

[sql] SELECT x.Id, x.val, n.b_node.value('@name', 'NVARCHAR(MAX)') b_node_name
FROM dbo.XML_data x
CROSS APPLY x.val.nodes('//b') n(b_node);
[/sql]

To make reading of the output a little easier, the query takes each node and uses the xml value function on it to only return the value of the name attribute of the node. The result is displayed below:

The XML value in row 1 contains a single tag, so the nodes function returns one row. The XML value in rows 2 and 3 contain two and three tags respectively, so as expected we see that many rows returned from the nodes function. Finally, the XML value in row 4 does not contain a tag at all. That means that the nodes function does not return a row. Therefore we don't see row 4 at all in the final result.

CROSS APPLY Syntax

Similar to the cross join syntax, the cross apply syntax is very straight forward: Table1 CORSS APPLY Table2. Remember that instead of a table you can use anything that produces rows, like a sub-query.

Other than with a cross join however, the order of the two row sources does matter. The second row source can reference attributes of the first and use them for example as parameters to a function call.

CROSS APPLY Operator

Another similarity to the cross join is that there is no cross apply operator. The cross apply functionality is instead handled by the Nested Loops (Inner Join) operator:

You can see that the first input to the Nested Loops operator is the scan of the Person table while the second input is the call to the ufnGetContactInformation function.

Summary

The CROSS APPLY is a SQL Server specific extension to the SQL standard. It functions similar to a cross apply with the big difference that the right side of the operator can reference attributes of the left side. That allows us to join each left side row to a unique right side result of for example a call to table valued function.

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.