CROSS APPLY and OUTER APPLY

SQL Server

SQL Server has two apply operators available. These two operators are the CROSS APPLY and OUTER APPLY. At first glance, it can seem that an APPLY and JOIN are the same. However, joins are used to combine two results sets together whereas the apply operator evaluates an expression against every row from a table or view.

Two common uses in SQL for the CROSS and OUTER APPLY are:

  • Using APPLY with table functions.
  • Using APPLY with subqueries.

Unlike joins the apply operator can also be used with table functions.

For example, a CROSS APPLY can be used with the sys.dm_exec_sql_text function to retrieve the SQL for any running processes.

SELECT T.Text AS [SQL_Text] ,P.*
FROM sys.sysprocesses AS [P]
CROSS APPLY sys.dm_exec_sql_text(P.sql_handle) AS [T]

The OUT APPLY is like a left join. All the rows from the left table will be in the result. When no result is returned from the right, the value is NULL.

The CROSS APPLY is like an inner join in that a row is only include in the results when a row from the left gets a return value from the function or subquery on the right.

Example Content

Here is some example data and inline function

  • SalesPerson – list of sales people.
  • SalesHistory – list of each sale and date for each sales person.
  • SalesTotalForPerson – Takes the salesperson id and month and returns sum sales for that month.

CREATE OR ALTER FUNCTION dbo.SalesTotalForPerson  
(	
    @PersonId AS INT
    ,@MonthInt AS INT
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT SUM(Amount) AS TotalAmount
	FROM dbo.SalesHistory
	WHERE SalesPersonId = @PersonId 
	  AND DATEPART(MONTH,SalesDate)= @MonthInt
	GROUP BY  SalesPersonId, DATEPART(MONTH,SalesDate)
)

HOW TO USE CROSS APPLY

Using CROSS APPLY with a inline function:

DECLARE @MonthInt AS INT = 10 --October
SELECT * FROM dbo.SalesPerson AS [SP] CROSS APPLY dbo.SalesTotalForPerson( SP.Id, @MonthInt )

Using cross apply with a subquery. In this example we can use cross apply with a count(*) to get the number of sales for each salesperson.

SELECT * FROM dbo.SalesPerson AS [SP]
CROSS APPLY (
     SELECT COUNT(*) AS [TotalSalesCount]
        FROM dbo.SalesHistory
        WHERE SalesPersonId = Sp.ID
) AS [SH]

HOW TO USE OUTER APPLY

The OUTER APPLY works the same way as the CROSS APPLY. However, all rows from the dbo.SalesPerson table are included in the result regardless if a value is returned form the SalesTotalForPerson function.

DECLARE @MonthInt AS INT = 10 --October
SELECT * FROM dbo.SalesPerson AS [SP] OUTER APPLY dbo.SalesTotalForPerson( SP.Id, @MonthInt )