4

The operator APPLY is not an operator I've any experience of using and I'm worried I'm missing certain possibilities.

This article by Alexander Kuznetsov gives a good example

In Alexander's example he uses APPLY to join to the return from a function - are there other situations where APPLY, CROSS or OUTER should be the first thing I use?

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • 1
    RE: "I'm worried I'm missing certain possibilities". [Some uses for it here](http://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql/9275865#9275865) – Martin Smith Sep 30 '12 at 17:40

3 Answers3

2

APPLY can join any arbitrary result set. I find it especially useful to return the TOP 1 row by some ordering:

select *
from T
cross apply (
 select top 1 * from T2 order by DateTime desc
) x

APPLY can do everything a join can and more. But it cannot use join hints.

Basically, you want to use a join if a join is enough. Use apply if you need more because it is a little more cumbersome to use.

usr
  • 168,620
  • 35
  • 240
  • 369
2

APPLY is a row-by-row operator that allows you to APPLY each row of the prior tables to either a function or a subquery. It therefore is very useful in situations where

  1. you need the latest/max/min record from B from each record in A
  2. you need to run a table-valued function on each record in A

Yes it can substitute a JOIN but but it will perform terribly where a normal JOIN would have worked, because you're forcing SQL Server to use row-by-row operators rather than set-based JOIN.

The difference between CROSS APPLY and OUTER APPLY is the same as the difference between INNER JOIN and OUTER JOIN. Basically, CROSS APPLY will remove the source record if the function/subquery to which it is applied returns 0 records. OUTER APPLY keeps the source record.

Read more : Using Apply (msdn)

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
0

Basically join use to combine two resultset but CROSS APPLY behave like loop its takes outer table result then pass every row to drive table or table-valued function as parameter to produce the resultset. there are two form on apply CROSS APPLY and OUTER APPLY. CROSS APPLY t return only rows which match with drive table or table-valued function and OUTER APPLY result all records of outer table and matching of drive table try to avoid using apply because its slow query then joins

Shyam
  • 31
  • 1