CROSS APPLY and OUTER APPLY in Everyday Queries

Most people meet CROSS APPLY as a way to call a table-valued function for each row, but it solves several everyday SQL problems. It can name an expression once, turn columns into rows, and read DMV function output. OUTER APPLY keeps left rows when the right side returns none.

Read CROSS APPLY as a Per-Row Table

APPLY evaluates a table expression using values from the row on its left. CROSS APPLY returns only left rows for which the right expression returns rows. OUTER APPLY keeps every left row and fills right-side columns with NULL when the expression returns no rows. That is similar to the difference between INNER and LEFT JOIN, but the right side can refer to left-side columns directly.

I reach for APPLY when a plain JOIN would require an awkward correlated subquery. Which row from the left side does the function or expression need? If the answer is none, a normal JOIN can be simpler.

Call a Table-Valued Function per Row

Suppose dbo.OrderLinesForCustomer is an inline table-valued function that accepts a customer ID. CROSS APPLY returns line rows for customers with matching output. OUTER APPLY also returns customers with no lines. The function and sample table names are placeholders for an existing schema.

SELECT c.CustomerID, x.OrderID, x.LineAmount
FROM dbo.Customers AS c
CROSS APPLY dbo.OrderLinesForCustomer(c.CustomerID) AS x;
SELECT c.CustomerID, x.OrderID, x.LineAmount
FROM dbo.Customers AS c
OUTER APPLY dbo.OrderLinesForCustomer(c.CustomerID) AS x;

An inline function can be optimized with the outer query. A multi-statement function can have different cardinality and cost behavior, so inspect its actual plan. APPLY is not a promise of speed; it is a way to express the relationship. Check row counts and indexes on the underlying tables.

Name an Expression Once With CROSS APPLY

A derived value used in SELECT and WHERE can be named through a one-row VALUES expression. The optimizer can still transform the expression, but the code has one readable definition. The example normalizes an email address before filtering and projecting. In production, think about collation, whitespace, and whether a persisted computed column or index is warranted.

SELECT c.CustomerID, x.normalized_email
FROM dbo.Customers AS c
CROSS APPLY
(
    VALUES (LOWER(LTRIM(RTRIM(c.EmailAddress))))
) AS x(normalized_email)
WHERE x.normalized_email LIKE N'%@example.com';

This predicate has a leading wildcard and is not a selective index seek on EmailAddress. APPLY does not fix that. The benefit here is avoiding the same normalization expression in several places. If the query is hot, redesign the search path and measure it.

Unpivot a Few Columns With CROSS APPLY

When a row has several related columns, a VALUES list inside APPLY can turn them into named rows without a large UNPIVOT statement. The example emits home, work, and mobile phone numbers for each customer. The WHERE clause drops the NULL numbers.

SELECT c.CustomerID, p.phone_kind, p.phone_number
FROM dbo.Customers AS c
CROSS APPLY
(
    VALUES (N'Home', c.HomePhone),
           (N'Work', c.WorkPhone),
           (N'Mobile', c.MobilePhone)
) AS p(phone_kind, phone_number)
WHERE p.phone_number IS NOT NULL;

VALUES needs one common type across its rows, so cast columns explicitly if types or lengths differ. I always test a customer with no phone numbers at all. In this query that customer disappears. Switching to OUTER APPLY alone does not save it, because the VALUES list always returns three rows and the WHERE clause removes all three.

Read Query Text From DMVs

Dynamic management functions accept a handle from each DMV row. APPLY makes the call per row and exposes the returned text or plan columns. For active requests, sys.dm_exec_sql_text can return the batch text; for cached plans, sys.dm_exec_query_plan can return XML. OUTER APPLY keeps a request row if metadata is unavailable. Filter on is_user_process rather than session_id > 50, because background tasks use session IDs above 50 on current versions.

SELECT r.session_id, r.status, r.command,
       t.text AS batch_text, p.query_plan
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
  ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS p
WHERE s.is_user_process = 1;

Use the required server-state permission and avoid dumping sensitive query text into public reports. Plans and SQL text can contain literals. A live request can end between observation and follow-up, so capture the session, time, and relevant text during the incident.

Keep the No-Match Semantics Visible

A common bug appears when OUTER APPLY is followed by a WHERE predicate on the right-side column. WHERE x.OrderID IS NOT NULL removes the no-match rows and makes it behave like CROSS APPLY for that test. Put the filter inside the right expression if you need to preserve left rows, or choose CROSS APPLY explicitly if no-match rows should disappear. Here is the phone query again, now keeping the customer with no numbers.

SELECT c.CustomerID, p.phone_kind, p.phone_number
FROM dbo.Customers AS c
OUTER APPLY
(
    SELECT v.phone_kind, v.phone_number
    FROM (VALUES (N'Home', c.HomePhone),
                 (N'Work', c.WorkPhone),
                 (N'Mobile', c.MobilePhone)) AS v(phone_kind, phone_number)
    WHERE v.phone_number IS NOT NULL
) AS p;

I compare a customer with matches and one without whenever I change APPLY. Then I check duplicates: a right expression returning three rows produces three output rows for one left row. That is expected and can inflate later sums if the query's grain is misunderstood. APPLY gives a compact syntax, while the data contract still determines correctness.

Use APPLY for Top-One per Parent

A common practical case is the latest order for each customer. OUTER APPLY can run a TOP (1) query ordered by date and a unique tie-breaker, preserving customers without orders. With an index on (CustomerID, OrderDate DESC, OrderID DESC), the right side can use a narrow seek. Without a supporting index, it can repeat expensive work for every customer. Compare it with a window-function design at production scale.

SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM dbo.Customers AS c
OUTER APPLY
(
  SELECT TOP (1) OrderID, OrderDate
  FROM dbo.Orders AS o
  WHERE o.CustomerID = c.CustomerID
  ORDER BY OrderDate DESC, OrderID DESC
) AS o;

The ORDER BY is essential. TOP without it selects an arbitrary row, and a date without a unique tie-breaker can switch between equal-date orders. A customer with no orders gets NULL right-side values under OUTER APPLY.

Compare a Join and APPLY on the Same Grain

APPLY can be syntactic sugar that the optimizer transforms into a join, or it can drive a genuinely correlated operation. Read the actual plan and logical reads rather than assuming a per-row function always executes exactly once in the physical plan. If a VALUES expression is deterministic and cheap, SQL Server can inline it. If a multi-statement function returns many rows, estimates and repeated work can dominate.

I test output grain before timing: one customer can expand to many rows from a function or unpivot. Aggregating afterward without the right grouping can double-count values. A clear table alias and explicit column names make the expansion visible.

Watch Later Joins After OUTER APPLY

When OUTER APPLY returns NULLs on the right, a later INNER JOIN to a right-side key removes those preserved left rows. The query still says OUTER, but the join after it quietly undoes that. Make the later join a LEFT JOIN too, or move it inside the applied expression. Then test a customer with no related data. That small syntax choice decides whether a report is complete.

Related reading on this blog: How to Join a Table Valued Function with a Database Table and Modern Explicit JOIN Syntax: A Brief Note.

APPLY is not a join spelling trick, it is a way to evaluate a right side for each left row.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.