July 1, 2012 at 9:43 pm
Is the benefit of OUTER APPLY and CROSS APPLY that you don't need an ON clause? I don't really see why these are different from LEFT OUTER JOIN and CROSS JOIN.
Can someone help me understand? A WHERE clause is needed with APPLY but not a JOIN.
July 2, 2012 at 4:35 am
It depends on the usage.Some of cross apply can be written as inner join but if the cross apply is used for a function then it can not be rewritten as the inner join, however, you could still use the join by not using the function but instead using the code of fucntion, if it is a single sql statement.
For much more details try the following link.
http://www.sqlservercentral.com/articles/APPLY/69953/
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 2, 2012 at 6:34 am
Ted_Kert (7/1/2012)
Is the benefit of OUTER APPLY and CROSS APPLY that you don't need an ON clause? I don't really see why these are different from LEFT OUTER JOIN and CROSS JOIN.Can someone help me understand? A WHERE clause is needed with APPLY but not a JOIN.
Two excellent papers by Paul White will clear this up for you, they're in my sig below. APPLY is somewhat like a correlated subquery (or parameterized view) in the FROMlist.
A WHERE clause is not necessary with APPLY, neither is a table reference.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply