CODEWITHSUNDEEP
sqlsql-server-2008-r2
whytheq
whytheq

Reputation: 35605

CROSS APPLY compared to OUTER APPLY

These scripts give me the same result

SELECT * FROM
(select x = null) x
OUTER APPLY
(select x = 1) y  

SELECT * FROM
(select x = null) x
CROSS APPLY
(select x = 1) y

Are CROSS APPLY and OUTER APPLY the same?

Is there an example of a situation where they do not return the same result?

Upvotes: 5

Views: 1761

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107826

Here's a situation where they won't return the same result. Incidentally, you only use APPLY when you need to correlate prior tables/subqueries with the next ones.

     SELECT x.x, y.x y
       FROM (select [x] = 1) x
OUTER APPLY (select [x] = 1 where x.x is null) y  

-- result
1, null

     SELECT x.x, y.x y
       FROM (select [x] = 1) x
CROSS APPLY (select [x] = 1 where x.x is null) y

-- result
(empty result set)

OUTER APPLY is to CROSS APPLY what
OUTER JOIN is to INNER JOIN

Upvotes: 6

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

Think INNER JOIN (for CROSS) and LEFT JOIN (for OUTER) to make the distinction easier to understand. CROSS returns only rows from the outer table where the applied function returns a result set. OUTER returns all rows from the outer table.

Upvotes: 10

Related Questions