SQL Server CROSS APPLY and OUTER APPLY Helpful Examples

Problem

Microsoft introduced the APPLY operator in SQL Server 2005. Similar to a JOIN, it allows correlation between two table expressions. The key difference between the JOIN and APPLY operators is when you have a table-valued expression on the right side and want to evaluate it for each row from the left table expression. Since they produce similar results, when do you choose an APPLY versus a JOIN?

Solution

In this article, I’ll explore how the APPLY operator joins two table expressions. Also, I’ll show how it differs from regular JOINs. Additionally, we’ll explore a few use cases. By the end, you’ll know when to choose a JOIN or an APPLY operator when crafting scripts.

SQL Server APPLY Operator Variants

The APPLY operator allows you to join two table expressions. SQL processes the right table expression for each row from the left table expression. Similarly, SQL evaluates the left table expression first, and then the right table expression is evaluated against each row of the left table expression for the final result set. Finally, our result set contains all the selected columns from the left table expression followed by all the columns of the right table expression.

Let’s take a minute and review the two types of APPLY operators, and how they relate to JOINs:

• CROSS APPLY
• OUTER APPLY

CROSS APPLY

The CROSS APPLY operator returns rows from the left table expression (in its final output) if it matches the right table expression. In other words, when matched, the right table expression returns rows for the left expression. It’s helpful to think of a CROSS APPLY as equivalent to an INNER JOIN (or, more precisely, it’s like a CROSS JOIN with a correlated sub-query) with an implicit join condition of 1=1. (Arguably, it should have been named INNER APPLY.)

OUTER APPLY

The OUTER APPLY operator returns all the rows from the left table expression irrespective of whether there are matches in the right table expression. For rows with no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression. The OUTER APPLY is equivalent to a LEFT OUTER JOIN.

If you can achieve the same results with a regular JOIN clause, why and when do you use the APPLY operator? Although you can achieve the same with a regular JOIN, the need for APPLY arises if you have a table-valued expression on the right part. Let me explain with some examples.

Video Example of CROSS APPLY and OUTER APPLY

Build Sample Data

The following script creates three tables: a Department table, an Employee table, and a Sales table.

-- https://www.mssqltips.com
USE [tempdb];
GO
DROP TABLE IF EXISTS dbo.Employee, dbo.Department, dbo.Sales;
GO
CREATE TABLE dbo.Department
(
    [DepartmentID] [INT] NOT NULL PRIMARY KEY,
    [Name] VARCHAR(250) NOT NULL,
);
GO
CREATE TABLE dbo.Employee
(
    [EmployeeID] [INT] NOT NULL PRIMARY KEY,
    [FirstName] VARCHAR(250) NOT NULL,
    [LastName] VARCHAR(250) NOT NULL,
    [HireDate] DATE NOT NULL,
    [YearlySalary] DECIMAL(16, 2) NOT NULL,
    [DepartmentID] [INT] NOT NULL
        REFERENCES [Department] (DepartmentID),
);
GO
CREATE TABLE dbo.Sales
(
  EmployeeID int NOT NULL,
  GrossTotal decimal(12,2),
  SaleDate date NOT NULL DEFAULT getdate()
);
GO
INSERT dbo.Department
(
    [DepartmentID],
    [Name]
)
VALUES
(1, N'Engineering'),
(2, N'Administration'),
(3, N'Sales'),
(4, N'Marketing'),
(5, N'Finance');
GO
INSERT dbo.Employee
(
    [EmployeeID],
    [FirstName],
    [LastName],
    [HireDate],
    [YearlySalary],
    [DepartmentID]
)
VALUES
(1, N'Orlando', N'Gee', '01-01-2023', 60000.00, 1),
(2, N'Keith', N'Harris', '01-21-2023', 60000.00, 2),
(3, N'Donna', N'Carreras', '03-01-2021', 82000.00, 3),
(4, N'Janet', N'Gates', '04-01-2022', 90000.00, 3),
(5, N'Bill', N'North', '07-01-2000', 85000.00, 1),
(6, N'Sally', N'Smith', '07-01-2000', 88000.00, 1);
GO
INSERT dbo.Sales
(
  EmployeeID,
  GrossTotal
)
VALUES
(3, 1000),
(3,  500),
(4,  725),
(4,  250),
(5,  250),
(2,   50);

SQL Server CROSS APPLY vs INNER JOIN

The first query below selects data from the Department table and uses a CROSS APPLY to evaluate the Employee table for each record of the Department table. The second query joins the Department table with the Employee table and returns all matching records. Both queries have the same results.

-- https://www.mssqltips.com
-- Query #1
SELECT *
FROM Department D
    CROSS APPLY
   (SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID) A;
GO
-- Query #2
SELECT *
FROM Department D
    INNER JOIN Employee E
        ON D.DepartmentID = E.DepartmentID;
GO

Also, the optimizer picks the same execution plans for both queries, which have an equal query cost, as shown in the screenshot below.

I’ll stick with an INNER JOIN for a simple query that joins tables. Using CROSS APPLY for this job is like using email for file storage. It works, but it’s not what anyone expects when reviewing the query.

SQL Server OUTER APPLY vs LEFT OUTER JOIN

Let’s take a minute and look at another example. The first query in the code block below, selects data from the Department table. It uses an OUTER APPLY to evaluate the Employee table for each record of the Department table. For those rows for which there is no match in the Employee table, SQL returns NULL, as you can see in the screenshots below.

The second query uses a LEFT OUTER JOIN between the Department and Employee tables. As expected, the query returns all rows from the Department table, even for those rows for which there is no match in the Employee table.

-- https://www.mssqltips.com
-- Query #1
SELECT *
FROM Department D
    OUTER APPLY
   (SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentID) A;
GO
-- Query #2
SELECT *
FROM Department D
    LEFT OUTER JOIN Employee E
        ON D.DepartmentID = E.DepartmentID;
GO

Even though the above two queries return the same data, the execution plan is different. Cost-wise, there is little difference. The query with the OUTER APPLY uses a Compute Scalar operator before the Nested Loops operator to evaluate and produce the columns of the Employee table.

The cost difference would not stop me from using OUTER APPLY. But, like the CROSS APPLY, I’ll stick to a LEFT OUTER JOIN if all I’m doing is joining two tables.

Joining Table-valued Functions and Tables Using SQL CROSS APPLY

We’ve looked at examples where we prefer a JOIN to an APPLY. Now, let’s look at an example where the APPLY operator shines.

I’m creating a table-valued function in the script below that accepts DepartmentID as its parameter. Then, it returns the top two employees, based on salary, who belong to that department. The first query selects data from the Department table and uses a CROSS APPLY to join with the function. It passes the DepartmentID for each row from the outer table expression (in our case, the Department table) and evaluates the function for each row, like a correlated subquery.

The next query uses the OUTER APPLY instead of the CROSS APPLY. Unlike the CROSS APPLY, which returns only correlated data, the SQL OUTER APPLY returns non-correlated data, placing NULLs into the missing columns.

-- https://www.mssqltips.com
CREATE OR ALTER FUNCTION dbo.fn_GetTopEmployeeSalary
(
    @DeptID AS INT
)
RETURNS TABLE
AS
RETURN
     (SELECT TOP 2
             CONCAT(LastName, ', ', FirstName) AS EmployeeName,
             YearlySalary
      FROM Employee E
      WHERE E.DepartmentID = @DeptID
      ORDER BY E.YearlySalary DESC);
GO
SELECT D.Name AS DepartmentName,
       E.EmployeeName,
       E.YearlySalary
FROM Department D
    CROSS APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;
GO
SELECT D.Name AS DepartmentName,
       E.EmployeeName,
       E.YearlySalary
FROM Department D
    OUTER APPLY dbo.fn_GetTopEmployeeSalary(D.DepartmentID) E;
GO

Can you use a simple join in place of the above queries? The answer is no. If you replace the CROSS/OUTER APPLY in the above queries with an INNER JOIN/LEFT OUTER JOIN, specifying the ON clause with 1=1, and run the query, you will get the error message below.

This error happens because, with JOINs, the execution context of the outer query differs from the execution context of the function (or a derived table). You can’t bind a value/variable from the outer query to the function as a parameter. You need the APPLY operator for such queries.

In short, queries need the APPLY operator when using a table-valued function, but you can only use it with inline SELECT statements.

Joining Table-valued System Functions and Tables Using APPLY

Let me show you another query using a Dynamic Management Function (DMF).

The script below returns all executing user queries except ones for the current session. As you can see, the sys.dm_exec_requests dynamic management view (DMV) is CROSS APPLY’ed with the sys.dm_exec_sql_text (DMF), which accepts a “plan handle” for the query. Finally, the “plan handle” is passed from the left/outer expression to the function to return results.

USE master;
GO
SELECT DB_NAME(r.database_id) AS [Database],
       st.[text] AS [Query]
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st
WHERE r.session_id > 50 -- Consider spids for users only, no system spids. 
            AND r.session_id NOT IN ( @@SPID ); -- Don't include request from current spid.

Notice for the above query, the [text] column returns all queries submitted in a batch. If you want to see only the active (currently executing) queries, you can use the statement_start_offset and statement_end_offset columns to trim the active part of the query. Refer to this tip: How to isolate the current running commands in SQL Server for a good example.

APPLY where a JOIN would be more complicated

Finally, let’s look at one more case. In some scenarios, we can use an APPLY operator because it’s harder (or maybe even impossible) to achieve the same result with a JOIN. Consider the “top N per group” problem, where we might want to know the top 2 employees, per department, based on sales. (Even though in this case it is contrived because, really, only the sales department should have sales.)

Let’s add a new salesperson with some sales:

INSERT dbo.Employee
(
    [EmployeeID],
    [FirstName],
    [LastName],
    [HireDate],
    [YearlySalary],
    [DepartmentID]
)
VALUES
(7, N'Brett', N'Warner', '02-02-2025', 55000.00, 3);
INSERT dbo.Sales
(
  EmployeeID,
  GrossTotal
)
VALUES
(7, 1000);

The result we ultimately want is to filter out Janet Gates, because she had the lowest sales figure out of the (now) three salespeople:

There are many ways to achieve this result. One is through a join, a subquery, and a window function:

SELECT Name, FirstName, LastName, Sales
FROM
(
  SELECT d.Name, e.FirstName, e.LastName, s.Sales,
      rn = ROW_NUMBER() OVER (PARTITION BY d.DepartmentId ORDER BY s.Sales DESC)
    FROM Employee AS e
   INNER JOIN Department AS d
      ON e.DepartmentID = d.DepartmentID
   INNER JOIN 
   (
     SELECT EmployeeID, Sales = SUM(GrossTotal)
       FROM Sales AS s
      GROUP BY EmployeeID
   ) AS s ON s.EmployeeID = e.EmployeeID
) AS sub
WHERE rn <= 2
ORDER BY Name, rn;

But we can express this more succinctly with an APPLY, which essentially sends every department through a TOP (2) subquery:

SELECT d.Name, e.FirstName, e.LastName, sub.EmployeeID, sub.Sales
  FROM Department AS d
 CROSS APPLY 
 (
   SELECT TOP (2) e.EmployeeID, e.DepartmentID,
                  Sales = SUM(s.GrossTotal)
     FROM Sales AS s
    INNER JOIN Employee AS e 
       ON s.EmployeeID = e.EmployeeID
    WHERE e.DepartmentID = d.DepartmentID
    GROUP BY e.EmployeeID, e.DepartmentID
    ORDER BY Sales DESC
) AS sub
INNER JOIN Employee AS e
ON sub.EmployeeID = e.EmployeeID;

As for performance, as long as we have suitable indexes to support these queries, for example:

CREATE INDEX IX_Employee_Department_Employee
    ON dbo.Employee (DepartmentID, EmployeeID);
CREATE INDEX IX_Sales_Employee
    ON dbo.Sales (EmployeeID)
    INCLUDE (GrossTotal);

The APPLY variation should perform better – particularly at scale – because it allows the optimizer to eliminate both a scan and a sort. Just remember to ignore estimated cost %:

image 2

Summary

I’ve included a summary table below for your convenience:

Other Notes on SQL CROSS APPLY

Remember, the APPLY operator is not an ANSI operator but an extension of T-SQL. Consider this if you plan to port your database to some other DBMS.

Also, a query with an APPLY operator performs better in specific scenarios than a query with regular joins. Here are two articles that discuss this effect in detail.

• INNER JOINS vs CROSS APPLY
• Using CROSS APPLY to optimize joins on BETWEEN conditions

Finally, I invite you to review the slew of comments below. Thank you all for sharing your experiences with the APPLY operator.

Next Steps

• Jared Westover compares the performance of using an APPLY operator or the ROW_NUMBER() function in the article, Return TOP (N) Rows using APPLY or ROW_NUMBER() in SQL Server.
• Aaron Bertrand wrote a short article, Advanced JSON Techniques in SQL Server – Part 3, on reading JSON using CROSS APPLY and the OPENJSON function.
• In the article, Convert OUTER APPLY and CROSS APPLY Joins for Snowflake using LEFT JOINS, Sergey Gigoyan explores alternatives to APPLY in Snowflake.

Last updated by Jared Westover on 2023-12-07 and Aaron Bertrand on 2025-10-10

Arshad Ali

Arshad specializes in Database, Data Warehousing and Business Intelligence application design, development and deployment, at enterprise level, with SQL Server, SSIS, SSRS, SSAS, Service Broker, MDS, DQS, SharePoint and PPS. He is a Microsoft Certified IT Professional in Microsoft SQL Server.  He also has experience developing applications in VB/ASP/.NET/ASP.NET/C#, and is a Microsoft Certified Application Developer and Solution Developer for the .NET platform in Web/Windows/Enterprise.  Arshad has Master’s degrees in Computer Applications and Business Administration in IT.

Disclaimer: Arshad worked for Microsoft and helped people and businesses make better use of technology to realize their full potential. The opinions mentioned in his tips and articles herein are solely his and do not reflect those of his current or previous employers.

• MSSQLTips Awards: Champion (100+ tips) – 2014