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
WHERE t.Symbol = s.Symbol
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
SUM(Price * Volume) / NULLIF(SUM(Volume), 0) AS VWAP,
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
WHERE t.Symbol = s.Symbol
AND t.TradeDate >= DATEADD(HOUR, -1, GETDATE()) -- Last hour
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.
