SQL Server Interview Question #177

What is OUTER APPLY?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

OUTER APPLY is similar to CROSS APPLY, but it preserves rows from the left input even when the right-side table expression returns no rows. In that case, right-side columns are NULL.

Its row-preservation behavior is therefore analogous to LEFT JOIN while retaining APPLY's ability to evaluate a correlated table expression per left row.

Code Example

SELECT c.Id,
       c.Name,
       p.Name AS MostExpensiveProduct
FROM dbo.Categories AS c
OUTER APPLY
(
    SELECT TOP (1) Name
    FROM dbo.Products AS p
    WHERE p.CategoryId = c.Id
    ORDER BY p.Price DESC, p.Id
) AS p;