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)
RETURNS @sales TABLE (
    SalesOrderID    int NOT NULL,
    CustomerID    int NOT NULL,
    TotalDue    numeric(19, 4) NOT NULL,
    PRIMARY KEY CLUSTERED (SalesOrderID)
)
AS

BEGIN;

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

    RETURN;
END;

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
    s.SalesPersonID=p.BusinessEntityID
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,
       x.StoreContact
FROM Sales.Store AS s
INNER JOIN Person.Person AS p ON
    s.SalesPersonID=p.BusinessEntityID
CROSS APPLY (
    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.