APPLY

APPLY is like a cross join in the type of result set that it produces, but usable only with functions. In a cross join, both inputs (left and right) are tables or views that already exist in the database, with a fixed definition. However, APPLY is used in scenarios where a join cannot be used. In APPLY, one of the inputs (the right) is not physically materialized in the database because its output is dependent on input parameters, such as in the case of a table-valued function (TVF).

For example, the AdventureWorks sample database has a SalesPerson table that contains the BusinessEntityID and SalesYTD columns, and a ufnGetContactInformation TVF that returns the FirstName, LastName, and JobTitle columns. The TVF creates a runtime abstraction for columns that exist in multiple underlying tables, like building a table on-the-fly. To write a query that returns the year-to-date (YTD) sales per sales person, together with their name and job title, a cross apply can be used to return all rows from the SalesPerson table, and each of those rows is combined with the rows coming from the ufnGetContactInformation TVF.

The query would look like the following code block:

SELECT SP.SalesYTD, P.FirstName, P.LastName, P.JobTitle
FROM Sales.SalesPerson AS SP
CROSS APPLY dbo.ufnGetContactInformation (SP.BusinessEntityID) AS P;

In the following screenshot, the results of the ufnGetContactInformation function are displayed alongside the SalesYTD column, just as if they came from another table using a simple inner or outer join:

However, the following query produces an error (ID 4104) because a join cannot be used directly with a TVF:

SELECT SP.SalesYTD, P.FirstName, P.LastName, P.JobTitle
FROM Sales.SalesPerson AS SP
CROSS JOIN dbo.ufnGetContactInformation (SP.BusinessEntityID) AS P;