Using APPLY Operator

The 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. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

The Apply operator is similar to that of Joins but has no conditions in the query.

There are two forms of Apply operator:

• Cross Apply
  The CROSS APPLY operator returns rows from the primary (outer) table only if the table-value function produces a result set. It's similar to that of Inner Join or Cross Join.
• Outer Apply
  The OUTER APPLY, on the other hand, returns all rows from the outer table, even if the function produces no results. Thus, it generates NULL values for those records whose result set is not returned. It's similar to that of Outer Join.

Consider an example below to demonstrate Apply operator:

GO

CREATE TABLE [dbo].[Departments](
 [DepartmentId] [int] NOT NULL PRIMARY KEY,
 [DeparttmentName] [varchar](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, 'Gardening')


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

In above example we can see list of Departments and Employees related to departments (if any) in appropriate tables respectively.

As mentioned above, Apply operator works with table-values function (also known as TVF), consider the below example of it.

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.