SQL SERVER – How to Write Correlated Subquery?

A Correlated Subquery references the outer query. I use a stock-item comparison after the earlier single-table aggregate lesson.

-- Original WideWorldImporters example:
SELECT sih.StockItemID, sih.QuantityOnHand, sih.LastCostPrice
FROM Warehouse.StockItemHoldings AS sih
WHERE sih.LastCostPrice >
 (SELECT AVG(ol.UnitPrice) FROM Sales.OrderLines AS ol
  WHERE ol.StockItemID = sih.StockItemID);

-- Standalone department example:
DECLARE @Employees TABLE (EmployeeNumber int, Department int, Salary decimal(10,2));
INSERT @Employees VALUES (1,10,100),(2,10,200),(3,20,300),(4,20,500);
SELECT emp.EmployeeNumber
FROM @Employees AS emp
WHERE emp.Salary > (SELECT AVG(other.Salary) FROM @Employees AS other
                    WHERE other.Department = emp.Department);

The inner query restricts average sale price to the outer StockItemID. It compares purchase cost with that item-specific average. No matching sales produce NULL from AVG. The comparison then does not qualify the item.

The department example asks for salary above an employee’s department average. I attributed that pattern to Wikipedia. The disposable sample returns employee numbers 2 and 4.

Correlation doesn’t establish that the inner query executes independently once per row. It also doesn’t prove the expression must be slow. SQL Server can transform it. Inspect the actual plan and representative workload before selecting a rewrite.

Related reading

• SQL SERVER – Writing Your First Subquery From a Single Table

A correlated reference is not proof of repeated physical execution, it is a logical relationship the optimizer may transform.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.