Correlated subqueries

What is a correlated subquery ?

Correlated subquery is a sql query where the subquery portion depends on the main/outer query to interpret its result.

For example if you want to get 2nd max SalesAmt you can use the correlated subquery as below.

Create table #temp

(

Country VARCHAR(10),

[State] VARCHAR(10),

SalesAmt Money

)

INSERT INTO #temp VALUES('India','Karnataka',50000),('India','Andhra',30000),('India','Telangana',15000),

('USA','Texas',50000),('USA','NY',32000),('USA','Redmond',15000),

('UK','London',50000),('UK','Reading',30100),('UK','Edinburg',15000)

SELECT * FROM #temp T WHERE T.SalesAmt = (

SELECT MAX(SalesAmt) Amt FROM #temp A

WHERE SalesAmt < (SELECT MAX(SalesAmt) FROM #temp WHERE A.Country = Country)

AND A.Country = T.Country)

There are easier methods to get 2nd max like using Ranking functions. You can also combine Group By and joins to do so.

In correlated subquery you can use operators like =,<>, < ,> or you can use in and not in.

for details you can refer to MSDN