3

I was reading a lot about CROSS APPLY versus INNER JOIN recently. I tried writing some queries and they both work in a similar fashion, the execution plan seems to be same as well.

Maybe someone with a deeper knowledge could explain to me what the difference is between these two?

OUTER APPLY (SELECT UserID
                , ActionPerformedDate = MAX(ActionDate)
            FROM dbo.AdminUsage
            WHERE SubscriberID = S.SubscriberID
                AND ActionPerformed = 'Some Action'
                AND Description = 'True'
            GROUP BY UserID) AS AU


LEFT JOIN (SELECT SubscriberID
                , UserID
                , MAX(ActionDate) OVER(PARTITION BY SubscriberID) AS ActionPerformedDate
            FROM dbo.AdminUsage
            WHERE ActionPerformed = 'Some Action'
                AND Description = 'True') AS AU
    ON AU.SubscriberID = S.SubscriberID

Is there a preferred approach in this case?

  • You forgot the GROUP BY in the 2nd sample. – ypercubeᵀᴹ Sep 17 '14 at 15:02
5

They're two slightly different animals that can be used for the same purposes, as in your example. CROSS APPLY is your only option for "joining" table value functions and "expanding" xml documents, though.

Some queries, particularly parallel queries, can exhibit vastly improved performance using CROSS APPLY, provided you have the requisite processor threads and indexing strategy.

Microsoft MVP Itzik Ben-Gan elaborates a couple of great examples in this talk

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.