CROSS/OUTER APPLY

What you’ll learn

Per-row subqueries/TVFs via CROSS APPLY and OUTER APPLY.

-- Get the most recent order per customer
SELECT c.CustomerID, c.Name, x.TopOrderDate
FROM dbo.Customers AS c
CROSS APPLY (
  SELECT TOP (1) OrderDate
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID
  ORDER BY o.OrderDate DESC
) AS x(TopOrderDate);

-- OUTER APPLY returns NULLs when the inner subquery is empty

Notes

CROSS APPLY is like an inner join to a per-row derived set; OUTER APPLY preserves the left row.
Great for JSON shredding, string splitting, and top‑N‑per‑group patterns.