Adding CROSS APPLY to your bag of T-SQL tricks
Recently I found myself stuck on a problem while constructing a query for a stored procedure. Like any other developer when I get stuck I start looking through my “bag of tricks” to see if I can find something that will help me solve this problem, but this time I came up empty handed. That’s when I started reading and learned about the APPLY operator, mainly CROSS apply. Many of you may be well versed with this operator already but if you are not, it may come in handy sometime in the future.
According to MSDN the APPLY operator allows you to invoke a table-valued function for each row returned by an out table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input. So to reiterate what MSDN is trying to get across is that when you use the APPLY operator each row of the output table of the table valued function (a function that returns a table rather than a scalar value) or sub-query is evaluated for each row of the outer table (your main query). So for each row of your main query, some function or sub query is being applied to it using data from that row; the results of that function or sub-query are then being added to row.
The APPLY operator comes in two flavors, CROSS APPLY and OUTER APPLY. CROSS APPLY returns only the rows from the outer table (main query) which have a result set from the table valued function. This is similar to how an INNER JOIN functions. On the other hand OUTER APPLY returns both rows that produce results and those that do not. For the rows where results were not produced NULL values will be present.
Always keep performance in mind; every time a table-value function is run there will have to be at least one scan. This means that although using the APPLY operator can be great fun, it has its time and place and shouldn’t be used all the time (Although, I know it can be tempting). I hope this helps clear up the APPLY operator so that you can use it in the future when you get stuck on a problem.