Microsoft® SQL Server™ 2000 Programming by Example

Correlated Subqueries

In the previous examples, the subqueries were normal queries that you could run isolated from the outer query.

In some cases, you might need to write a subquery that depends on values from the outer query to retrieve its results. In these cases, you call the subquery a correlated subquery. This usually forces the execution of the subquery once per every row returned from the outer query.

Tip

You can easily identify a correlated subquery because it is enclosed in parentheses and it cannot be executed independently from the outer query.

Correlated subqueries can return a single scalar value, a list of values, or an array of values in the same way as standard subqueries.

Suppose you wanted to know the suggested UnitPrice