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, 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 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.

Cross apply in SQL , Outer apply in sql

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 JOINS.

Cross apply in SQL , Outer apply in sqlThe 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 JOIN on two queries.
  • OUTER APPLY: This works same as the LEFT JOIN on two queries.

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

Cross apply in SQL , Outer apply in sqlAs 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 Department query, for each record of Employee record (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 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:

Cross apply in SQL , Outer apply in sql

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:

Cross apply in SQL , Outer apply in sqlSo, 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.

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in SQL Server and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s