Day 10: APPLY operator

A. Lesson overview

Activity breakdown

Business scenario. Customer Services is building a “customer 360” screen: for each customer, their most recent order, their total payments, their favourite product and every way to contact them. Each item is a small query that depends on the customer — “for this customer, give me…”. APPLY was designed for exactly that shape of problem.

B. Theory explained clearly

Part 1 — What APPLY does

A join combines two independent inputs: the right-hand table cannot refer to columns of the left-hand table. APPLY removes that restriction. The right-hand side is a table expression evaluated for each row of the left-hand side, and it may use that row’s columns — like a correlated subquery that can return many rows and many columns.

USE RetailLab;
GO

SELECT  c.CustomerID, c.FirstName, r.OrderID, r.OrderDate
FROM    Sales.Customer AS c
CROSS APPLY
(
    SELECT TOP (2) o.OrderID, o.OrderDate
    FROM   Sales.SalesOrder AS o
    WHERE  o.CustomerID = c.CustomerID          -- refers to the outer row
    ORDER BY o.OrderDate DESC, o.OrderID DESC
) AS r
ORDER BY c.CustomerID, r.OrderDate DESC;

For each customer, the inner query returns that customer’s two most recent orders. You could not write this as a join to a derived table: a derived table cannot see c.CustomerID, and TOP (2) in a plain derived table would return two rows in total, not two per customer.

Part 2 — CROSS APPLY versus OUTER APPLY

The example above uses CROSS APPLY, so the five customers who have never ordered are missing; with OUTER APPLY they appear once each with NULLs.

Aggregates always return a row. SELECT SUM(Amount) FROM … WHERE … returns one row (with NULL) even when nothing matches. So CROSS APPLY (SELECT SUM(…) …) keeps every outer row — the “no rows” case never happens. Add GROUP BY inside the APPLY if you want “no matches → no row”.

Order of evaluation and filters. As with joins, a WHERE condition on OUTER APPLY columns in the outer query removes the NULL rows. Put conditions that restrict the right side inside the APPLY.

Part 3 — Primer: inline table-valued functions

An inline table-valued function (TVF) is a named, parameterised query. It returns a table and can be used wherever a table can — typically with APPLY, passing columns of the outer row as arguments.

CREATE OR ALTER FUNCTION Lab.fn_RecentOrders
(
    @CustomerID int,
    @Top        int
)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@Top) o.OrderID, o.OrderDate, o.OrderStatus, o.SalesChannel
    FROM   Sales.SalesOrder AS o
    WHERE  o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC, o.OrderID DESC
);
GO

Calling it:

SELECT * FROM Lab.fn_RecentOrders(1, 3);                         -- on its own

SELECT c.CustomerID, r.*
FROM   Sales.Customer AS c
CROSS APPLY Lab.fn_RecentOrders(c.CustomerID, 2) AS r;           -- per customer

Permissions. Creating a function needs the CREATE FUNCTION permission in the database and ALTER permission on the target schema. Your training login (a sysadmin) has both; in a work database, ask for rights on a development schema rather than broad permissions. Running a function needs SELECT on it.

There is also a multi-statement TVF, which builds a table variable step by step. It behaves very differently for performance; Day 18 compares the two. Prefer inline TVFs.

Part 4 — Four everyday APPLY patterns

The third pattern solves the Day 7 problem “an alias cannot be used in the same SELECT”: values defined in an APPLY are visible to WHERE, GROUP BY, SELECT and ORDER BY, because the APPLY belongs to the FROM clause (step 1 of logical processing).

APPLY or ROW_NUMBER? Both solve top-N per group. APPLY + TOP shines when there are few outer rows and an index supports the inner ORDER BY — SQL Server can seek straight to each group’s top rows. ROW_NUMBER over the whole table is often better when most rows are needed anyway. Day 23 shows how to measure.

C. Dataset and starting state

Preparation. None if RetailLab is unchanged. Otherwise run scripts/setup/04_reset_training_data.sql in SQLCMD Mode (destructive) or scripts 00 → 03. The reset also removes any Lab objects.

Validation. Expected: 40, 35, 0 (no functions in Lab yet).

USE RetailLab;
GO

SELECT  (SELECT COUNT(*) FROM Sales.Customer)                     AS Customers,
        (SELECT COUNT(DISTINCT CustomerID) FROM Sales.SalesOrder) AS CustomersWhoOrdered,
        (SELECT COUNT(*) FROM sys.objects
         WHERE  schema_id = SCHEMA_ID('Lab') AND type IN ('IF', 'TF', 'FN')) AS LabFunctions;

(sys.objects is a catalog view listing database objects; IF = inline TVF, TF = multi-statement TVF, FN = scalar function.)

D. Worked examples

Worked example 1 — Two most recent orders per customer: CROSS vs OUTER APPLY

SELECT  c.CustomerID, r.OrderID, r.OrderDate
FROM    Sales.Customer AS c
CROSS APPLY
(
    SELECT TOP (2) o.OrderID, o.OrderDate
    FROM   Sales.SalesOrder AS o
    WHERE  o.CustomerID = c.CustomerID
    ORDER BY o.OrderDate DESC, o.OrderID DESC
) AS r
ORDER BY c.CustomerID, r.OrderDate DESC, r.OrderID DESC;

Expected result: 70 rows — every one of the 35 ordering customers has at least two orders. Customer 1: orders 1373 (2025-12-29) and 1372 (2025-12-28). Change CROSS to OUTER: 75 rows, adding customers 36–40 with NULL order columns.

Worked example 2 — Create and use an inline TVF

CREATE OR ALTER FUNCTION Lab.fn_RecentOrders
(
    @CustomerID int,
    @Top        int
)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@Top) o.OrderID, o.OrderDate, o.OrderStatus, o.SalesChannel
    FROM   Sales.SalesOrder AS o
    WHERE  o.CustomerID = @CustomerID
    ORDER BY o.OrderDate DESC, o.OrderID DESC
);
GO

SELECT * FROM Lab.fn_RecentOrders(17, 3) ORDER BY OrderDate DESC, OrderID DESC;

SELECT  c.CustomerID, c.FirstName, r.OrderID, r.OrderDate, r.OrderStatus
FROM    Sales.Customer AS c
OUTER APPLY Lab.fn_RecentOrders(c.CustomerID, 1) AS r
WHERE   c.CustomerID IN (1, 17, 35, 37)
ORDER BY c.CustomerID;

Expected result: the first query returns customer 17’s three latest orders: 1376 (2025-12-31), 1365 (2025-12-23) and 1297 (2025-08-06). The second returns 4 rows: customer 1 → 1373 (Pending), 17 → 1376 (Delivered), 35 → 1364 (Shipped), 37 → NULL.

What to notice: the ORDER BY inside the function decides which rows TOP keeps; it does not guarantee the order the caller sees — hence the ORDER BY in each calling query.

Worked example 3 — Several aggregates per customer without fan-out

SELECT  c.CustomerID,
        o.Orders,
        o.LastOrderDate,
        p.Payments,
        p.TotalPaid
FROM    Sales.Customer AS c
OUTER APPLY
(
    SELECT COUNT(*) AS Orders, MAX(OrderDate) AS LastOrderDate
    FROM   Sales.SalesOrder
    WHERE  CustomerID = c.CustomerID
) AS o
OUTER APPLY
(
    SELECT COUNT(*) AS Payments, SUM(py.Amount) AS TotalPaid
    FROM   Sales.Payment    AS py
    JOIN   Sales.SalesOrder AS so ON so.OrderID = py.OrderID
    WHERE  so.CustomerID = c.CustomerID
) AS p
WHERE   c.CustomerID IN (1, 12, 37)
ORDER BY c.CustomerID;

Expected result

What to notice: each APPLY aggregates its own child table per customer, so orders and payments never multiply each other (Day 8’s fan-out). Customer 37 gets a row with 0 and NULL from each APPLY even with no data — aggregates always return a row, so CROSS APPLY would have kept them too. Customer 12 has 30 orders in total, including cancelled ones; the scorecard on Day 7 counted 27 non-cancelled.

Worked example 4 — Define calculations once with CROSS APPLY (VALUES)

SELECT  p.ProductCode,
        p.ListPrice,
        m.UnitMargin,
        m.MarginPct
FROM    Inventory.Product AS p
CROSS APPLY (VALUES (p.ListPrice - p.StandardCost)) AS u (UnitMargin)
CROSS APPLY (VALUES (u.UnitMargin,
                     CAST(100.0 * u.UnitMargin / NULLIF(p.ListPrice, 0) AS decimal(5,1)))
            ) AS m (UnitMargin, MarginPct)
WHERE   m.MarginPct >= 55                         -- the alias is usable in WHERE
ORDER BY m.MarginPct DESC, p.ProductCode;

Expected result: 20 products with a margin of at least 55%, from NAV-003 Waterproof Map Case (66.6%) down to JKT-001 and JKT-002 (55.0%). The second APPLY builds on the first (u.UnitMargin) — a chain of named calculations inside a single SELECT, without a CTE.

Worked example 5 — Columns to rows: one row per contact method

SELECT  c.CustomerID,
        v.ContactType,
        v.ContactValue
FROM    Sales.Customer AS c
CROSS APPLY (VALUES (N'Email', c.Email),
                    (N'Phone', CAST(c.Phone AS nvarchar(255)))) AS v (ContactType, ContactValue)
WHERE   v.ContactValue IS NOT NULL
  AND   c.CustomerID IN (1, 5, 14)
ORDER BY c.CustomerID, v.ContactType;

Expected result: 3 rows — customer 1’s email and phone, and customer 5’s phone. Customer 14 has neither, so both rows are filtered out. The CAST gives both VALUES rows the same type (Email is nvarchar(255), Phone is varchar(30)). Day 11 shows UNPIVOT, which does the same job with different NULL behaviour.

E. Guided practical lab — Customer 360

Build one row per active customer with: latest order (date and status), number of payments and total paid, and favourite product (most units bought; ties broken by product code).

Step 1 — Start from active customers

1. Do: Select the base rows.
2. Run:

   USE RetailLab;
   GO
   
   SELECT  c.CustomerID, CONCAT_WS(N' ', c.FirstName, c.LastName) AS CustomerName
   FROM    Sales.Customer AS c
   WHERE   c.IsActive = 1
   ORDER BY c.CustomerID;

3. Observe: 37 rows.
4. Why it matters: Every APPLY you add must keep this count. If it changes, an APPLY is filtering or multiplying rows.
5. Confirm: 37 rows.

Step 2 — Latest order with OUTER APPLY + TOP (1)

1. Do: Add the latest order.
2. Run:

   SELECT  c.CustomerID,
           CONCAT_WS(N' ', c.FirstName, c.LastName) AS CustomerName,
           lo.OrderDate                              AS LatestOrderDate,
           lo.OrderStatus                            AS LatestOrderStatus
   FROM    Sales.Customer AS c
   OUTER APPLY
   (
       SELECT TOP (1) o.OrderDate, o.OrderStatus
       FROM   Sales.SalesOrder AS o
       WHERE  o.CustomerID = c.CustomerID
       ORDER BY o.OrderDate DESC, o.OrderID DESC
   ) AS lo
   WHERE   c.IsActive = 1
   ORDER BY c.CustomerID;

3. Observe: 37 rows; customers 37–40 show NULLs.
4. Why it matters: OUTER APPLY keeps customers with no orders; CROSS APPLY would have returned 33 rows.
5. Confirm: customer 1 → 2025-12-29, Pending.

Step 3 — Payments with an aggregate APPLY

1. Do: Add another OUTER APPLY that counts and sums the customer’s payments.
2. Run: add after the first APPLY:

   OUTER APPLY
   (
       SELECT COUNT(*) AS Payments, COALESCE(SUM(py.Amount), 0) AS TotalPaid
       FROM   Sales.Payment    AS py
       JOIN   Sales.SalesOrder AS so ON so.OrderID = py.OrderID
       WHERE  so.CustomerID = c.CustomerID
   ) AS pd

   and add pd.Payments, pd.TotalPaid to the SELECT list.
3. Observe: 37 rows. Customer 1: 27 payments, 8475.96. Customer 37: 0 and 0.00.
4. Why it matters: An aggregate inside APPLY keeps the outer grain.
5. Confirm: still 37 rows.

Step 4 — Favourite product with GROUP BY + TOP (1) inside APPLY

1. Do: Add a third OUTER APPLY that groups the customer’s lines by product and keeps the top one.

2. Run: add:

   OUTER APPLY
   (
       SELECT TOP (1) p.ProductCode, p.ProductName, SUM(l.Quantity) AS Units
       FROM   Sales.SalesOrder     AS o
       JOIN   Sales.SalesOrderLine AS l ON l.OrderID   = o.OrderID
       JOIN   Inventory.Product    AS p ON p.ProductID = l.ProductID
       WHERE  o.CustomerID = c.CustomerID
         AND  o.OrderStatus <> 'Cancelled'
       GROUP BY p.ProductCode, p.ProductName
       ORDER BY SUM(l.Quantity) DESC, p.ProductCode
   ) AS fav

   and add fav.ProductName AS FavouriteProduct, fav.Units AS FavouriteUnits to the SELECT list.

3. Observe: sample rows:

   CustomerID	LatestOrderDate	LatestOrderStatus	Payments	TotalPaid	FavouriteProduct	FavouriteUnits
   1	2025-12-29	Pending	27	8475.96	Merino Base Layer Leggings	8
   2	2025-12-22	Shipped	6	1469.91	Baseplate Compass	3
   5	2025-10-10	Delivered	7	837.90	Ridge Walking Boot	2
   12	2025-12-23	Shipped	30	8814.95	Gas Canister 230g	18
   35	2025-12-22	Shipped	2	509.95	Dreamer 3-Season Sleeping Bag	2
   37	NULL	NULL	0	0.00	NULL	NULL

4. Why it matters: GROUP BY, TOP and ORDER BY all work inside APPLY — it is a full query per customer. The tie-breaker on ProductCode makes “favourite” deterministic.

5. Confirm: still 37 rows.

Step 5 — Add the preferred contact and the final sort

1. Do: Use CROSS APPLY (VALUES …) to compute a preferred contact once and reuse it.
2. Run: add:

   CROSS APPLY (VALUES (COALESCE(c.Email, CAST(c.Phone AS nvarchar(255)), N'No contact details'))) AS ct (PreferredContact)

   and add ct.PreferredContact to the SELECT list. Then add ORDER BY CASE WHEN lo.OrderDate IS NULL THEN 1 ELSE 0 END, pd.TotalPaid DESC, c.CustomerID to list the best payers first and never-ordered customers last.
3. Observe: Customer 18 (8893.41) is first, then 12 (8814.95) and 1 (8475.96); customers 37–40 are last.
4. Why it matters: The final query has four APPLY operators and one row per customer. Each piece can be developed and tested on its own.
5. Confirm: 37 rows; first row customer 18.

F. Independent practice

Task 1 — Latest three orders per store

For each store, return its three most recent orders: StoreName, OrderID, OrderDate and CustomerID. Sort by store name, then date descending.

Success criteria: 9 rows. Edinburgh Old Town: 1376 (2025-12-31), 1371 (2025-12-27), 1352 (2025-12-07). London Covent Garden: 1359, 1338, 1334. Manchester Northern Quarter: 1365, 1361, 1357.

Task 2 — When was each product last sold?

For every product, return ProductCode, LastSoldDate, LastOrderID and LastCustomerID (non-cancelled orders). Sort by LastSoldDate, then ProductCode.

Success criteria: 30 rows. NAV-004 has NULLs (never sold) and sorts first; TNT-004 was last sold on 2024-12-19 (order 1163, customer 4); COK-003 on 2025-12-29 (order 1373, customer 1).

Task 3 — A reusable yearly summary function

Create an inline TVF Lab.fn_CustomerYearSummary(@CustomerID int, @Year int) that returns Orders and NetSales for that customer’s non-cancelled orders in that year. Use it with CROSS APPLY to list the five customers with the highest 2025 net sales.

Success criteria: customers 1 (14 orders, 4914.22), 29 (10, 4479.25), 13 (15, 4333.85), 6 (12, 4166.75) and 24 (5, 3920.00).

Task 4 — Days to the second order (challenging)

For each customer who has ordered, find their first and second non-cancelled orders with APPLY, and return CustomerID, FirstOrderDate, SecondOrderDate and DaysToSecondOrder. Then, in a second query, return the number of customers and the average days (1 decimal place).

Success criteria: 35 rows; customer 28 waited longest (651 days, 2024-03-05 → 2025-12-16). Average: 114.1 days across 35 customers.

Stretch challenge — Tag popularity

Inventory.Product.Tags holds comma-separated keywords. Use CROSS APPLY STRING_SPLIT(p.Tags, ',') to return one row per product and tag, then count products per tag. Return tags used by at least two products, most common first, then alphabetically.

Success criteria: 12 tags. waterproof (5), then jacket and tent (4 each), then backpack (3), then eight tags with 2. In total the 30 products carry 63 tags (43 distinct).

G. Solutions and explanations

SELECT  s.StoreName, r.OrderID, r.OrderDate, r.CustomerID
FROM    Sales.Store AS s
CROSS APPLY
(
    SELECT TOP (3) o.OrderID, o.OrderDate, o.CustomerID
    FROM   Sales.SalesOrder AS o
    WHERE  o.StoreID = s.StoreID
    ORDER BY o.OrderDate DESC, o.OrderID DESC
) AS r
ORDER BY s.StoreName, r.OrderDate DESC, r.OrderID DESC;

SELECT  p.ProductCode,
        ls.OrderDate  AS LastSoldDate,
        ls.OrderID    AS LastOrderID,
        ls.CustomerID AS LastCustomerID
FROM    Inventory.Product AS p
OUTER APPLY
(
    SELECT TOP (1) o.OrderDate, o.OrderID, o.CustomerID
    FROM   Sales.SalesOrderLine AS l
    JOIN   Sales.SalesOrder     AS o ON o.OrderID = l.OrderID
    WHERE  l.ProductID = p.ProductID
      AND  o.OrderStatus <> 'Cancelled'
    ORDER BY o.OrderDate DESC, o.OrderID DESC
) AS ls
ORDER BY ls.OrderDate, p.ProductCode;

CREATE OR ALTER FUNCTION Lab.fn_CustomerYearSummary
(
    @CustomerID int,
    @Year       int
)
RETURNS TABLE
AS
RETURN
(
    SELECT  COUNT(DISTINCT o.OrderID)                                AS Orders,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))      AS NetSales
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.CustomerID  = @CustomerID
      AND   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate  >= DATEFROMPARTS(@Year, 1, 1)
      AND   o.OrderDate  <  DATEFROMPARTS(@Year + 1, 1, 1)
);
GO

SELECT TOP (5)
        c.CustomerID,
        y.Orders,
        CAST(y.NetSales AS decimal(12,2)) AS NetSales2025
FROM    Sales.Customer AS c
CROSS APPLY Lab.fn_CustomerYearSummary(c.CustomerID, 2025) AS y
ORDER BY y.NetSales DESC, c.CustomerID;

SELECT  c.CustomerID,
        f.OrderDate                            AS FirstOrderDate,
        s.OrderDate                            AS SecondOrderDate,
        DATEDIFF(DAY, f.OrderDate, s.OrderDate) AS DaysToSecondOrder
FROM    Sales.Customer AS c
CROSS APPLY
(
    SELECT TOP (1) o.OrderDate
    FROM   Sales.SalesOrder AS o
    WHERE  o.CustomerID = c.CustomerID AND o.OrderStatus <> 'Cancelled'
    ORDER BY o.OrderDate, o.OrderID
) AS f
CROSS APPLY
(
    SELECT o.OrderDate
    FROM   Sales.SalesOrder AS o
    WHERE  o.CustomerID = c.CustomerID AND o.OrderStatus <> 'Cancelled'
    ORDER BY o.OrderDate, o.OrderID
    OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY
) AS s
ORDER BY DaysToSecondOrder DESC, c.CustomerID;

-- Summary
SELECT  COUNT(*)                                                               AS Customers,
        CAST(AVG(CAST(DATEDIFF(DAY, f.OrderDate, s.OrderDate) AS decimal(10,2))) AS decimal(6,1)) AS AvgDaysToSecond
FROM    Sales.Customer AS c
CROSS APPLY (SELECT TOP (1) o.OrderDate FROM Sales.SalesOrder AS o
             WHERE o.CustomerID = c.CustomerID AND o.OrderStatus <> 'Cancelled'
             ORDER BY o.OrderDate, o.OrderID) AS f
CROSS APPLY (SELECT o.OrderDate FROM Sales.SalesOrder AS o
             WHERE o.CustomerID = c.CustomerID AND o.OrderStatus <> 'Cancelled'
             ORDER BY o.OrderDate, o.OrderID
             OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY) AS s;

SELECT  TRIM(t.value) AS Tag,
        COUNT(*)      AS Products
FROM    Inventory.Product AS p
CROSS APPLY STRING_SPLIT(p.Tags, ',') AS t
GROUP BY TRIM(t.value)
HAVING  COUNT(*) >= 2
ORDER BY Products DESC, Tag;

H. Knowledge check

1. Understanding. Why can’t you get “the two latest orders per customer” by joining customers to a derived table that uses TOP (2)?

2. Query interpretation. A query uses CROSS APPLY (SELECT SUM(Amount) AS Paid FROM Sales.Payment AS p WHERE p.OrderID = o.OrderID). Do cancelled orders (which have no payments) disappear from the result? Why?

3. Debugging. CREATE OR ALTER FUNCTION Lab.fn_X … fails with ‘CREATE FUNCTION’ must be the first statement in a query batch. Fix it.

4. Debugging. An OUTER APPLY returns the expected 40 customers, but after adding WHERE r.OrderStatus <> 'Cancelled' only 35 remain. Why, and where should the condition go?

5. Understanding. Give one advantage of CROSS APPLY (VALUES …) over repeating an expression in SELECT, WHERE and ORDER BY.

I. Troubleshooting and cleanup

Cleanup. Drop today’s functions — later lessons do not use them. If you skip this, they are harmless and are removed by the next reset.

USE RetailLab;
GO
DROP FUNCTION IF EXISTS Lab.fn_RecentOrders;
DROP FUNCTION IF EXISTS Lab.fn_CustomerYearSummary;
GO

Core data is unchanged; nothing else needs restoring for Day 11.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 returned 70 rows with CROSS APPLY and 75 with OUTER APPLY.
• You created, called and dropped Lab.fn_RecentOrders.
• Your customer 360 query returns 37 rows with customer 18 first.
• Tasks 1–4 match the success criteria (9, 30, 5 and 35 rows).
• You can explain why aggregates inside CROSS APPLY never remove outer rows.

Recap

• APPLY evaluates a table expression per outer row and can use that row’s columns.
• CROSS APPLY behaves like an inner join (rows without results disappear); OUTER APPLY like a left join.
• Top-N per row: APPLY + TOP … ORDER BY with a unique tie-breaker.
• Aggregates inside APPLY add related totals without fan-out, and always return a row.
• CROSS APPLY (VALUES …) names calculations once and turns columns into rows.
• Inline TVFs are parameterised queries that SQL Server expands into the caller; create them with CREATE OR ALTER in their own batch.

Next: Day 11 — PIVOT and UNPIVOT, including dynamic pivots for an unknown set of columns.

References (Microsoft Learn)

• FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL) — CROSS APPLY and OUTER APPLY
• CREATE FUNCTION (Transact-SQL)
• Create user-defined functions (Database Engine)
• Table value constructor (Transact-SQL)
• STRING_SPLIT (Transact-SQL)
• sys.objects (Transact-SQL)