Outer / Cross Apply

SQL Server 2005 introduced the APPLY operator.

SQL Server APPLY operator has two variants; CROSS APPLY and OUTER APPLY

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

You might be wondering if the same can be achieved with a regular JOIN clause, so why and when do you use the APPLY operator? Although the same can be achieved with a normal JOIN, the need of APPLY arises if you have a table-valued expression on the right part and in some cases the use of the APPLY operator boosts performance of your query.

SELECT * FROM Department D 
CROSS APPLY 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = D.DepartmentID 
   ) A 
GO

SELECT * FROM Department D 
OUTER APPLY 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = D.DepartmentID 
   ) A 
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 

SELECT 
	  pr.name
	, pa.name
FROM   
    sys.procedures pr
    OUTER APPLY (
		    SELECT TOP 2 *
                    FROM sys.parameters pa
                    WHERE pa.object_id = pr.object_id
                    ORDER BY pr.name
		) pa
ORDER BY 
	  pr.name
	, pa.name            

Sources:

https://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/