Evaluate many of the previous queries by executing the subquery once and substituting the resulting values into the where clause of the outer query; these are noncorrelated subqueries. In queries that include a repeating subquery, or correlated subquery, the subquery depends on the outer query for its values. The subquery is executed repeatedly, once for each row that is selected by the outer query.
This example finds the names of all authors who earn 100 percent royalty on a book:
select au_lname, au_fname from authors where 100 in (select royaltyper from titleauthor where au_id = authors.au_id)
au_lname au_fname -------------- ---------- White Johnson Green Marjorie Carson Cheryl Straight Dick Locksley Chastity Blotchet-Hall Reginald del Castillo Innes Panteley Sylvia Ringer Albert (9 rows affected)
Unlike most of the previous examples, 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 variable—it changes as Adaptive Server examines different rows of the authors table.
This is how the preceding query is evaluated: Transact-SQL considers each row of the authors table for inclusion in the results, by substituting the value in each row in the inner query. For example, suppose Transact-SQL first examines the row for Johnson White. Then, authors.au_id takes the value “172-32-1176,” which Transact-SQL substitutes for the inner query:
select royaltyper from titleauthor where au_id = "172-32-1176"
The result is 100, so the outer query evaluates to:
select au_lname, au_fname from authors where 100 in (100)
Since the where condition is true, the row for Johnson White is included in the results. If you go through the same procedure with the row for Abraham Bennet, you can see how that row is not included in the results.
This query uses a correlated variable as the outer member of a Transact-SQL outer join:
select t2.b1, (select t2.b2 from t1 where t2.b1 *= t1.a1) from t2
