Apply Operator

June 30, 2006

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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers