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?
Best Answer
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