Erik Darling gets an auto-link for talking about my favorite operator:
I end up converting a lot of derived joins, particularly those that use windowing functions, to use the apply syntax. Sometimes good indexes are in place to support that, other times they need to be created to avoid an Eager Index Spool.
One of the most common questions I get is when developers should consider using apply over other join syntax.
The short answer is that I start mentally picturing the apply syntax being useful when:
To learn when, you’re going to have to read the whole thing. And, if you want to learn even more about it, I have a talk on the topic that might be of interest.