CROSS APPLY and OUTER APPLY

A join combines two tables that each make sense on their own. Sometimes the right-hand side depends on the left-hand row: “the two dearest products of this category”. APPLY runs a table expression once for each row on its left, and that expression can use the left row’s columns:

1SELECT c.Name AS Category, t.Name, t.Price2FROM dbo.Categories AS c3CROSS APPLY (4    SELECT TOP (2) p.Name, p.Price5    FROM dbo.Products AS p6    WHERE p.CategoryId = c.CategoryId7    ORDER BY p.Price DESC8) AS t9ORDER BY c.CategoryId, t.Price DESC;

Category  Name                 Price
--------  -------------------  -----
Tools     Cordless drill       89.00
Tools     Claw hammer          18.50
Building  Cement 25 kg          7.20
Building  Sand 25 kg            4.10
Timber    Plywood sheet 18 mm  42.00
Timber    Timber 2x4 2.4 m      6.80
Fixings   Wood screws (200)    11.90
Fixings   Wall plugs (100)      4.50
Paint     Masonry paint 10 L   54.00
Paint     Paint roller set     12.50
Safety    Safety goggles        6.80

For each category, the inner query runs with that category’s id and returns up to two rows. Each is joined to the category row. Safety has only one product, so it gets one row; Plumbing has none, so it disappears, just as with an inner join.

OUTER APPLY keeps every left row

OUTER APPLY is to CROSS APPLY what LEFT JOIN is to INNER JOIN: a left row whose inner query returns nothing is kept, with NULLs:

1SELECT c.Name AS Category, t.Name, t.Price2FROM dbo.Categories AS c3OUTER APPLY (4    SELECT TOP (1) p.Name, p.Price5    FROM dbo.Products AS p6    WHERE p.CategoryId = c.CategoryId7    ORDER BY p.Price DESC8) AS t9ORDER BY c.CategoryId;

Category  Name                 Price
--------  -------------------  -----
Tools     Cordless drill       89.00
Building  Cement 25 kg          7.20
Timber    Plywood sheet 18 mm  42.00
Fixings   Wood screws (200)    11.90
Paint     Masonry paint 10 L   54.00
Safety    Safety goggles        6.80
Plumbing  NULL                  NULL

SELECT TOP (2) p.Name, p.Price
FROM dbo.Products AS p
WHERE p.CategoryId = 1  -- c.CategoryId
ORDER BY p.Price DESC

Why a join cannot do this

A derived table on the right of a JOIN is worked out on its own, before it is matched with anything. It cannot see the left table’s columns:

1SELECT c.Name AS Category, t.Name, t.Price2FROM dbo.Categories AS c3JOIN (4    SELECT TOP (2) p.Name, p.Price, p.CategoryId5    FROM dbo.Products AS p6    WHERE p.CategoryId = c.CategoryId7    ORDER BY p.Price DESC8) AS t ON t.CategoryId = c.CategoryId;

Msg 4104, Level 16, State 1, Line 6
The multi-part identifier "c.CategoryId" could not be bound.

Moving the filter into ON does not help either: then TOP (2) picks the two dearest products of the whole table before any matching happens. APPLY is the operator that lets the right side depend on the left row, which is called a lateral join in other databases.

Each customer’s latest order

The inner query can return several columns, which a scalar subquery in SELECT cannot. OUTER APPLY keeps the customers who have never ordered:

1SELECT c.FirstName, lo.OrderId, lo.OrderDate, lo.Status2FROM dbo.Customers AS c3OUTER APPLY (4    SELECT TOP (1) o.OrderId, o.OrderDate, o.Status5    FROM dbo.Orders AS o6    WHERE o.CustomerId = c.CustomerId7    ORDER BY o.OrderDate DESC8) AS lo9ORDER BY c.CustomerId;

FirstName  OrderId  OrderDate   Status
---------  -------  ----------  ---------
Asha             3  2024-03-01  Shipped
Ben              9  2024-06-01  Pending
Chloe            6  2024-04-20  Cancelled
Dev              7  2024-05-05  Shipped
Ella          NULL  NULL        NULL
Farid           10  2024-06-12  Shipped
Grace         NULL  NULL        NULL
Zoë              8  2024-05-18  Pending

Naming a calculation

A column alias from SELECT cannot be used in the same SELECT, in WHERE or in GROUP BY. CROSS APPLY with a one-row SELECT (no FROM) gives a calculation a name that every later clause can use, so you write it only once:

1SELECT oi.OrderId, oi.ProductId, v.Net, g.Vat, g.Gross2FROM dbo.OrderItems AS oi3CROSS APPLY (SELECT oi.Quantity * oi.UnitPrice AS Net) AS v4CROSS APPLY (SELECT CAST(v.Net * 0.20 AS decimal(10,2)) AS Vat,5                    CAST(v.Net * 1.20 AS decimal(10,2)) AS Gross) AS g6WHERE g.Gross > 2007ORDER BY g.Gross DESC;

OrderId  ProductId     Net    Vat   Gross
-------  ---------  ------  -----  ------
      4          6  272.00  54.40  326.40
      4          7  252.00  50.40  302.40

The second APPLY uses v.Net from the first: each APPLY can see everything to its left. This costs nothing: the optimiser folds the expressions into the query as if you had written them out.

APPLY with a table-valued function: STRING_SPLIT

The right side of APPLY can be a table-valued function that takes the left row’s columns as arguments. STRING_SPLIT(text, separator) is one built into SQL Server: it returns one row per piece in a column called value:

1CREATE TABLE #Tags (ProductId int, Tags nvarchar(100));2INSERT INTO #Tags VALUES (1, N'hand tool,steel'), (2, N'power tool,cordless,18V'), (6, N'timber');3 4SELECT t.ProductId, p.Name, s.value AS Tag5FROM #Tags AS t6JOIN dbo.Products AS p ON p.ProductId = t.ProductId7CROSS APPLY STRING_SPLIT(t.Tags, N',') AS s8ORDER BY t.ProductId, s.value;

ProductId  Name              Tag
---------  ----------------  ----------
        1  Claw hammer       hand tool
        1  Claw hammer       steel
        2  Cordless drill    18V
        2  Cordless drill    cordless
        2  Cordless drill    power tool
        6  Timber 2x4 2.4 m  timber

Unpivoting with VALUES

As the PIVOT lesson showed, CROSS APPLY (VALUES …) turns columns into rows and, unlike UNPIVOT, keeps NULLs (here filtered out on purpose). One row per way to reach each customer:

1SELECT c.FirstName, v.Method, v.Contact2FROM dbo.Customers AS c3CROSS APPLY (VALUES ('Email', CAST(c.Email AS nvarchar(100))),4                    ('Phone', CAST(c.Phone AS nvarchar(100)))) AS v(Method, Contact)5WHERE v.Contact IS NOT NULL6  AND c.CustomerId <= 47ORDER BY c.CustomerId, v.Method;

FirstName  Method  Contact
---------  ------  -----------------
Asha       Email   asha@example.com
Asha       Phone   0113 496 0001
Ben        Phone   01904 555 010
Chloe      Email   chloe@example.com
Dev        Email   dev@example.com
Dev        Phone   01274 555 220

1SELECT p.Name, lo.OrderId, lo.OrderDate2FROM dbo.Products AS p3OUTER APPLY (4    SELECT TOP (1) o.OrderId, o.OrderDate5    FROM dbo.OrderItems AS oi6    JOIN dbo.Orders AS o ON o.OrderId = oi.OrderId7    WHERE oi.ProductId = p.ProductId8    ORDER BY o.OrderDate DESC9) AS lo10WHERE p.CategoryId IN (1, 3)11ORDER BY p.ProductId;

Name                 OrderId  OrderDate
-------------------  -------  ----------
Claw hammer                8  2024-05-18
Cordless drill             3  2024-03-01
Tape measure 8 m          10  2024-06-12
Timber 2x4 2.4 m          10  2024-06-12
Plywood sheet 18 mm        8  2024-05-18