Cross Apply VS Outer Apply

The APPLY operator is required when you have to use table-valued function in the query, but it can also be used with an inline SELECT statements.

 

The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only. Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise it’s like a CROSS JOIN with a correlated sub-query) with an implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

Cross Apply returns only rows from the outer table that produces a result set from the table-valued function.

Outer Apply returns both rows that produce a result set and rows that do not, with NULL values in the columns produced by the table-valued function.