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 artic...