OUTER APPLY in SQL Server -- When and Why to Use It

OUTER APPLY is one of those SQL Server operators that can seem mysterious at first, but once you understand its power, it becomes an invaluable tool in your T-SQL arsenal.  In this post I will try to demystify it using some fun examples with the stock market and trading.

What is OUTER APPLY?

OUTER APPLY is a table operator introduced in SQL Server 2005 that allows us to invoke a table-valued function for each row returned by an outer table expression.  Think of it as a 'for each row, do this' operator that can reference columns from the outer query.

The key difference between OUTER APPLY and CROSS APPLY is that OUTER APPLY returns all rows from the left table expression, even if the right table expression returns no rows (similar to LEFT JOIN vs INNER JOIN).

When to use OUTER APPLY?

1. Top N Per Group Scenarios - A very common use case is retrieving the most recent trades for each symbol.

-- Get the 5 most recent trades for each symbol

SELECT 

    s.Symbol,

    s.CompanyName,

    s.Sector,

    t.TradeID,

    t.TradeDate,

    t.Price,

    t.Volume,

    t.Side -- BUY/SELL

FROM dbo.Securities s

OUTER APPLY (

    SELECT TOP 5

        TradeID, 

        TradeDate, 

        Price,

        Volume,

        Side

    FROM dbo.Trades t

    WHERE t.Symbol = s.Symbol

    ORDER BY TradeDate DESC

) t

2. Complex Market Calculations with Row Context - When you need to perform

complex calculations that depend on the current security.

-- Calculate VWAP and trading statistics for each symbol

SELECT 

    s.Symbol,

    s.LastPrice,

    stats.VWAP,

    stats.TotalVolume,

    stats.BuyVolume,

    stats.SellVolume,

    stats.ImbalanceRatio

FROM dbo.Securities s

OUTER APPLY (

    SELECT 

        SUM(Price * Volume) / NULLIF(SUM(Volume), 0) AS VWAP,

        SUM(Volume) TotalVolume,

        SUM(CASE WHEN Side = 'BUY' THEN Volume ELSE 0 END) AS BuyVolume,

        SUM(CASE WHEN Side = 'SELL' THEN Volume ELSE 0 END) AS SellVolume,

        CAST(SUM(CASE WHEN Side = 'BUY' THEN Volume ELSE 0 END) as FLOAT) / 

            NULLIF(SUM(CASE WHEN Side = 'SELL' THEN Volume ELSE 0 END), 0) AS ImbalanceRatio

    FROM dbo.Trades t

    WHERE t.Symbol = s.Symbol

    AND t.TradeDate >= DATEADD(HOUR, -1, GETDATE()) -- Last hour

) stats

3. Finding Related Market Events - Perfect for correlating trades with market events

or finding bracket orders:

-- Find the next fill after each order placement

SELECT

o.OrderID,

o.Symbol,

o.OrderTime,

o.OrderType,

o.LimitPrice,

fill.ExecutionTime,

fill.FillPrice,

fill.FillQuantity,

DATEDIFF(MILLISECOND, o.OrderTime, fill.ExecutionTime) AS LatencyMs

FROM dbo.Orders o

OUTER APPLY (

SELECT TOP 1

e.ExecutionTime,

e.Price as FillPrice,

e.Quantity as FillQuantity

FROM dbo.Executions e

WHERE e.OrderID = o.OrderID

AND e.ExecutionTime >= o.OrderTime

ORDER BY e.ExecutionTime

) fill

WHERE o.OrderDate = CAST(GETDATE() as DATE)

4. Portfolio Position Calculations - Calculate running positions and P&L per account.

-- Get current positions with realized P&L

SELECT

a.AccountID,

a.AccountName,

pos.Symbol,

pos.NetPosition,

pos.AvgCost,

pos.RealizedPnL

FROM dbo.Accounts a

OUTER APPLY (

SELECT

Symbol,

SUM(CASE WHEN Side = 'BUY' THEN Quantity ELSE -Quantity END) AS NetPosition,

AVG(CASE WHEN Side = 'BUY' THEN Price END) AS AvgCost,

SUM(CASE WHEN Side = 'SELL' THEN (Price - Cost) * Quantity ELSE 0 END) AS RealizedPnL

FROM dbo.Trades t

WHERE t.AccountID = a.AccountID

AND t.TradeDate >= DATEADD(DAY, -30, GETDATE())

GROUP BY Symbol

HAVING SUM(CASE WHEN Side = 'BUY' THEN Quantity ELSE -Quantity END) <> 0

) pos

When NOT to Use OUTER APPLY?

When Simple Joins Suffice - If you're just joining trading tables on a simple

condition without needing row-by-row operations, a regular JOIN is more

readable and often performs better.

1. One of my biggest bandwagons... KISS. Keep It Simple Stupid. If you don't need

it and the simple JOIN will suffice, don't overcomplicate things.  

-- Don't use OUTER APPLY for this:

SELECT s.*, t.LastTradePrice

FROM dbo.Securities s

OUTER APPLY (

SELECT Price as LastTradePrice

FROM dbo.Trades t

WHERE t.Symbol = s.Symbol

) t

-- Use a simple LEFT JOIN instead:

SELECT s.*, t.Price as LastTradePrice

FROM dbo.Securities s LEFT JOIN Trades t

ON s.Symbol = t.Symbol

2. Set-Based Operations Work Better (THE BEST!)

When you can solve the problem with window functions or aggregate queries,

they're usually more efficient.

-- Instead of OUTER APPLY for trade ranking, consider using ROW_NUMBER() with a CTE

WITH RankedTrades AS (

SELECT

Symbol,

TradeID,

Price,

Volume,

TradeDate,

ROW_NUMBER() OVER (PARTITION BY Symbol ORDER BY Volume DESC) AS VolumeRank

FROM dbo.Trades

WHERE TradeDate = CAST(GETDATE() as DATE)

)

SELECT * FROM RankedTrades WHERE VolumeRank <= 10

Performance Concerns with High-Frequency Data OUTER APPLY essentially performs a correlated subquery for each row.

With high-frequency trading data (millions of trades), this can be slower than

alternative approaches without proper indexing. With or without OUTER APPLY,

here are some Performance Tips for Trading Systems: 1. Index critical columns: Symbol, TradeDate, AccountID in your trades table 2. Partition large tables by TradeDate for better performance 3. Consider columnstore indexes for analytical queries on trade history 4. Test with realistic data volumes - what works for 1000 trades might not scale to 10 million 5. Use READ UNCOMMITTED when appropriate for real-time market data queries

And, because you know how much I love the market data...

-- Get bid/ask spread and depth for each symbol

SELECT

s.Symbol,

s.LastPrice,

depth.BestBid,

depth.BestAsk,

depth.Spread,

depth.BidDepth,

depth.AskDepth

FROM dbo.Securities s

OUTER APPLY (

SELECT

MAX(CASE WHEN Side = 'BUY' THEN Price END) AS BestBid,

MIN(CASE WHEN Side = 'SELL' THEN Price END) AS BestAsk,

MIN(CASE WHEN Side = 'SELL' THEN Price END) -

MAX(CASE WHEN Side = 'BUY' THEN Price END) AS Spread,

SUM(CASE WHEN Side = 'BUY' THEN Size ELSE 0 END) AS BidDepth,

SUM(CASE WHEN Side = 'SELL' THEN Size ELSE 0 END) AS AskDepth

FROM dbo.MarketDepth md

WHERE md.Symbol = s.Symbol

AND md.UpdateTime >= DATEADD(SECOND, -1, GETDATE())

) depth

WHERE s.IsActive = 1

OUTER APPLY shines in trading systems when you need row-by-row operations

that reference the outer query, especially for finding recent trades per symbol,

calculating running positions and correlating market events. However, with

high-frequency trading data, always benchmark against window functions and

consider your indexing strategy carefully.

More to read. FROM Clause