powershelldba.de

CROSS APPLY and OUTER APPLY: Mastering SQL Server's Most Powerful Operators

Most developers avoid APPLY because it looks confusing. But APPLY is SQL Server's most powerful operator for solving problems that would require cursors or complex self-joins in other databases. Once you understand APPLY, you'll use it everywhere: fetching top N per group, ranking rows, accessing table-valued functions—all with clean, efficient SQL.

What is CROSS APPLY?

CROSS APPLY is like a sophisticated JOIN that evaluates the right side for EVERY row on the left side. The right side can reference columns from the left side (correlation). Think of it as a row-by-row "call" to a function or derived table.

Basic Syntax

SELECT *
FROM LeftTable L
CROSS APPLY (
  -- Right side can reference L.column
  SELECT TOP 1 *
  FROM RightTable R
  WHERE R.ForeignKey = L.PrimaryKey
  ORDER BY R.Date DESC
) AS R;

Example: Get Latest Order Per Customer

-- Without APPLY (awkward self-join):
SELECT c.CustomerID, c.Name, o.OrderDate, o.Amount
FROM dbo.Customers c
JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderID = (
  SELECT TOP 1 OrderID
  FROM dbo.Orders o2
  WHERE o2.CustomerID = c.CustomerID
  ORDER BY o2.OrderDate DESC
);
-- Awkward subquery, hard to read

-- WITH CROSS APPLY (clean):
SELECT c.CustomerID, c.Name, latestOrder.OrderDate, latestOrder.Amount
FROM dbo.Customers c
CROSS APPLY (
  SELECT TOP 1 OrderID, OrderDate, Amount
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID
  ORDER BY o.OrderDate DESC
) AS latestOrder;
Key insight: CROSS APPLY evaluates the subquery FOR EVERY customer row. For each customer, it fetches the latest order. Clean, readable, and efficient.

CROSS APPLY vs. OUTER APPLY

Operator Behavior Like SQL JOIN When to Use
CROSS APPLY Right side MUST match. If no match, row excluded. INNER JOIN Results MUST exist for every left row
OUTER APPLY Right side optional. If no match, NULLs returned. LEFT OUTER JOIN Want all left rows, even if right has no match

Example: CROSS APPLY (Excludes Non-Matching)

-- Customers WITH orders only:
SELECT c.CustomerID, c.Name, latestOrder.OrderDate
FROM dbo.Customers c
CROSS APPLY (
  SELECT TOP 1 OrderDate
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID
  ORDER BY o.OrderDate DESC
) AS latestOrder;

-- Result: Only customers who have orders
-- Customers with NO orders are excluded

Example: OUTER APPLY (Includes Non-Matching)

-- ALL customers, with their latest order (if exists):
SELECT c.CustomerID, c.Name, latestOrder.OrderDate
FROM dbo.Customers c
OUTER APPLY (
  SELECT TOP 1 OrderDate
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID
  ORDER BY o.OrderDate DESC
) AS latestOrder;

-- Result: ALL customers (including those with no orders)
-- Customers with no orders show OrderDate = NULL

CROSS APPLY with Table-Valued Functions

Where APPLY truly shines: calling table-valued functions per row.

-- Table-valued function
CREATE FUNCTION dbo.GetTopOrdersForCustomer (@CustomerID INT, @Top INT = 3)
RETURNS TABLE
AS
RETURN
  SELECT TOP (@Top) OrderID, OrderDate, Amount
  FROM dbo.Orders
  WHERE CustomerID = @CustomerID
  ORDER BY OrderDate DESC;

-- Call it for EVERY customer
SELECT c.CustomerID, c.Name, o.OrderID, o.OrderDate, o.Amount
FROM dbo.Customers c
CROSS APPLY dbo.GetTopOrdersForCustomer(c.CustomerID, 3) AS o;

-- Result: Top 3 orders per customer
-- Clean, readable, reusable function

Top N Per Group: The Classic APPLY Problem

Scenario: Get Top 2 Products Per Category

-- WITHOUT APPLY (window functions, harder to read):
SELECT *
FROM (
  SELECT
    CategoryID, ProductName, Sales,
    ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY Sales DESC) AS rn
  FROM dbo.Products
) ranked
WHERE rn <= 2;

-- WITH CROSS APPLY (clearer intent):
SELECT c.CategoryID, c.Name, p.ProductName, p.Sales
FROM dbo.Categories c
CROSS APPLY (
  SELECT TOP 2 ProductName, Sales
  FROM dbo.Products p
  WHERE p.CategoryID = c.CategoryID
  ORDER BY p.Sales DESC
) AS p;

Row-by-Row Evaluation: Understanding APPLY

How APPLY Executes

-- Data:
Customers: CustomerID 1, 2, 3
Orders: 1→(100, 200), 2→(150), 3→(none)

-- Query:
SELECT c.CustomerID, o.*
FROM dbo.Customers c
CROSS APPLY (
  SELECT Amount FROM dbo.Orders WHERE CustomerID = c.CustomerID
) AS o;

-- Execution (row-by-row):
Row 1: c.CustomerID = 1
  → RUN: SELECT Amount WHERE CustomerID = 1
  → Returns: 100, 200
  → Output: (1, 100), (1, 200)

Row 2: c.CustomerID = 2
  → RUN: SELECT Amount WHERE CustomerID = 2
  → Returns: 150
  → Output: (2, 150)

Row 3: c.CustomerID = 3
  → RUN: SELECT Amount WHERE CustomerID = 3
  → Returns: (nothing)
  → Output: (excluded - no rows to join)

-- CROSS APPLY excludes Row 3
// OUTER APPLY would return (3, NULL)

Real-World Examples

Example 1: Top N + Aggregation Per Group

-- Get top 3 sales per region with totals
SELECT
  r.RegionID,
  r.RegionName,
  topSales.SalesmanID,
  topSales.TotalSales,
  topSales.OrderCount
FROM dbo.Regions r
CROSS APPLY (
  SELECT TOP 3
    SalesmanID,
    SUM(Amount) AS TotalSales,
    COUNT(*) AS OrderCount
  FROM dbo.Orders o
  WHERE o.RegionID = r.RegionID
  GROUP BY SalesmanID
  ORDER BY SUM(Amount) DESC
) AS topSales;

Example 2: Date Range Per Category

-- Get latest and earliest order date per category
SELECT
  c.CategoryID,
  c.CategoryName,
  dateRange.LatestOrderDate,
  dateRange.EarliestOrderDate,
  dateRange.DaysBetween
FROM dbo.Categories c
OUTER APPLY (
  SELECT
    MAX(o.OrderDate) AS LatestOrderDate,
    MIN(o.OrderDate) AS EarliestOrderDate,
    DATEDIFF(DAY, MIN(o.OrderDate), MAX(o.OrderDate)) AS DaysBetween
  FROM dbo.Orders o
  WHERE o.CategoryID = c.CategoryID
) AS dateRange;

-- OUTER APPLY: Shows all categories (even if no orders)

Example 3: String Split with Row Numbers

-- Split comma-separated values, add row numbers
SELECT
  id,
  value,
  rowNum
FROM dbo.TableWithCSV
CROSS APPLY (
  SELECT
    value,
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rowNum
  FROM STRING_SPLIT(CSVColumn, ',')
) AS split;

Performance Considerations

When APPLY is Fast

-- Fast: Filtered subquery (uses indexes)
SELECT c.CustomerID, o.*
FROM dbo.Customers c
CROSS APPLY (
  SELECT TOP 1 OrderDate, Amount
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID  -- Filter applied
  ORDER BY OrderDate DESC
) AS o;

-- Each iteration: Indexes on (CustomerID, OrderDate) help
// Customer 1 → Indexed seek to Orders where CustomerID=1

When APPLY is Slow

-- Slow: Unfiltered full scans
SELECT c.CustomerID
FROM dbo.Customers c
CROSS APPLY (
  SELECT COUNT(*) AS cnt
  FROM dbo.Orders o
  -- NO WHERE clause! Scans entire Orders table per customer
) AS stats;

-- For each customer: Full table scan of Orders!
// 10,000 customers × Full scan = Disaster

-- Fix: Add WHERE clause to filter
CROSS APPLY (
  SELECT COUNT(*) AS cnt
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID  -- Filter!
) AS stats;
⚠️ APPLY row-by-row evaluation can be slow if:
• Subquery has no WHERE filter → Full scan per row
• Large left table × Slow subquery = Disaster
• No indexes on join columns
Always add WHERE clause to correlate left-to-right.

APPLY vs. JOIN vs. Window Functions

Method Readability Performance Best For
APPLY Excellent (clear intent) Good (indexed subqueries) Top N per group, complex logic
JOIN Good Excellent Simple 1:1 or 1:many relationships
Window Functions Moderate (ROW_NUMBER complex) Very Good Ranking, aggregation per group

Common Mistakes

Mistake 1: Using CROSS APPLY When OUTER APPLY Needed

-- ✗ BAD: Excludes products with no orders
SELECT p.ProductID, p.Name, latestOrder.OrderDate
FROM dbo.Products p
CROSS APPLY (
  SELECT TOP 1 OrderDate
  FROM dbo.Orders o
  WHERE o.ProductID = p.ProductID
  ORDER BY OrderDate DESC
) AS latestOrder;
-- Products with no orders are missing!

-- ✓ GOOD: Includes all products (NULL if no orders)
SELECT p.ProductID, p.Name, latestOrder.OrderDate
FROM dbo.Products p
OUTER APPLY (
  SELECT TOP 1 OrderDate
  FROM dbo.Orders o
  WHERE o.ProductID = p.ProductID
  ORDER BY OrderDate DESC
) AS latestOrder;

Mistake 2: Missing WHERE Clause (Row-by-Row Full Scans)

-- ✗ SLOW (full scan per customer):
SELECT c.CustomerID, summary.TotalSpent
FROM dbo.Customers c
CROSS APPLY (
  SELECT SUM(Amount) AS TotalSpent
  FROM dbo.Orders  -- NO WHERE!
) AS summary;

-- ✓ FAST (indexed filter):
SELECT c.CustomerID, summary.TotalSpent
FROM dbo.Customers c
CROSS APPLY (
  SELECT SUM(Amount) AS TotalSpent
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID  -- WHERE clause!
) AS summary;

Mistake 3: Forgetting TOP When You Want Limit

-- ✗ Returns ALL orders per customer (not just top 1):
SELECT c.CustomerID, o.*
FROM dbo.Customers c
CROSS APPLY (
  SELECT OrderID, OrderDate
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID
  -- No TOP or LIMIT
) AS o;

-- ✓ Returns only TOP 1 per customer:
SELECT c.CustomerID, o.*
FROM dbo.Customers c
CROSS APPLY (
  SELECT TOP 1 OrderID, OrderDate
  FROM dbo.Orders o
  WHERE o.CustomerID = c.CustomerID
  ORDER BY OrderDate DESC  -- TOP needs ORDER BY
) AS o;

Best Practices

When to Use APPLY vs. Alternatives

Use CROSS/OUTER APPLY when:
• Need top N per group (cleaner than window functions)
• Calling table-valued functions per row
• Complex filtering per row (multiple conditions, subqueries)
• Need both matching AND non-matching handling (OUTER APPLY)

Use JOIN when:
• Simple 1:1 relationship
• No TOP N filtering
• All rows must match (no NULLs)

Use Window Functions when:
• Ranking, numbering (ROW_NUMBER, RANK, DENSE_RANK)
• Running totals (SUM OVER)
• Comparing to previous/next row (LAG, LEAD)

The Bottom Line

CROSS/OUTER APPLY is SQL Server's secret weapon for problems that would require cursors or complex logic in other databases. Once you master it, you'll write cleaner, more efficient queries.

Key rule: APPLY is row-by-row evaluation. The right side runs for EVERY left row. Use WHERE clauses to filter efficiently, use TOP to limit results per row, and use OUTER APPLY to include non-matching rows.

CROSS APPLY = INNER JOIN behavior (excludes non-matches). OUTER APPLY = LEFT JOIN behavior (includes non-matches, with NULLs). Pick the right one, and you'll solve complex queries that seem impossible with traditional SQL.

← Back to Blog