Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor demonstrate correlated subqueries.

-- SQL correlated (o.ProductID = ProductID) subquery
-- list all the order ids with order quantity in the bottom quartile

SELECT DISTINCT o.OrderId, o.ProductID

FROM   Northwind.dbo.[Order Details] o

WHERE  Quantity <= (SELECT avg(Quantity) * .25

                    FROM   Northwind.dbo.[Order Details]

                    WHERE  o.ProductID = ProductID)

/* OrderId ProductID

10609       1

10700       1

10838       1

11003       1

11005       1

10851       2

10722       2  .... */

------------

-- Create table for demo purposes

SELECT * INTO dbo.Department from AdventureWorks.HumanResources.Department

GO

-- UPDATE with correlated subquery

UPDATE D

SET Name =

(

 SELECT Name FROM dbo.Department

 WHERE DepartmentID = D.DepartmentID

)

FROM AdventureWorks.HumanResources.Department D;

Related article:

http://www.sqlusa.com/bestpractices/derivedcorrelated/