SQL Server Cross/outer Apply usage

This is the SQL Server Help documentation for the description of apply:

Use the APPLY operator (version 2005 or later) to invoke a table-valued function for each row returned by an external table expression that implements a query operation. The table-valued function acts as the right input and the outer table expression as the left input. The resulting row is combined as the final output by evaluating the right input for each row in the left input. The list of columns generated by the APPLY operator is the column set in the left input, followed by the list of columns returned by the right input.

There are two forms of apply: cross apply and OUTER apply. Cross APPLY returns only the rows in the external table that generated the result set through the table-valued function. OUTER APPLY returns both the row that generated the result set and the row that does not produce the result set, where the value in the column generated by the table-valued function is NULL.

Take a look at the example:

SELECT * FROM table1 join MyFunction (1) on 1=1

The MyFunction parameter is a constant that can return a table.

But sometimes we want to use the Table1 field as a parameter, to pass in the function to calculate, like:

SELECT * FROM table1 join MyFunction (ID) on 1=1

This is going to go wrong. This time we can use apply to achieve. For example:

SELECT * FROM table1 cross apply MyFunction (ID) on 1=1

Simply put, apply allows us to pass the data of each row of the preceding result set as an argument to the following expression, which can be either a table-valued function or a select result set.

Actual Project Application:

Product tables and clickthrough statistics, by product in the last one months by click-through order.

If not apply, the implementation is more troublesome, with apply is probably the way to achieve:

; with Cteresult as (   select Row_number () over (order by HitCount) as rowID,           ProductID,          ProductName      from ProductInfo pi with (nolock)     outer apply (select SUM (HitCount) HitCount from Hitstatisticsinfo HSI     where HSI. Targetid = Pi. ProductID and      HSI. Hittime >= ' 2015-08-08 '     Group by Targetid) HSI     where pi. Isdel = 0) SELECT * from Cteresult where rowid between 1 and 20

The difference between cross apply and outer apply is like the difference between a left join and a join. If apply does not take place, outer apply will return the line, and cross apply will not output the line.

So, when you need to use the value of a field as an argument, or when a join is more complex to implement, you can consider apply.

SQL Server Cross/outer Apply usage