SQL Server supports table valued functions, what are functions that return data in the form of tables.

JOIN operations in SQL Server are used to join two or more tables. However, JOIN operations cannot be used to join a table with the output of a table valued function.

APPLY operators are used for this purpose.

There are two main types of APPLY operators. 1) CROSS APPLY and 2) OUTER APPLY.

The CROSS APPLY operator is semantically similar to INNER JOIN operator. It retrieves those records from the table valued function and the table being joined, where it finds matching rows between the two.

On the other hand, OUTER APPLY retrieves all the records from both the table valued function and the table, irrespective of the match.

In this article we will take a look at the CROSS APPLY and OUTER APPLY operators. We will see how they are implemented practically with the help of an example and will also discuss how they differ from each other.


How does Cross Apply and Outer Apply work

  • The APPLY operator introduced in SQL Server 2005, is used to join a table to a table-valued function.
  • The Table Valued Function on the right hand side of the APPLY operator gets called for each row from the left (also called outer table) table.
  • Cross Apply returns only matching rows (semantically equivalent to Inner Join)
  • Outer Apply returns matching + non-matching rows (semantically equivalent to Left Outer Join). The unmatched columns of the table valued function will be set to NULL.