The correlated subquery is a form of a subquery that cannot be run independently of the outer query, because it contains one or more columns from the outer query. The correlated subquery, just like a normal subquery, is sometimes referred to as the inner query. If the correlated subquery (inner query) is run independently of the outer query it will return an error. Because the execution of the inner query depends on values from the outer query, it is called a correlated subquery.
The correlated subquery may be executed many times. It will be run once for each candidate row selected in the outer query. The column values of each candidate row will be used to supply values for the outer query columns in the inner for each execution of the correlated subquery. The final results of a statement that contains a correlated subquery will be based on results of each execution of the correlated subquery.
SELECT CustomerID FROM Sales.SalesOrderHeader OH
WHERE (SELECT COUNT(*) FROM Sales.SalesOrderDetail
WHERE SalesOrderID = OH.SalesOrderID) > 70;
There are times when you might want to constrain a HAVING clause by different values from outer query. This is when you can use a correlated subquery in your HAVING clause. Suppose you have to write a query that will calculate rebate amounts for those customer that have purchased more than $150000 worth of products before taxes in the year 2008. The code in Listing 3 calculates the rebate amount for those valued customers by using a correlated subquery in the HAVING clause.
SELECT Outer_H.[CustomerID]
, SUM(Outer_H.[SubTotal]) AS TotalPurchase
, SUM(Outer_H.[SubTotal]) * .10 AS Rebate
FROM [Sales].[SalesOrderHeader] AS Outer_H
WHERE YEAR(Outer_H.[OrderDate]) = '2008'
GROUP BY Outer_H.[CustomerID]
HAVING (SELECT SUM(Inner_H.[SubTotal]) FROM [Sales].[SalesOrderHeader] AS Inner_H
WHERE Inner_H.[CustomerID] = Outer_H.[CustomerID]
AND YEAR(Inner_H.[OrderDate]) = '2008') > 150000
ORDER BY Rebate DESC;