Geek asks and answers

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 APPLYand OUTER APPLYthe same?

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

+5
source share
2 answers

Think INPUT INPUT (for CROSS) and LEFT JOIN (for OUTER) to make the difference more clear. CROSS returns only rows from an external table, where the application function returns a set of results. OUTER returns all rows from an external table.

+9
source

Here is a situation where they do not return the same result. By the way, you use APPLY only when you need to match previous tables / subqueries with the following.

     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)

- ,

+5

More articles:


All Articles