APPLY - 2

Question

Post reply

APPLY - 2

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jim_K SSCrazy Points: 2418 More actions · Answer 1

Coffee was a prerequisite in order for me to get this right.

Carl Landry Hall of Fame Points: 3914 More actions · Answer 2

I just don't get what to do with this APPLY keyword.

The query can be redone using a LEFT OUTER JOIN, which results in the same output and the same plan.

Is APPLY easier to read for you? Are there cases where APPLY is the only way to go?

Carl Landry Hall of Fame Points: 3914 More actions · Answer 3

Looks like I found an answer. The documentation shows an example where APPLY is used to provide an argument to a table-function that require a parameter. It looks like it can't be done with a JOIN.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

demonfox SSCertifiable Points: 6289 More actions · Answer 5

thanks for the question

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

carl.landry (10/2/2012)
I just don't get what to do with this APPLY keyword.
The query can be redone using a LEFT OUTER JOIN, which results in the same output and the same plan.
Is APPLY easier to read for you? Are there cases where APPLY is the only way to go?

carl.landry (10/2/2012)
Looks like I found an answer. The documentation shows an example where APPLY is used to provide an argument to a table-function that require a parameter. It looks like it can't be done with a JOIN.

It's not just for calling user-defined table-valued functions. APPLY is also the only way to include a subquery that is correlated to another table in the same FROM clause. Your first post (that the query can be redone using LEFT OUTER JOIN) is not correct; when you do that, it will result in an error. (Okay, technically you are correct; with enough effort you can redo the query with LEFT OUTER JOIN, but that would require a non-trivial effort to rewrite the query, and the result would be harder to read and maintain).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/