Correlated Subqueries

Many of the previous queries can be evaluated by executing the subquery once and substituting 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. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

This statement finds the names of all authors who earn 100 percent of the shared royalty (royaltyper) on a book:

SELECT au_lname, au_fname
FROM authors
WHERE 100 IN
    (SELECT royaltyper
    FROM titleauthor
    WHERE titleauthor.au_ID = authors.au_id)

au_lname

au_fname

-------------

--------

White

Johnson

Green

Marjorie

Carson

Cheryl

Straight

Dean

Locksley

Charlene

Blotchet-Halls

Reginald

del Castillo

Innes

Panteley

Sylvia

Ringer

Albert

(9 row(s) affected)

Unlike most of the subqueries previously shown, the subquery in this statement cannot be resolved independently of the main query. It needs a value for authors.au_id, but this value is a variable ¾ it changes as SQL Server examines different rows of the authors table.

That's exactly how this query is evaluated: SQL Server considers each row of the authors table for inclusion in the results by substituting the value in each row into the inner query. Say that SQL Server first examines the row for Cheryl Carson. The variable authors.au_id takes the value 238-95-7766, which SQL Server substitutes into the inner query:

SELECT royaltyper
FROM titleauthor
WHERE au_id = '238-95-7766'

The result is 100, so the outer query evaluates to:

SELECT au_lname, au_fname
FROM authors
WHERE 100 in (100)

Since this is true, the row for Cheryl Carson is included in the results. If you go through the same procedure with the row for Abraham Bennet, you'll see that this row is not included in the results.