Differences between subquery and correlated subquery

    A correlated subquery is a subquery where the inner query is evaluated once for every value returned by the outer query. Generally a query which has a subquery will execute the subquery once and substitute the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values.

   
For example, using the northwind database, if you want to find out the price of all the books ordered, you can use a correlated subquery. Price information about each book is in the [Order Details] table, and the Orders table has all the books ordered.

SELECT O.OrderID, O.OrderDate,
(SELECT MAX (Od.UnitPrice) FROM [Order Details] AS Od
WHERE Od.OrderID = O.orderid) AS MaxUnitPrice
FROM Orders AS O

   
The subquery runs once for each row that the main query returns. This repeated access to the [Order Details] table could be inefficient. Generally, correlated subqueries can be re-written as a query using a join