HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

CROSS APPLY versus INNER JOIN

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
versuscrossapplyjoininner

Problem

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?

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

Context

StackExchange Database Administrators Q#74859, answer score: 5

Revisions (0)

No revisions yet.