APPLY in SQL Server

The apply keyword was introduced mainly for the use of selecting data by combining a select query with a table valued function, that 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) as 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 table. 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 use of a LEFT, RIGHT or INNER JOIN, that will depend on our requirements. Let's see the results with the use of JOINS.



The same results can be obtained using the apply keyword. APPLY works the same way as the co-related sub query, with the fact that the select query on which the apply is used, in other words the right table, will be executed for each and every record of the main table or the left table, that is nothing but how the co-related sub query works. 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 forms of apply 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 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 sub query. But we need to view the rest of the columns of the second table. in other words the Department table. Can we do that? It doesn't seem to be, unless we add some twist to the query. But this can be easily done with the APPLY keyword. Simply add the name of the columns we want to view, in the select statement of Department and we are done. Let's change our queries and see the results:



Another possible and big 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:



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

• A co-related sub query 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.