SQL Server Interview Question #176

What is CROSS APPLY?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

CROSS APPLY evaluates a table expression for each row from the left input and returns only left rows for which the right-side expression produces rows.

It is particularly useful with table-valued functions, correlated TOP queries, JSON/XML processing, and selecting a small related set per parent row. It resembles an INNER JOIN in its row-preservation behavior but supports correlated table expressions naturally.

Code Example

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