The APPLY Operator

Hi, welcome back. After the series of posts about window functions, it’s time to talk about something else which is very interesting and there’s not a lot of people using it. I’m talking about the APPLY operator.

The APPLY operator is a very powerful and useful operator that we can use to apply some logic to every row from a table. It’s very similar to a JOIN operator, but the main difference is that JOIN treats both sides as a complete set of rows, so if any side of the JOIN is a query, you can’t call columns from one side to the other.

With APPLY, the left side of the operator stays as a static predefined relation, and the right side is evaluated per row of the left side. Since APPLY is a table operator, anything we define in a table operator, will be available in subsequent phases in the logical processing flow. I’m not going to talk about the logical processing flow of a query right now, I’ll leave that for another post.

For now, let me show you an example on how to use one of the flavors of APPLY.

CROSS APPLY

Not long ago, somebody came to with a query similar to the one below:

Don’t know why the query was like that or why they wanted it to be like that, I didn’t ask. I only help them make it a little bit better. There are different ways to improve this query, however, at the moment I thought that it would be nice to use the CROSS APPLY given the situation.

This is how I modified the query to use CROSS APPLY, I’ll explain:

Instead of using UNION ALL, I generated a table using the VALUES table constructor, in here I put what they needed to show as a category and the value to display, which in the SELECT I’m adding using the SUM aggregate function.

Notice that after using the CROSS APPLY, you have to declare a “name” for that table operator, in this case I named it A(Category, Value), this way I can go into the SELECT and use whatever I have.

OUTER APPLY

The OUTER APPLY is very similar to a LEFT OUTER JOIN, they both will return rows from the left side even if there’s no rows matching on the right side. Here’s an example:

In the query above, I’m returning the latest three orders the employee sold. If you see, employees 5 and 9 are showing in the result set even though they don’t have any order. Because of the way APPLY works, there is no APPLY that will do the same as a RIGHT OUTER JOIN.

WORKING WITH FUNCTIONS

There is another way you can work with the APPLY operator, which is sometimes easier depending on the requirements. This is, wrapping the logic inside the APPLY in a Table Valued Function. I’ll take what’s inside the OUTER APPLY and put it into a function.

Here’s an example:

I’m passing the EmployeeID as a parameter for the function, and now we can use it this way with CROSS APPLY or OUTER APPLY.

As you can see, I get the same results as if I just put the query inside the OUTER APPLY operator. This way you can define a reusable logic within the function and pass parameters. When you don’t need to use any parameter, you can use a view instead.

Worth to mention, this is not from a performance point of view, I’m saving that for some other time.

Here’s the link to the code so you can follow along or practice.
https://github.com/learningsql/sql-code

Well, that’s all folks! I hope you enjoyed the post learned something new, or refreshed something you have forgotten.

Don’t forget to like and share, see you soon!