The apply keyword was introduced mainly for the use of selecting data by combining a select query with a table valued function, which is nothing but select query on another table (or result of any business logic in the function). To understand it better, let’s call our main table as 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
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 aim will be to join these two tables to get the required data. One solution which immediately comes to mind is use of
LEFT, RIGHT or
INNER JOIN, which will depend on our requirement. Let’s see the results with the use of
The same results can be achieved using the apply keyword. It works the same way as the co-related sub query, with the fact that the select query on which apply is used i.e. the right table, will be executed for each and every record of the main table or the left table. It can be applied in two different ways:
- CROSS APPLY: This works same as the
INNER JOINon two queries.
- OUTER APPLY: This works same as the
LEFT JOINon 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
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
Department query, for each record of
Employee record (same as that of a
Next, suppose we were using the co-related sub query. But we need to view the rest of the columns of the second table i.e. the
Department table. Can we do that ? Doesn’t seems to be, unless we add some twist in the query. But this can be easily done with
APPLY keyword. Simply add 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 which returns
Department details by
Id. Next we replace our Select statement for
Department, with call to the user defined function. See the query below:
- 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.