How do you use the APPLY operator with CROSS APPLY and OUTER APPLY in conjunction with table-valued functions?
NickCrt
Posted by NickCrt
Last Updated: August 04, 2024
APPLY Functions
The APPLY operator in SQL Server is used to invoke a table-valued function for each row returned by a query. There are two types of APPLY: CROSS APPLY and OUTER APPLY. Understanding the differences between them and their usage with table-valued functions is crucial for effective database querying.
CROSS APPLY
CROSS APPLY operates similarly to an inner join, meaning that it only returns rows from the outer query that have matching rows in the table-valued function. If there are no matching rows from the function, that row from the outer query will not be included in the result set.
Example:
Assume you have a table of Employees and a table-valued function GetEmployeeDepartments that returns departments for a given employee ID. 1. Table-Valued Function: 
CREATE FUNCTION GetEmployeeDepartments (@EmployeeID INT)
   RETURNS TABLE
   AS
   RETURN (
       SELECT DepartmentName
       FROM Departments
       WHERE EmployeeID = @EmployeeID
   )
2. Using CROSS APPLY: 
SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
   FROM Employees e
   CROSS APPLY GetEmployeeDepartments(e.EmployeeID) d
In this case, only employees who are assigned to any departments will be included in the results.
OUTER APPLY
OUTER APPLY is similar to a left join. It returns all rows from the outer query and, where there are no corresponding rows from the table-valued function, it will return NULL for the columns of the result from the function.
Example:
Using the same scenario as above, if you want to return all employees even if they do not belong to any department, you would use OUTER APPLY. 1. Using OUTER APPLY: 
SELECT e.EmployeeID, e.EmployeeName, d.DepartmentName
   FROM Employees e
   OUTER APPLY GetEmployeeDepartments(e.EmployeeID) d
In this case, all employees will be listed, and for those who do not belong to any departments, DepartmentName will be NULL.
Summary
- CROSS APPLY: Works like an inner join; returns rows from the outer query that have matching rows from the table-valued function. - OUTER APPLY: Works like a left join; returns all rows from the outer query, with NULL values where there are no corresponding rows from the function.
When to Use
- Use CROSS APPLY when you only care about rows with matching results from the function. - Use OUTER APPLY when you want all rows from the outer query and are okay with having NULL for those that don't match any results from the function. This flexibility in using APPLY makes it a powerful tool for working with table-valued functions in SQL Server.
Default Image