Apply Operator in SQL Server

 In SQL Server, the APPLY operator is used to invoke a table-valued function for each row returned by an outer query. The APPLY operator comes in two forms: CROSS APPLY and OUTER APPLY.

[Click on the link below for detail Q & A]

1. CROSS APPLY

• CROSS APPLY returns only those rows from the outer table that produce a result set from the table-valued function. If the function returns no result, the corresponding row from the outer table is not included in the final result set.

2. OUTER APPLY

• OUTER APPLY returns all rows from the outer table, regardless of whether the table-valued function returns a result for that row. If the function returns no result for a row, NULL values are returned for the columns from the table-valued function.

Example Usage

Let's say you have two tables:

1. Employees:

   • EmployeeID
   • EmployeeName

2. Orders:

   • OrderID
   • EmployeeID
   • OrderDate

And a table-valued function GetRecentOrder which takes an EmployeeID as a parameter and returns the most recent order for that employee.

Using CROSS APPLY:

SELECT e.EmployeeID, e.EmployeeName, o.OrderID, o.OrderDate
FROM Employees e
CROSS APPLY dbo.GetRecentOrder(e.EmployeeID) o;

• This query will return only employees who have placed at least one order, showing their most recent order.

Using OUTER APPLY:

SELECT e.EmployeeID, e.EmployeeName, o.OrderID, o.OrderDate
FROM Employees e
OUTER APPLY dbo.GetRecentOrder(e.EmployeeID) o;

• This query will return all employees, including those who haven't placed any orders. For employees without orders, the OrderID and OrderDate fields will be NULL.

When to Use CROSS APPLY vs. OUTER APPLY

• Use CROSS APPLY when you want to include only rows that have corresponding results from the applied function.
• Use OUTER APPLY when you want to include all rows from the outer query, even if there are no corresponding results from the applied function.

Benefits of APPLY

• It provides a way to apply complex logic or filtering on a per-row basis.
• It can be more efficient than using a JOIN in scenarios where you need to correlate rows from the outer query with results from a table-valued function.

This functionality is particularly useful when dealing with subqueries, table-valued functions, or any scenario where you need to filter or calculate results based on individual rows from the outer query.