APPLY operator was introduced in Sql Server 2005. The main purpose of the APPLY operator is to JOIN a Table with a Table Valued User Defined Function. The APPLY operator invokes/executes the Table Valued User Defined Function for each row returned by the LEFT side table expression of the APPLY operator.

APPLY operator can be used to join two table expressions just like any other JOIN operators. In case of APPLY operator, for each row in the LEFT side table expression of the APPLY operator the RIGHT side table expression is evaluated and final result can have the columns from both LEFT and RIGHT side table expressions just like in any other JOIN operations result.

Unlike APPLY operator, classic joins like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN etc, doesn’t support evaluating a RIGHT side table valued function based on the column values of each row returned by the LEFT side table expression.

In other words the classic joins works on the self-sufficient sets of data, where these sets of data doesn’t depend on one another. Where-as in case of APPLY OPERATOR the RIGHT side data set is not self-sufficient, instead the APPLY operator uses values from the LEFT side table expression to define the RIGHT side data set.

Types of APPLY operators in Sql Server

There are two types of APPLY operators in Sql Server

CROSS APPLY

This operator returns only those rows from the LEFT side table expression of the CROSS APPLY operator which produces the result from the RIGHT side Table expression. So from the result perspective we can say that the CROSS APPLY is similar to that of the classic INNER JOIN operation

To understand CROSS APPLY with extensive list of examples, you may like to read CROSS APPLY in Sql Server

OUTER APPLY

This operator returns all the rows from the LEFT side table expression of the OUTER APPLY operator irrespective of whether it produces the corresponding result in the RIGHT side table expression or not. The RIGHT side table expression columns value will be NULL in the final result for those rows in the LEFT table expression that don’t produce the result from the RIGHT side table expression. So from the result perspective we can say that the OUTER APPLY is similar to the classic LEFT OUTER JOIN

To understand OUTER APPLY with extensive list of examples, you may like to read OUTER APPLY in Sql Server