Correlated subqueries

Correlated subqueries are not self-contained, but instead refer to columns from the parent query.

For example, the query, “Find all stores where the sales of dairy products is more than 10 percent of the total sales,” can be written in SQL as:

SELECT StoreID FROM Stores S
WHERE S.TotalSales*0.1 <
(SELECT SUM(1.Price) FROM Item i
WHERE S.StoreID = I.StoreId and I.ItemType = "Dairy");

This query is a correlated subquery because the inner query uses the outer column StoreID from the Stores table in the parent query to calculate dairy totals for specific stores.

In many cases, you can also write correlated queries as JOINS. For example, you can rewrite the example by using a JOIN between the Stores table and a table subquery on Items.

SELECT S.StoreId FROM Stores S,
(SELECT I.StoreId, Sum(Price) DairySales FROM Items I
WHERE I.ItemType = "Dairy" GROUP BY I.StoreId) D
WHERE S.StoreId = D.StoreId AND S.TotalSales *0.1 < D.DairySales;

In this version, the subquery creates a results table as part of the outer FROM clause that is then joined with the Stores tables so that the Dairy sales test can be performed.