APPLY OPERATOR IN T-SQL

APPLY operator is used in the FROM clause of a query. It allows you to call a Table valued function for each row of your outer TABLE. We can pass outer table's columns as function arguments.

It has two options:

1. CROSS APPLY, and
2. OUTER APPLY

CROSS APPLY will not return the outer tables row if function table has no row corresponding to it, whereas OUTER APPLY returns NULL values instead of function columns.

Let's take an example.

First I will make a table valued function and then I will try to show you how to apply the "APPLY" operator.

USE [AdventureWorks]
GO
/****** Object:  UserDefinedFunction [dbo].[fnGetDepartmentId]    Script Date: 07/15/2012 21:54:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGetDepartmentId] (@empid int)
RETURNS TABLE
AS
RETURN
(
--Outer Query
SELECT D.DepartmentID,D.Name 
FROM [AdventureWorks].[HumanResources].[EmployeeDepartmentHistory] EDH
INNER JOIN [AdventureWorks].[HumanResources].[Department] D
ON EDH.DepartmentID=D.DepartmentID
WHERE Employeeid=@empid
) 


This function will return the department name and its ID according to the each employee ID.


Now I will use the APPLY operator to get my required results.


SELECT E.EmployeeID,E.Title,DepartmentID,Name AS DepartmentName

FROM [AdventureWorks].[HumanResources].[Employee] E
CROSS APPLY
dbo.fnGetDepartmentId(E.EmployeeID)