With the release of SQL Server 2005 Microsoft added a new operator that allows you to join a record set with a function, and apply the function to every qualifying row of the table (or view). This new operator is the APPLY operator. Technically, the operation performed is not actually a JOIN, but because it is used like a JOIN, it is frequently referred to as a joining operation. The APPLY operator takes on two formats: CROSS APPLY, or OUTER APPLY. In this article I will explain the differences of these two format, and show you examples of how each of these formats work.

Introduction to APPLY operator

Have you ever wanted to write a SELECT statement that will call a tabled value function or evaluate table-valued expression for every record in a record set? If so then you will be happy to know the APPLY operator allows you to do this. There are two different formats of the APPLY operator.

The first format is CROSS APPLY. This format takes column values from each row of the table or record set on one side of the CROSS APPLY operator and uses the value as input to the tabled value function or expression on the other side of the CROSS APPLY operator. The rows returned from each call to the table-valued function or expression are joined to row containing the value that invoked the function call. ALL sets of joined rows are then joined together using the UNION ALL operation. If the table-valued function returns no rows for a particular invocation of the function, then the table or record set row is not included in the final result set, because it can't be joined to any table-valued function rows.

The second format the APPLY operator can use is OUTER APPLY. This operator works similar to the CROSS APPLY operator when the table-valued function or expression returns rows. But it has an additional feature. The OUTER APPLY operator also returns the table or record set row even when the table-valued function invoked by the row doesn't return any rows.

To better understand how these two formats of the APPLY operator work, let's look at some examples for each of these different formats.

Test Data and Function for Examples

Before I can show you the different examples of the APPLY operator I need to create some test data and a table-valued function. My test data tables and function can be found in Listing 1.

USE tempdb;
GO
IF object_id('dbo.Product') IS NOT NULL 
    DROP TABLE dbo.Product;
IF object_id('dbo.SearchString') IS NOT NULL 
    DROP TABLE dbo.SearchString;
IF object_id('dbo.FindProductLike') IS NOT NULL
    DROP FUNCTION dbo.FindProductLike;
CREATE TABLE dbo.Product
    (
      ID INT IDENTITY ,
      ProductName VARCHAR(100) ,
      Price MONEY
    );
INSERT  INTO dbo.Product
VALUES  ( 'Red Santa Suit', 199.99 ),
        ( 'Candy Canes', 1.99 ),
        ( 'Fake Snow', 2.99 ),
        ( 'Red Bells', 49.99 ),
        ( 'LED Lights', 6.99 );
CREATE TABLE dbo.SearchString
    (
      ID INT IDENTITY ,
      String VARCHAR(100)
    );
INSERT  INTO dbo.SearchString
VALUES  ( 'Red' ),
        ( 'Lights' ),
        ( 'Star' );
GO
CREATE FUNCTION dbo.FindProductLike
    (
      @FindString VARCHAR(100)
    )
RETURNS TABLE
AS
RETURN
    ( SELECT    ProductName ,
                Price
      FROM      dbo.Product
      WHERE     ProductName LIKE '%' + @FindString + '%'
    )

Listing 1: Create table and Table-valued Function

The code in Listing 1 creates a table named Product that contains five different products. I also create a table called dbo.SearchString that contains 3 different strings. Lastly I created a table-valued function called dbo.FindProductLike. The dbo.FindProductLike function accepts one parameter @FindString. This function takes the parameter provided and finds all the Product table rows that have a ProductName that is contains the string of characters passed in the @FindString parameter.

Using CROSS APPLY operator

The CROSS APPLY operator will execute a table-valued function for every row in the record set with which it is associated. To demonstrate how this works let me run the code in Listing 2:

USE tempdb;
GO
SELECT  *
FROM    dbo.SearchString AS S
        CROSS APPLY dbo.FindProductLike(S.String);

Listing 2: Example of CROSS APPLY operator

When I run the code in listing 2, with the query results going to text, I get the output in Report 1.

ID    String           ProductName                Price
----- ---------------- -------------------------- ---------
1     Red              Red Santa Suit             199.99
1     Red              Red Bells                  49.99
2     Lights           LED Lights                 6.99

Report 1: Output produced by running the code in Listing 2

If you review the code in Listing 2 you will see I used the CROSS APPLY operator to join the rows in the dbo.SearchString table with the results from the dbo.FindProductLike table-valued function. The CROSS APPLY operate takes the String value from the dbo.SearchString table and calls the table-valued function dbo.FindProductLike. If the table-valued function returns any rows, they are joined to the dbo.SearchString row.

The first two rows in the result table where produced by taking the String value of “Red” and using it to call the dbo.FindProductLike function. When “Red” is passed to this function it returns ProductName and Price column values for rows where the ProductName column contains the word “Red”. The two rows returned from the dbo.FindProductLike are then join to the dbo.SearchString row that contained the value “Red” to produce the first two rows shown in Report 1. The results in the third row of the report is produced the same way as the first two rows, except this time the function was called with a parameter value of “Lights”, which only match a single ProductName value. Thus producing only a single row in the result set shown in Report 1. The last row in the dbo.SearchString table, with a Search column value of “Star”, didn't match any ProductName's values in the Product table, so no rows where produced in Report 1 for the dbo.SearchString row that contain the column value of “Star”.

Using the OUTER APPLY operator

The OUTER APPLY works similar to CROSS APPLY. The only difference is the OUTER APPLY operator also returns results for table rows that do not return any rows from the table-valued function. To see how this works review the code in Listing 3.

USE tempdb;
GO
SELECT  *
FROM    dbo.SearchString AS S
        OUTER APPLY dbo.FindProductLike(S.String);

Listing 3: OUTER APPLY example

When I run the code in Listing 3 I get the output in Report 2.

ID    String           ProductName                Price
----- ---------------- -------------------------- ---------
1     Red              Red Santa Suit             199.99
1     Red              Red Bells                  49.99
2     Lights           LED Lights                 6.99
3     Star             NULL                       NULL

Report 2: Output produced by running the code in Listing 3

By using OUTER APPLY operator in Listing 3 you can see there is now a row that contains the NULL value in columns ProductName and Price columns for the String value of “Star”. This occurred because the String value of “Star” in the dbo.SearchString table is not found to be part of any ProductName within the dbo.Product table. When the dbo.FindProductLike table-valued function returns the empty row set using a String column valued then SQL Server associates NULL values with the columns produced by the table-valued function. This functionality of the OUTER APPLY operator is similar to when there are no matches between two tables in an OUTER JOIN operation. When a String value does return rows when passed to the dbo.FindProductLike table-valued function, then the row from the dbo.SearchString table is joined to the results of the table-valued function, just like is done when a CROSS APPLY operator is used.

Using a Table-valued Expression

Up to this point I've only shown you examples of where the APPLY operator is used between a record set and a table-valued function. The APPLY operator also works with a table-valued expression. To see how this works review the code in Listing 4.

USE tempdb;
GO
SELECT * FROM dbo.SearchString as S
CROSS APPLY 
(SELECT ProductName, Price
    FROM dbo.Product 
WHERE ProductName like '%' + S.String + '%') as X

Listing 4: CROSS APPLY with table-valued expression

When I run the code in Listing 4 I get the output in Report 3.

ID    String           ProductName                Price
----- ---------------- -------------------------- ---------
1     Red              Red Santa Suit             199.99
1     Red              Red Bells                  49.99
2     Lights           LED Lights                 6.99

Report 3: Output produced by running the code in Listing 4

The code in Listing 4 is equivalent to the code in Listing 2, and the output in Report 3 is exactly the same as the output in Report 1. In Listing 4, I changed the expression string on the right side of the CROSS APPLY operate to a correlated subquery. This subquery uses the String column value from the dbo.SearchString table to find all the ProductName values that have a name that contains the value of the String column.

I can also take the code in Listing 4 and make it equivalent to the code in Listing 3 by just changing the CROSS APPLY operation to an OUTER APPLY operation. I'll let you make that change and run the new code to verify it does produce the same output as Report 2.

Summary

The APPLY operation allows you to join rows in a record set to the results of a table-valued function or table-valued expression. By using the APPLY operator you can write set based queries where one of the sets is a table-valued function or expression. Next time you need to join rows from a record set to a table-valued function or expression you can use the APPLY operator.

Question and Answer

In this section you can review how well you have understood using the APPLY operator by answering the following questions.

Question 1:

The APPLY operator allows you to join a record set with a scalar function. (TRUE or FALSE)?

• TRUE
• FALSE

Question 2:

What are the two formats supported by the APPLY operator?

• CROSS APPLY
• FULL APPLY
• INNER APPLY
• OUTER APPLY

Question 3:

If you want to return all records from a record set associated with the table-valued function using the APPLY operator, which APPLY operator format would you use?

• CROSS APPLY
• OUTER APPLY

Answers:

Question 1:

The correct answer is b or false. The APPLY operator does not allow you to join a record set with a scalar function. But it does allow you to join a record set with a table-valued function or table-valued expression.

Question 2:

The correct answers are a and d. The APPLY operator supports two formats CROSS APPLY and OUTER APPLY.

Question 3:

The correct answer is b. a is incorrect because if you use the CROSS APPLY operator you might not return all rows in the record set associated with the table-valued function. The OUTER APPLY operation is the only format that will guarantee that all records from the record set associated with the table-valued function are returned in the final result set.