Correlated 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) 

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 SAP ASE 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