APPLY in SQL Server

Introduction

The apply keyword was introduced mainly for selecting data by combining a select query with a table-valued function, which is nothing but a select query on another table (or the result of any business logic in the function). To understand it better, let's call our main table the left table and the second table (in the table-valued function) the right table.

Let's start by creating sample data. We will have two tables, Employee and Department. Here, our Employee table will be the left table, and the Department table will be the right one. Each employee can only belong to one department.

Our intent will be to join these two tables to get the required data. One solution that immediately comes to mind is using a LEFT, RIGHT, or INNER JOIN, which will depend on our requirements. Let's see the results with the use of JOINS.

APPLY works the same way as the co-related subquery, with the fact that the select query on which the application is used, in other words, the right table, will be executed for every record of the main table or the left table, that is nothing but how the co-related sub query works. The same results can be obtained using the apply keyword. It can be applied in either of the following two ways:

• CROSS APPLY- Works the same as the INNER JOIN on two queries. 
• OUTER APPLY- Works the same as the LEFT JOIN on two queries.

Let's change the queries using these two apply forms and see the results.

As we can see above, CROSS APPLY gives the same result as the INNER JOIN, and OUTER APPLY gives the same result as the LEFT OUTER JOIN. The difference with the JOIN is that APPLY results in the execution of the select statement of the Department query for each record of the Employee record (the same as that of a co-related sub-query).

Next, suppose we were using the co-related subquery. But we need to view the rest of the columns of the second table. In other words, the Department table. Can we do that? Unless we add some twist to the query, it doesn't seem to be. But this can be easily done with the APPLY keyword. Add the name of the columns we want to view in the select statement of the Department, and we are done. Let's change our queries and see the results:

Another possible and extensive use of APPLY is with the table-valued function. We create a simple function that returns Department details by Id. Next, we replace our select statement for Department with a call to the user-defined function. See the query below:

Summary

So, depending on the requirements, we can add or remove the columns' names in the function call's SELECT statement. To summarize, we can use the apply keyword as.

• A co-related subquery with the advantage of selecting multiple columns.
• A join with the table-valued user-defined function to select multiple columns from the second table.

So this was about the use of the apply keyword.