A correlated subquery is a Subquery that uses the values of the outer query and it is evaluated for each row processed by the outer query .
A correlated subquery always depends on outer query for its value. Because of this dependency, a correlated subquery cannot be executed independently like as simple subquery .
Lets look at an example of correalted subquery.
Here, we have two tables – Orders and orders_details as given below.
Select OrderId, OrderDate, customerName, Orderqty from orders Select OrderId, UnitPrice from Orders_details
Following statement uses correlated subquery to returns the details of the orders table- orders_details whose OrderQty price is equal to max unitprice in a table – orders
select OrderId ,OrderDate ,CustomerName ,Orderqty from orders odr where Orderqty in (select max(UnitPrice) as qty from orders_details od where Odr.OrderId =od.OrderId Group by orderId )