Derived tables and APPLY

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Use a named inline result and evaluate a related expression per outer row.

Run the example

SELECT c.CustomerID, x.ExampleOrderDate
FROM dbo.Customers AS c
OUTER APPLY (
    SELECT o.OrderDate AS ExampleOrderDate
    FROM dbo.Orders AS o
    WHERE o.CustomerID = c.CustomerID AND o.OrderID = 1
) AS x
WHERE c.CustomerID BETWEEN 1 AND 3
ORDER BY c.CustomerID;

Read it step by step

1. OUTER APPLY evaluates the right-hand query for each customer.
2. The right-hand query returns Order 1 only for Customer 1; it returns no row for Customers 2 and 3.
3. OUTER APPLY keeps all three customers and shows NULL for the two without a matching right-hand row. CROSS APPLY would return only Customer 1.

Check the result

The OrderID = 1 filter is intentionally narrow so the difference between OUTER APPLY and CROSS APPLY is visible. A derived table names a subquery in FROM and is not inherently correlated.

Practice

Use a derived table to show Categories 1–3 with their product counts.

SELECT x.CategoryID, x.ProductCount
FROM (
    SELECT CategoryID, COUNT(*) AS ProductCount
    FROM dbo.Products GROUP BY CategoryID
) AS x
WHERE x.CategoryID BETWEEN 1 AND 3
ORDER BY x.CategoryID;