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

Get Microsoft® SQL Server™ 2000 Programming by Example now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.