: SQL - The Power of APPLYJul 06 2011, 19:10 PM

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 [text()]
                  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