In SQL Server, there are two operators that can be used to join two table expressions: JOIN and APPLY. While they may produce similar results, there are key differences between them. In this article, we will explore the APPLY operator and compare it to regular JOINs. We will also discuss when to use each operator and provide some use cases.

CROSS APPLY vs INNER JOIN

The CROSS APPLY operator is used when there is no easy join condition and the right table is an expression or a table-valued function. It is similar to an INNER JOIN, as it requires rows to exist in both tables for the results to be returned. On the other hand, if the same results can be achieved with an INNER JOIN, it is recommended to use the INNER JOIN instead of CROSS APPLY.

Here is an example of using CROSS APPLY:

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

And here is an example of using INNER JOIN:

SELECT L.Name,
       R.Name
FROM LeftTable L
    INNER JOIN RightTable R
        ON R.ReferenceId = L.Id;

OUTER APPLY vs LEFT OUTER JOIN

The OUTER APPLY operator is used when there is no easy join condition and the right table is an expression or a table-valued function. It is similar to a LEFT OUTER JOIN, as it returns all rows from the left table expression, even if there are no matches in the right table expression. If the same results can be achieved with a LEFT OUTER JOIN, it is recommended to use the LEFT OUTER JOIN instead of OUTER APPLY.

Here is an example of using OUTER APPLY:

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

And here is an example of using LEFT OUTER JOIN:

SELECT L.Name,
       R.Name
FROM LeftTable L
    LEFT OUTER JOIN RightTable R
        ON R.ReferenceId = L.Id;

Using APPLY with Table-valued Functions and Tables

The APPLY operator is particularly useful when joining table-valued functions with tables. It allows you to pass values from the outer query to the function as parameters. Here is an example:

SELECT D.Name AS DepartmentName,
       E.EmployeeName,
       E.YearlySalary
FROM Department D
    CROSS APPLY dbo.GetTopEmployeeSalary(D.DepartmentID) E;

In this example, the GetTopEmployeeSalary function returns the top two employees based on salary for each department. The APPLY operator allows us to evaluate the function for each row in the Department table.

Using APPLY with Table-valued System Functions and Tables

The APPLY operator can also be used with table-valued system functions, such as dynamic management functions (DMFs). These functions provide information about the SQL Server instance. Here is an example:

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
    AND r.session_id NOT IN (@@SPID);

In this example, we are using the CROSS APPLY operator to join the sys.dm_exec_requests DMV with the sys.dm_exec_sql_text DMF. This allows us to retrieve the executing user queries, excluding the current session.

Overall, the APPLY operator provides a powerful tool for joining table expressions in SQL Server. It is particularly useful when working with table-valued functions and system functions. By understanding the differences between JOIN and APPLY, you can choose the appropriate operator for your specific scenario.

Thank you for reading this article. If you have any questions or comments, please feel free to leave them below.

Article Last Updated: 2023-12-07