APPLY tutorial

The APPLY operator can be a bit confusing, but if you master how it works, you can do great things with it. It is similar to a JOIN (hence the confusion), but differs in one key area: The JOIN defines a relation between two datasets using the ON keyword, whereas APPLY does not.

Table valued functions

Let’s say we have a table valued function (in the AdventureWorks database) that returns sales for an individual sales person, and it looks like this:

CREATE FUNCTION Sales.fn_SalesByPerson(@SalesPersonID int)
    SalesOrderID    int NOT NULL,
    CustomerID    int NOT NULL,
    TotalDue    numeric(19, 4) NOT NULL,


    INSERT INTO @sales (SalesOrderID, CustomerID, TotalDue)
    SELECT SalesOrderID, CustomerID, TotalDue
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID=@SalesPersonID;


As you know, you can use a table value function like any table or view in a query, but in this case, there’s no SalesPersonID column in the output – instead, it’s a parameter to the function (@SalesPersonID int). This means that you can’t JOIN your employee table to the output of the function. So if you want to list all sales persons along with their respective sales, you might use a cursor to loop through each sales person and run the function once for each sales person.

But if you’re like me, you hate cursors. And indeed, the APPLY operator is a much better solution. Here’s how: APPLY does not use a JOIN-style syntax with the ON keyword. Instead, you can supply arguments from your other columns are parameters to a function.

SELECT p.FirstName, p.LastName, SUM(s.TotalDue) AS TotalSales
FROM Person.Person AS p
OUTER APPLY Sales.fn_SalesByPerson(p.BusinessEntityID) AS s
WHERE p.PersonType='SP'   -- Only sales persons
GROUP BY p.FirstName, p.LastName;

In the example, I’m using OUTER APPLY, but you could just as well use CROSS APPLY. This is more or less the APPLY equivalent of using an OUTER JOIN or INNER JOIN. A CROSS APPLY will restrict the result set to just those rows where “s” actually returns data, whereas OUTER APPLY will show you all the rows from “p”, regardless of wether the function returns any data.

XML parsing

APPLY can be used to “join” a recordset derived from an XML structure to a tabular set, but this is the subject of a different post.

Cleaning up your code

But there’s another way you can use the APPLY operator – to clean up your code and make it a lot more readable. If you’ve done any T-SQL development, you’ll recognize what’s happening here:

SELECT MIN(s.BusinessEntityID) AS StoreID,
       p.FirstName+' '+p.LastName+', '+s.[Name] AS StoreContact
FROM Sales.Store AS s
INNER JOIN Person.Person AS p ON
GROUP BY p.FirstName+' '+p.LastName+', '+s.[Name]
ORDER BY p.FirstName+' '+p.LastName+', '+s.[Name];

Instead, you can CROSS APPLY the entire expression, like this:

SELECT MIN(s.BusinessEntityID) AS StoreID,
FROM Sales.Store AS s
INNER JOIN Person.Person AS p ON
    SELECT p.FirstName+' '+p.LastName+', '+s.[Name] AS StoreContact) AS x
GROUP BY x.StoreContact
ORDER BY x.StoreContact;

So instead of CROSS APPLYing a table value function, we’re building the recordset directly in the “x” subquery, then we can reference all the columns in “x” without having to repeat the code over and over. Since “x” is just like an ordinary subquery, you can add as many columns to it as you like.

Credits to SQL guru Håkan Winther at SQL Service for showing me this.

7 thoughts on “APPLY tutorial

  1. Pingback: Parsing XML data | Sunday morning T-SQL

  2. Pingback: An introduction to table types | Sunday morning T-SQL

  3. Pingback: Aggregate string concatenations « Sunday morning T-SQL

  4. Pingback: “Unpivot” using CROSS APPLY « Sunday morning T-SQL

  5. Pingback: Finding primary key candidates |

  6. Pingback: Fun with random names |

  7. Pingback: An alternative to data masking |

Let me hear your thoughts!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.