Correlated subqueries

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Calculate a related value separately for each outer row.

Run the example

SELECT TOP (5) p.ProductID,
       (SELECT COUNT(*) FROM dbo.OrderItems AS i
        WHERE i.ProductID = p.ProductID) AS LineCount
FROM dbo.Products AS p
ORDER BY p.ProductID;

Read it step by step

1. The inner WHERE refers to the current outer ProductID.
2. COUNT(*) returns one count for that product, including zero when no line matches.
3. TOP (5) with ORDER BY selects five repeatable products.

Check the result

The correlation changes the inner count for each product.

Practice

Count orders for Customers 1–3 using a correlated subquery.

SELECT c.CustomerID,
       (SELECT COUNT(*) FROM dbo.Orders AS o
        WHERE o.CustomerID = c.CustomerID) AS OrderCount
FROM dbo.Customers AS c
WHERE c.CustomerID BETWEEN 1 AND 3
ORDER BY c.CustomerID;