SQL Server CROSS APPLY and OUTER APPLY

The APPLY operator allows us to invoke a table-valued function for each row returned by an outer table expression of a query. The APPLY operator allows us to join two table expressions; the right table expression is processed every time for each row from the left table expression. 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 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 allows you 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 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.  

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

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 

Apply with table valued function

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = @DeptID 
   ) GO SELECT * FROM Department D CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO SELECT * FROM Department D OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO