How to use CROSS APPLY and OUTER APPLY Operators in SQL Server

The CROSS APPLY and OUTER APPLY Operators in SQL Server

The APPLY operator is similar to a Join clause and it allows to Join two table expressions like Joining a Left/Outer table expression with a Right/Inner table expression.

We mainly use the APPLY operator when we have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression.
The primary use of Apply operator in SQL Server is to invoke a table valued function (TVF) but it can also be used in some other important scenarios.

Apply Operator executes for each row of left expression and finds the matches in right expression and then returns the result set as per it’s type; Cross or Outer Apply.

SQL Server APPLY operator has two variants; CROSS APPLY and OUTER APPLY

CROSS APPLY :

The CROSS APPLY operator is similar to INNER JOIN which returns only those rows from the outer left table expression if it matches with the right table expression. In other words, the right table expression returns rows for left table expression based on match condition specified in the Where clause. 

The result of CROSS APPLY doesn’t contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN.

Example :

Suppose we have the two Tables as follows, on which we apply APPLY operators.

Select * From [dbo].[Tbl_Emp]

Select * From [dbo].[Tbl_Dept]

Now will write a simple Query with Inner Join , and Cross Apply ; both will return the same result and the execution plans for these queries are similar and they have an equal query cost.

--Inner Join Query

Select E.*, D.DeptName
From [dbo].[Tbl_Emp] As E  
 Inner Join
 [dbo].[Tbl_Dept] As D 
 ON  E.Dept_Id=D.DeptId  

--Cross Apply Query

Select * From [dbo].[Tbl_Emp] As E  
CROSS  APPLY 
(Select D.DeptName  From [dbo].[Tbl_Dept] As D Where E.Dept_Id=D.DeptId) S1

Result :

OUTER APPLY :

The OUTER APPLY operator is similar to LEFT OUTER JOIN which returns all the rows from the left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression.

Example:

Now will write a simple Query with Left Join , and Outer Apply ; both will return the same result and the execution plans for these queries are similar and they have an equal query cost.

--Left Join Query

Select E.*, D.DeptName
From [dbo].[Tbl_Emp] As E  
 Left Join
 [dbo].[Tbl_Dept] As D 
 ON  E.Dept_Id=D.DeptId  

--Outer Apply Query

Select * From [dbo].[Tbl_Emp] As E  
OUTER  APPLY 

(Select D.DeptName  From [dbo].[Tbl_Dept] As D Where E.Dept_Id=D.DeptId) S1

Result :

APPLY with User Define function:
We can perform APPLY operation with a function that may be scalar or table valued function. This function will invoke each row and return result that will be associated with the outer table.
Now we will create a table-valued function which accepts Department ID as its parameter and returns all the Employee details who belong to this department. 

IF EXISTS (Select * From Sys.Objects 

Where Object_Id = Object_Id (N'[fn_GetEmpDtlsByDeptID]') AND Type IN (N'IF'))
Begin
   Drop Function dbo.fn_GetEmpDtlsByDeptID
End
GO

Notes :

There are several types of 'Object_Id' to be checked for functions.
FN = SQL scalar function
IF = SQL Inline Table-Valued Function
TF = SQL Table-Valued Function
FT = Assembly (CLR) Table-Valued Function

Create Function dbo.fn_GetEmpDtlsByDeptID(@DeptID AS INT)
Returns TABLE
AS
Return
     (
        Select D.DeptName From [dbo].[Tbl_Dept] As D Where D.DeptId= @DeptID
      )
GO

Now the following query selects data from the Employee table and uses a CROSS APPLY to Join with the Function we created above. It passes the DepartmentID for each row from the outer table expression (in our case Employee table) and evaluates the function for each row like a Correlated Subquery.

Select * From [dbo].[Tbl_Emp] E 
CROSS APPLY dbo.fn_GetEmpDtlsByDeptID(E.Dept_Id)
GO

Result:

Now the following query uses the OUTER APPLY in place of the CROSS APPLY and hence unlike the CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing values.

Select * From [dbo].[Tbl_Emp] E 
OUTER  APPLY dbo.fn_GetEmpDtlsByDeptID(E.Dept_Id)
GO

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------