This is a relatively new feature of Microsoft SQL Server introduced with Microsoft SQL Server 2005, that I have never really had a need for before, but today I found myself wanting to push 'joins' beyond what that can handle. After a little bit of
research I came across the APPLY operator.
The APPLY operator allows you to join the columns of a 'table value function' onto a record set.
There are two types of APPLY operators CROSS APPLY and OUTER APPLY, and as you can probably guess the CROSS APPLY works in a fairly similar way to an INNER JOIN and an OUTER APPLY works like an OUTER JOIN. INNER JOIN and CROSS APPLY will return results when both record sets have a value and OUTER JOIN and OUTER APPLY will return results regardless if there is a matching record on either side.
More information and examples can be found on DatabaseJournal.com