The APPLY operator in SQL Server is used to invoke a table-valued function for each row of a table or a query result set. It enables you to join the results of a table-valued function with the rows of a table, allowing you to include the output of the function as part of your result set.
There are two types of APPLY operators:
1. CROSS APPLY: This is similar to an INNER JOIN. It returns only the rows from the left table (or query) where the function invocation returns a row.
2. OUTER APPLY: This is similar to a LEFT JOIN. It returns all rows from the left table (or query), along with any matching rows from the function invocation. If no matching rows are returned from the function, NULL values are generated for the columns of the function's result set.
Syntax
Here is the basic syntax for using APPLY:
SELECT
t1.Columns,
t2.* -- Columns from the table-valued function
FROM
Table1 t1
CROSS APPLY
TableValuedFunction(t1.KeyColumn) t2
Example Usage
Assume we have a table Employees and a table-valued function GetEmployeeProjects that returns projects for a given employee ID.
Table Creation
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50)
);
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY,
EmployeeID INT,
ProjectName VARCHAR(50),
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
Table-Valued Function
Here's a sample definition of a table-valued function:
CREATE FUNCTION GetEmployeeProjects
(@EmployeeID INT)
RETURNS TABLE
AS
RETURN (
SELECT ProjectID, ProjectName
FROM Projects
WHERE EmployeeID = @EmployeeID
);
Using CROSS APPLY
To retrieve each employee along with their projects, you can use the CROSS APPLY operator like this:
SELECT
e.EmployeeName,
p.ProjectName
FROM
Employees e
CROSS APPLY
GetEmployeeProjects(e.EmployeeID) p;
This query selects the employee names and their associated projects. If there are employees with no projects, they will not be included in the result set.
Using OUTER APPLY
If you want to include all employees, even those without projects, use OUTER APPLY:
SELECT
e.EmployeeName,
p.ProjectName
FROM
Employees e
OUTER APPLY
GetEmployeeProjects(e.EmployeeID) p;
In this case, employees without any projects will still be listed, but the ProjectName will be NULL for those rows.
Summary
- Use CROSS APPLY when you only want rows from the left table with matching results from the function.
- Use OUTER APPLY when you want all rows from the left table and optionally matching rows from the function, filling in NULLs where there are no matches.
- APPLY is particularly useful for hierarchical data or scenarios where you need to compute a result based on each row in another result set.