Apply operator available in SQL Server 2005 onwards is used to invoke a table-valued function for each row returned by an outer table expression of a query. It has two forms i.e. CROSS APPLY and OUTER APPLY.

Let's say we have 2 tables i.e. Departments and Employees. So here we use table valued function to get data from outer table expression based on argument from left input table. Now based on type of Apply operator used it returns the final output.

Consider following example:

    GO

CREATE TABLE [dbo].[<span class="IL_AD" id="IL_AD12">Departments</span>](
 [DepartmentId] [int] NOT NULL PRIMARY KEY,
 [DeparttmentName] [<span class="IL_AD" id="IL_AD9">varchar</span>](50) NULL,
);

GO

INSERT INTO [dbo].[Departments] VALUES(1, 'HR')
INSERT INTO [dbo].[Departments] VALUES(2, 'Marketing')
INSERT INTO [dbo].[Departments] VALUES(3, 'Finance')
INSERT INTO [dbo].[Departments] VALUES(4, 'R&D')
INSERT INTO [dbo].[Departments] VALUES(5, 'Training')
INSERT INTO [dbo].[Departments] VALUES(6, '<span class="IL_AD" id="IL_AD7">Gardening</span>')


GO

CREATE TABLE [dbo].[Employees](
 [EmployeeId] [int] NOT NULL PRIMARY KEY,
 [EmployeeName] [varchar](50) NULL,
 [DepartmentId] [int] NULL REFERENCES [dbo].[Departments],
);

GO

INSERT INTO [dbo].[Employees] VALUES(1, 'Jeet', 1)
INSERT INTO [dbo].[Employees] VALUES(2, 'Sagar', 2)
INSERT INTO [dbo].[Employees] VALUES(3, 'Chirag', 2)
INSERT INTO [dbo].[Employees] VALUES(4, 'Jayesh', 3)
INSERT INTO [dbo].[Employees] VALUES(5, 'Ketan', 4)
INSERT INTO [dbo].[Employees] VALUES(6, 'Rikin', 4)
INSERT INTO [dbo].[Employees] VALUES(7, 'Pratik', 6)

GO

TVF containing outer table expression:

GO
CREATE FUNCTION [dbo].[GetEmployees]
(
 @DepartmentId int
)
RETURNS
@ResultSet TABLE
(
 EmployeeId int,
 EmployeeName varchar(20)
)
AS
BEGIN
 INSERT INTO @ResultSet
  SELECT EmployeeId,EmployeeName
  FROM Employees
  WHERE DepartmentId = @DepartmentId

 RETURN
END

GO

Using CROSS APPLY

SELECT * FROM Departments
 CROSS APPLY GetEmployees(Departments.DepartmentId)

The above query returns the rows with employees assigned to departments, whereas the department wherein the employees are not assigned does not appear. Thus we can see the department "Training" is missing.

Using OUTER APPLY

SELECT * FROM Departments
 OUTER APPLY GetEmployees(Departments.DepartmentId)

The above query returns all the rows with the outer table, whether or not the function returns data for a specific row. Thus we can see that employee value of "Training" department has NULL values.

Published under:  ·  ·  ·  ·