Apply Operator

With the introduction of SQL
Server 2005 T-SQL was expanded to include new functionality. The “APPLY”
operation was one of the new T-SQL enhancements. This article will discuss the
APPLY operator and provide a few examples of how it can be used.

What is the APPLY Operator?

The APPLY operator allows
T-SQL to invoke a table value function for each row in an outer record set. For
each row in the outer record set, the table value function is evaluated to
determine if any rows are returned. If any rows are returned from the table
value function then those rows are joined using a UNION ALL operation to the
row in the outer record set. The columns returned from the table value function
are merged with the columns of the row in the outer record set to produce the
final set of columns returned. The APPLY operator lets you easily join columns
from a table value function with the columns of the outer record set.

Syntax for the Apply Operator

The APPLY operator is used
in the FROM clause between a left table source and a right table source. The
APPLY operator comes in the following two formats: “CROSS APPLY,” or “OUTER
APPLY.” Below is the syntax for using the APPLY operator:

SELECT <column list> FROM
left_table_source { CROSS | OUTER } APPLY right_table_source

The left_table_source is
also known as the outer record set. Each row in this outer record set is
evaluated against the right_table_source. The right_table_source, a table
value function, is evaluated for each row in the outer record set, using one or
more columns from the row in the outer record set. The left_table_source can
also be a table value function but it cannot contain arguments that come from
the right_table_source. In order to use the APPLY operator your SQL Server
2005 database compatibility needs to be set to 90.

The “CROSS APPLY” format of
the APPLY operator only returns rows from the left_table_source when one or
more records are returned from the table value function in the right_table_source.
The “OUTER APPLY” format specifies that you want to return all the rows from
the left_table_source even if a specific row does not produce any corresponding
rows from the right table value function.

What is a Table Value Function?

A table value function can
take two forms. This first form, is the one most people think of, which is a
user or system defined function that returns a table data type. But a table
value function can also be an inline table value function. An inline table
value function has no function body, it is just the result set of a parameter
driven SELECT statement

Examples of Using CROSS APPLY

This first example will use
the CROSS APPLY operator to merge rows from a table value function with rows
from a table in the AdventureWorks databases. Here is the code for the
function I will be using in this example:


CREATE FUNCTION Sales.ufn_QtyByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
SELECT P.ProductID, P.Name, SUM(SD.OrderQty) AS ‘Quantity’
FROM Production.Product AS P
JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
WHERE SH.CustomerID = @storeid
GROUP BY P.ProductID, P.Name
)

This function will display
the quantity sold for each product for a given store. I will use this function
and the APPLY operator to display the top two selling items by store for the
first 5 customers. To accomplish this I use the CROSS APPLY operator in code below:


select S.Name,QbyS.Name ProductName,QbyS.Quantity from
(select top(5) Name, CustomerId from AdventureWorks.Sales.Store) S
CROSS APPLY
(select top(2) Name, Quantity from AdventureWorks.Sales.ufn_QtyByStore(S.CustomerId)
order by Quantity desc) QbyS

Here are the results I get
when I run this query against my AdventureWorks databases:


Name ProductName Quantity
—————————– —————————- ———–
A Bike Store Road-150 Red, 56 8
A Bike Store Road-450 Red, 48 7
Progressive Sports Full-Finger Gloves, L 22
Progressive Sports Women’s Mountain Shorts, 19
Advanced Bike Components AWC Logo Cap 86
Advanced Bike Components Long-Sleeve Logo Jersey, L 75
Modular Cycle Systems Racing Socks, L 48
Modular Cycle Systems Road-250 Black, 44 38
Metropolitan Sports Supply Racing Socks, L 37
Metropolitan Sports Supply Road-750 Black, 48 23

The CROSS APPLY operator
allowed me to take the CustomerId from each of the top 5 Store records in the
left table and use that as a parameter to the “unf_QtyByStore” function. For
each of the Store records selected (top 5 stores) the CROSS APPLY operator will
merge the columns from the records returned from the “unf_QtyByStore” table
value function with the individual store records. Since the SELECT statement
that references the “unf_QtyByStore” function returns only the top two products
sold by store, you see only two product names displayed for each store, along
with the quantity sold for that product.

For this second example, I
will demonstrate how the CROSS APPLY operator can be used with an inline table
value function. The code below generates some parent/children test data records
then uses the CROSS APPLY operator to display the two oldest children for each
parent:


set nocount on
create table Person(PersonID int, LastName char(20), FirstName char(20), BirthDate datetime, ParentID int)
insert into Person values(1,’Smith’,’Dick’,’1925-12-27′,0)
insert into Person values(2,’Smith’,’Doris’,’1955-10-19′,1)
insert into Person values(3,’Smith’,’John’,’1958-02-26′,1)
insert into Person values(2,’Smith’,’Jan’,’1960-12-19′,1)
insert into Person values(3,’Smith’,’Joshua’,’1962-11-24′,1)
insert into Person values(4,’Francis’,’Duke’,’1937-01-22′,0)
insert into Person values(5,’Jones’,’Mary’,’1939-08-08′,0)
insert into Person values(6,’Jones’,’Danielle’,’1962-03-17′,5)
select rtrim(P1.LastName) + ‘, ‘ + rtrim(P1.FirstName) As Parent,
rtrim(P2.LastName) + ‘, ‘ + P2.FirstName As Child,
convert(char(10),P2.BirthDate,102) as BirthDate
from Person P1
cross apply (select top (2) * from Person
where ParentID = P1.PersonID order by BirthDate) P2
drop table Person

When I run the above code on
my SQL Server 2005 instance, I get the following results:


Parent Child BirthDate
——————————- —————————- ———-
Smith, Dick Smith, Doris 1955.10.19
Smith, Dick Smith, John 1958.02.26
Jones, Mary Jones, Danielle 1962.03.17

My CROSS APPLY query above
used the Person table as the left table source and I created an inline table
value function using the Person table for the right table source. I
constrained my inline table value function to only return the top two children
for a given parent, by referencing “P1.PersonID” in the inline table value
function. If you look above at the records inserted into the Person table, you
will see that “Dick Smith” has 4 different children, “Duke Francis” has no
children and “Mary Jones" only has one child. The CROSS APPLY operator
merged the parent and child records for each parent record that had children.
As you can see by looking at the output “Smith, Dick” appears twice with a
different child on each row. “Jones, Mary” appears only once in the result set
since she only has only one child. “Duke Francis,” does not appear in the
output because he does not have any children, as well as the children records don’t
appear because they also don’t have any children. Remember the CROSS APPLY
operate only returns rows from the left table source if there is a
corresponding record found in the right table source.

Example of using OUTER APPLY

To demonstrate how the OUTER
APPLY works let’s use the same Person Table I created in my prior example, but
modify the APPLY operator to use an OUTER APPLY instead of a CROSS APPLY. Here
is my OUTER APPLY T-SQL code:


select rtrim(P1.LastName) + ‘, ‘ + rtrim(P1.FirstName) As Parent,
rtrim(P2.LastName) + ‘, ‘ + P2.FirstName As Child,
convert(char(10),P2.BirthDate,102) as BirthDate
from Person P1
outer apply (select top (2) * from Person
where ParentID = P1.PersonID order by BirthDate) P2

When I run this, using the
test data I created in my prior example, I get the following results:


Parent Child BirthDate
——————————- ————————– ———-
Smith, Dick Smith, Doris 1955.10.19
Smith, Dick Smith, John 1958.02.26
Smith, Doris NULL NULL
Smith, John NULL NULL
Smith, Jan NULL NULL
Smith, Joshua NULL NULL
Francis, Duke NULL NULL
Jones, Mary Jones, Danielle 1962.03.17
Jones, Danielle NULL NULL

Because I used the OUTER
APPLY operator, every record in the PERSON table was returned and the name of
the person can be found under the “Parent” column. For those PERSON records
that didn’t have a child record, or the child record didn’t meet the criteria
of being one of the “top (2)”, there is a “NULL” entry in the columns (“Child”
and “BirthDate”). The rows that have a null value did not return any children
when the PersonID was used in the inline table value function.

Conclusion

The APPLY operator is just
one of the many T-SQL enhancements introduced with SQL Server 2005. Having the
APPLY operator now allows you to join columns from a record set with records
returned from a table value function. Being able to accomplish this allows you
to more easily write a set based solution when you need to return data from a
table value function based on rows in an outer table.

»


See All Articles by Columnist
Gregory A. Larsen

Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Latest Articles