SQL – The Power of APPLY

MS SQL 2005 and up adds support for the APPLY clause, which, in turn, lets you join a table to any dynamic sets, such as table-valued-functions or even derived tables. While we can argue over the benefits and dangers of the latter (another set of article perhaps?), being able to do things more than one way is certainly always awesome.
The two new functions (CROSS APPLY and OUTER APPLY) are essentially INNER and OUTER JOINs for table-functions (you cannot directly join them like tables). Here is just a simple example of the usage (don’t hesitate to expand on it):

SELECT All.CustomerID
 FROM
 Customer as C
 CROSS APPLY
 fnSelectAllChildren(C.CustomerID) AS All
 WHERE C.Status = 1

This technique can also be used in more complex queries alongside derived tables, FOR XML, etc., covering the need for things such as inline multi-row CONCAT (multiple rows into a single column):

SELECT
 ito.ID,
 (
 SELECT TOP 1
 ISNULL(LEFT(o.list, LEN(o.list) - 1), 'Unknown')
 FROM
 Orders AS ito2
 CROSS APPLY (
 SELECT
 CONVERT(VARCHAR(12), ServiceType) + ',' AS 
 FROM
 OrderService AS itos2 (NOLOCK)
 JOIN
 OrderServiceType AS itost (NOLOCK)
 ON
 itost.Id = itos2.OrderServiceType
 WHERE
 itos2.OrderId = ito.ID
 FOR
 XML PATH('')
 ) o (list)
 WHERE
 ito2.ID = ito.ID
 ) AS AllTypes
 FROM
 Orders (NOLOCK) ito
 WHERE
 ito.Status = 0