CROSS APPLY

[Skittle]

I'm trying to expand my humble SQL query skills and I have stumbled across the CROSS APPLY.

I have an example from a book and I kind of get it but I just want to clarify a few things.
I want to understand when you would use a CROSS APPLY and what the benefits are.

It seems to me the benefits dictate when you would use CROSS APPLY.

Benefits
I believe the benefits are to reduce the number of times a function is called for rows in a query.
Instead of running the function for every possible row in a big SELECT you can use CROSS APPLY
to only run the function against rows returned on the outer query selection to reduce the amount of resources it takes to get the returned query results.


When To Use
To increase performance by running a function against less rows than necessary.



Questions
1) Is my ‘Benefits’ description accurate – if not what is missing?
2) Is my ‘When To Use’ description accurate – if not what else is there to consider?
3) Does it make any difference if the CROSS APPLY function returns a set of ROWS or
single aggregate?


Context
For context, here is the example from the book.

SELECT S.Supplierid, s.companyname AS Supplier, A.*
FROM Production.Suppliers AS S
   CROSS APPLY (SELECT productid, productname, unitprice
                              FROM Production.Products AS P
                              WHERE P.Supplierid = S.Supplierid
                              ORDER BY unitprice, productid
                              OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY) AS A
WHERE S.Country = ‘N’Japan;

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[gk53]

cross apply is analog to inner join, it works created to work with table valued functions
if query
select F.* from sys.objects O
inner join dbo.myTableFun(O.name) F
on F.schema_id= O.schema_id
giving syntactic error
query
select F.* from sys.objects O
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id
compiles properly

 

[TamarGranor]

I spent a bunch of time playing with and trying to get my head around APPLY a couple of months ago. As I see it, the key benefit is the ability to "correlate" the right-hand side (whether it's a function call or a derived table). That is, the function can receive a specific value from the table on the left-hand side and use it to determine which results to return.

One place that APPLY is particularly useful is when you want a function to compute multiple values for each record from the left-hand side. For example, suppose you want to know how many of each product a customer bought in a given period, and how much she spent total on each product. The function can compute all of that for a customer and then return it as a table.

Hope this helps.

Tamar