CROSS APPLY compared to OUTER APPLY

Question

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?

Answer 1

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

Answer 2

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.