CROSS APPLY and OUTER APPLY are operators used in SQL Server and some other databases to join a table (or derived table) with a function or a subquery that returns a table. They can be particularly useful for working with table-valued functions or when you have a derived table that depends on columns from the outer query.
Here's a brief overview of the two operators, followed by examples for each.
CROSS APPLY
- CROSS APPLY works similarly to an INNER JOIN. It returns only the rows from the outer table that have matching rows in the inner table (the result of the applied function or subquery).
- If the inner table returns no rows for a given row in the outer table, that row will not be included in the result.
Example of CROSS APPLY
Suppose you have a table called Employees and a table-valued function called GetEmployeeProjects, which returns a list of projects for each employee:
CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(100)
);
-- Assuming GetEmployeeProjects returns projects for a specific EmployeeID
SELECT e.EmployeeID, e.EmployeeName, p.ProjectName
FROM Employees e
CROSS APPLY GetEmployeeProjects(e.EmployeeID) p;
In this example, only employees with associated projects will be returned.
OUTER APPLY
- OUTER APPLY works like a LEFT JOIN. It returns all rows from the outer table, and if there are no matching rows in the inner table, it will return NULLs for the columns from the inner table.
Example of OUTER APPLY
Continuing with the previous example, if you want to retrieve all employees regardless of whether they have associated projects, you can use OUTER APPLY:
SELECT e.EmployeeID, e.EmployeeName, p.ProjectName
FROM Employees e
OUTER APPLY GetEmployeeProjects(e.EmployeeID) p;
In this case, all employees will be listed. If an employee has no projects, the ProjectName will be NULL.
Summary of Differences
- CROSS APPLY: Only returns rows from the outer table where there is a match in the inner query. It's equivalent to an INNER JOIN.
- OUTER APPLY: Returns all rows from the outer table and matches them with the inner query results. If there's no match, it returns NULLs for the inner table's columns. It's equivalent to a LEFT JOIN.
When to Use APPLY
- Use CROSS APPLY when you want to filter the results based on matches with the inner result set.
- Use OUTER APPLY when you need to see all results from the outer table, regardless of whether there are matching rows in the inner query.
Both of these operators are useful for working with table-valued functions, subqueries that return rows, and situations where you need to dynamically join data based on the outer query context.