Correlated Sub-Query Vs Nested Sub-Query – Exploring Correlated Sub-Queries

In SQL every Sub-Query has two parts inner query and outer query and these query may be another sub query. Correlated sub-query is dependent upon the outer query.

The outer query and the sub-query are related typically through a WHERE statement located in the sub-query. The way a correlated sub-query works is when a reference to the outer query is found in the sub-query, the outer query will be executed and the results returned to the sub-query. The sub-query is executed for every row that is selected by the outer query.So a correlated sub query executes once for every row retrieved by the parent query.

Since correlated sub queries have to execute for every row returned in the outer query performance can be degraded. But this performance is totally based on the query and data involved. The main advantage of a correlated sub-query is that you can use it to solve problems that cannot be solved with a conventional SQL Server query.

Example:
SELECT SaleDate, Product, SalePrice,
ISNULL ((SELECT SUM (SalePrice) FROM SalesHistory sh1
WHERE Sh1.Product = sh.Product AND Sh1.SaleID < sh.SaleID), 0) AS
RunningTotal FROM SalesHistory sh ORDER BY Product, SaleID

The running total query produced by the statement is the correlated sub-query. For each product in the table, the correlated sub-query iterates the result set and sums the SalePrice for every product sold before that record in the result set.
-
*Beginning SQL Server 2008 for Developers: From Novice to Professional