CROSS APPLY and OUTER APPLY are useful operators in SQL Server for applying table-valued functions to a set of rows. These operators allow you to join each row from the outer query to a set of rows returned by the table-valued function, which can be extremely powerful for returning related data.
CROSS APPLY
CROSS APPLY works like an inner join. It returns only the rows from the outer query for which the table-valued function returns matching rows. If the function returns no rows, the outer row is excluded from the result set.
Syntax Example
-- Assuming you have a function named dbo.GetItemsByCategory
SELECT
c.CategoryName,
i.ItemName
FROM
Categories c
CROSS APPLY
dbo.GetItemsByCategory(c.CategoryID) i;
In this example, GetItemsByCategory is a table-valued function that takes the CategoryID and returns items related to that category. Only categories that have items will be included in the results.
OUTER APPLY
OUTER APPLY, in contrast, behaves like a left join. It returns all the rows from the outer query, along with the rows from the table-valued function. If the function does not return any rows for a particular outer query row, NULL values will be returned for the columns from the function.
Syntax Example
-- Assuming you have a function named dbo.GetItemsByCategory
SELECT
c.CategoryName,
i.ItemName
FROM
Categories c
OUTER APPLY
dbo.GetItemsByCategory(c.CategoryID) i;
In this case, all categories will be listed, and if a category has no items, ItemName will return NULL for that category.
Key Differences
- CROSS APPLY: Returns only those rows from the outer source for which the function returns a result (inner join-like behavior).
- OUTER APPLY: Returns all rows from the outer source, including those without matching rows from the function (left join-like behavior).
Practical Use Cases
- Use CROSS APPLY when you know that you only want rows that produce results from the function, such as when looking for matching entries.
- Use OUTER APPLY when you want to keep all source data from the outer query, even if there are no associated entries in the result from the function.
These operators are especially useful when dealing with hierarchical data or when you want to return additional data that is related to the rows being processed in a single query.