SQL Server CROSS APPLY and OUTER APPLY

The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. As you might have guessed, the left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set. The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.

The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY. The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.  Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression.  For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

You might be wondering if the same can be achieved with regular JOIN clause then why and when to use APPLY operator? Though the same can be achieved with normal JOIN, the need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query. Let me explain you with help of some examples.

CREATE TABLE [Department](
   [DepartmentID] [int] NOT NULL PRIMARY KEY,
   [Name] VARCHAR(250) NOT NULL,
) ON [PRIMARY]
INSERT [Department] ([DepartmentID], [Name])
VALUES (1, N'Engineering')
INSERT [Department] ([DepartmentID], [Name])
VALUES (2, N'Administration')
INSERT [Department] ([DepartmentID], [Name])
VALUES (3, N'Sales')
INSERT [Department] ([DepartmentID], [Name])
VALUES (4, N'Marketing')
INSERT [Department] ([DepartmentID], [Name])
VALUES (5, N'Finance')
GO
CREATE TABLE [Employee](
   [EmployeeID] [int] NOT NULL PRIMARY KEY,
   [FirstName] VARCHAR(250) NOT NULL,
   [LastName] VARCHAR(250) NOT NULL,
   [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID),
) ON [PRIMARY]
GO
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (1, N'Orlando', N'Gee', 1 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (2, N'Keith', N'Harris', 2 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (3, N'Donna', N'Carreras', 3 )
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
VALUES (4, N'Janet', N'Gates', 3 )
go
---cross apply

create function dbo.fn_GetAllEmployeeOfDepartment(@DeptID AS INT)
Returns Table
as
Return
(
select * from Employee E
where e.DepartmentID=@DeptID)
go
select * from Department D
cross apply dbo.fn_GetAllEmployeeOfDepartment(D.DepartmentID)
go
SELECT * FROM Department D
CROSS APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A
GO

SELECT * FROM Department D
INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID
GO
---outer apply
SELECT * FROM Department D
OUTER APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A
GO
SELECT * FROM Department D
LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID
GO
go
select * from Department D

outer apply dbo.fn_GetAllEmployeeOfDepartment(d.departmentID)