November 28, 2009

Cross Apply and Outer Apply

The APPLY operator allows you to return values from an outer table much like JOIN, however I find it's easier to read, and allows joining two tables that I would've had to do some fancy foot work on to join. And since I'm always looking for the simpliest solution, this one fit the bill on my lastest project.

There are two forms of APPLY: CROSS APPLY and OUTER APPLY.  CROSS APPLY returns only rows from the outer table that produce a result. It works similiar to INNER JOIN in that it won't return values that are null. The OUTER APPLY returns either values, or nulls if no values exist.

The value of using the APPLY operator is that it can yield better performance. Check out http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/ to read more.
 
 
Information on how the Apply operator works
 
Here's a link to Technet's article on the APPLY operator: https://technet.microsoft.com/en-us/library/ms175156(SQL.90).aspx
 
Another article that might shed more light on this is from the SQL Team: http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005
 
A good article from Steve Novoselac's blog: T-SQL: Using CROSS APPLY to Turn 2 Queries Into 1
 

2 comments: