CROSS APPLY and OUTER APPLY

October 14, 2026

The APPLY operator in SQL Server lets you join a table with a table-valued function or a correlated subquery. Key points and examples:

CROSS APPLY:
- CROSS APPLY works like an INNER JOIN. It returns rows from the left table only if there is a matching row in the right table expression.

Example:

SELECT L.Name, R.Name
FROM #LeftTable L
CROSS APPLY (
   SELECT Name
    FROM #RightTable R
    WHERE R.ReferenceId = L.Id
) R;

OUTER APPLY:
- OUTER APPLY works like a LEFT JOIN. It returns all rows from the left table, and matching rows from the right table expression. If there is no match, the result is NULL on the right side.

Example:

SELECT L.Name, R.Name
FROM #LeftTable L
OUTER APPLY (
    SELECT Name
   FROM #RightTable R
   WHERE R.ReferenceId = L.Id
) R;

When to Use APPLY:
Use CROSS APPLY when you need to join a table with a table-valued function or a correlated subquery and only want rows where there is a match.
Use OUTER APPLY when you need to include all rows from the left table, regardless of whether there is a match in the right table expression
Performance Considerations:
While most queries that use CROSS APPLY can be rewritten using an INNER JOIN, CROSS APPLY can yield better execution plans and performance in certain scenarios. This is because CROSS APPLY can limit the set being joined before the join occurs
When to Use APPLY:
Use APPLY when you need to join with a table-valued function or a correlated subquery that needs to be evaluated for each row of the left input
Use JOIN when you are combining rows from two or more tables based on a static relationship between them

Other uses for APPLY:

Splitting Strings:
- You can use APPLY to split strings into multiple rows. For example, if you have a table with a column containing comma-separated values, you can use a table-valued function to split these values and CROSS APPLY to join the results with the original table
Hierarchical Data:
- APPLY is useful for working with hierarchical data, such as organizational charts or file directory structures. You can use OUTER APPLY to recursively retrieve parent-child relationships and build hierarchical queries
Pivoting Data:
When pivoting data, you can use APPLY to dynamically generate columns based on the values in a table. This is useful for creating dynamic pivot tables where the number of columns is not known in advance

Example

Here is an example of using APPLY with a table-valued function:

Using CROSS APPLY with a Table-Valued Function

-- Define a table-valued function
CREATE FUNCTION dbo.GetEmployeeProjects (@EmployeeID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT ProjectName
    FROM Projects
    WHERE EmployeeID = @EmployeeID
);

-- Use CROSS APPLY to join with the table-valued function
SELECT e.EmployeeName, p.ProjectName
FROM Employees e
CROSS APPLY dbo.GetEmployeeProjects(e.EmployeeID) p;

From my SQL Server DBA handbook.

T-SQL

Clive Roberts

Example

Using CROSS APPLY with a Table-Valued Function