patternsqlMinor
CROSS APPLY versus INNER JOIN
Viewed 0 times
versuscrossapplyjoininner
Problem
I was reading a lot about
Maybe someone with a deeper knowledge could explain to me what the difference is between these two?
Is there a preferred approach in this case?
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.SubscriberIDIs there a preferred approach in this case?
Solution
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
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
Context
StackExchange Database Administrators Q#74859, answer score: 5
Revisions (0)
No revisions yet.