Home

	Expand Search

Transact-SQL User's Guide

Chapter 5 Subqueries: Using Queries Within Other Queries

Using correlated subqueries

Using correlated subqueries

You can 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 subqueries, 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 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.

Correlated subqueries containing Transact-SQL outer joins

Adaptive Server version 12.5 does not process correlated subqueries containing Transact-SQL outer joins in the same way that earlier versions of Adaptive Server did. The following is an example of a query using 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

Earlier versions of Adaptive Server used trace flag 298 to display error messages for these queries. Depending on whether trace flag 298 was turned on or off and whether the query used the correlated variable as an inner or outer member of an outer join, Adaptive Server displayed the behavior described in Table 5-1:

Adaptive Server reverses the behavior of trace flag 298. Because Adaptive Server version 12.5 translates Transact-SQL outer joins into ANSI outer joins during the preprocessor stage, there is the potential for different results when allowing such queries to run. Allowing correlated subqueries that contain Transact-SQL outer joins to run with the 298 trace flag turned on is consistent with Sybase's historical trace flag usage. For version 12.5, the behavior of trace flag 298 is:

Adaptive Server has changed error message 301 to error message 11055, although the text of the message remains the same.

Correlated subqueries with correlation names

You can use a correlated subquery to find the types of books that are published by more than one publisher:

select distinct t1.type 
from titles t1 
where t1.type in 
   (select t2.type 
    from titles t2 
    where t1.pub_id != t2.pub_id)

type 
-------------------- 
business 
psychology 
 
(2 rows affected) 

Correlation names are required in the following query to distinguish between the two roles in which the titles table appears. This nested query is equivalent to the self-join query:

select distinct t1.type 
from titles t1, titles t2 
where t1.type = t2.type 
and t1.pub_id != t2.pub_id 

Correlated subqueries with comparison operators

Expression subqueries can be correlated subqueries. For example, to find the sales of psychology books where the quantity is less than average for sales of that title:

select s1.ord_num, s1.title_id, s1.qty 
from salesdetail s1 
where title_id like "PS%" 
and s1.qty < 
   (select avg(s2.qty) 
    from salesdetail s2 
    where s2.title_id = s1.title_id) 

ord_num            title_id     qty     
------------------ --------     ---
91-A-7             PS3333        90  
91-A-7             PS2106        30  
55-V-7             PS2106        31  
AX-532-FED-452-2Z7 PS7777       125
BA71224            PS7777       200  
NB-3.142           PS2091       200  
NB-3.142           PS7777       250  
NB-3.142           PS3333       345  
ZD-123-DFG-752-9G8 PS3333       750  
91-A-7             PS7777       180  
356921             PS3333       200

(11 rows affected) 

The outer query selects the rows of the sales table (or "s1") one by one. The subquery calculates the average quantity for each sale being considered for selection in the outer query. For each possible value of s1, Transact-SQL evaluates the subquery and puts the record being considered in the results, if the quantity is less than the calculated average.

Sometimes a correlated subquery mimics a group by statement. To find the titles of books that have prices higher than average for books of the same type, the query is:

select t1.type, t1.title 
from titles t1 
where t1.price > 
   (select avg(t2.price) 
    from titles t2 
    where t1.type = t2.type) 

type         title 
---------    --------------------------------------
business     The Busy Executive's Database Guide 
business     Straight Talk About Computers 
mod_cook     Silicon Valley Gastronomic Treats 
popular_comp But Is It User Friendly? 
psychology   Computer Phobic and Non-Phobic 
             Individuals: Behavior Variations 
psychology   Prolonged Data Deprivation: Four Case 
             Studies 
trad_cook    Onions, Leeks, and Garlic: Cooking 
             Secrets of the Mediterranean 
 
(7 rows affected) 

For each possible value of t1, Transact-SQL evaluates the subquery and includes the row in the results if the price value of that row is greater than the calculated average. It is not necessary to group by type explicitly, because the rows for which the average price is calculated are restricted by the where clause in the subquery.

Correlated subqueries in a having clause

Quantified predicate subqueries can be correlated subqueries.

This example of a correlated subquery in the having clause of an outer query finds the types of books in which the maximum advance is more than twice the average within a given group:

select t1.type 
from titles t1 
group by t1.type 
having max(t1.advance) >= any 
   (select 2 * avg(t2.advance) 
   from titles t2 
   where t1.type = t2.type)

type 
---------- 
mod_cook 
 
(1 row affected) 

The subquery above is evaluated once for each group that is defined in the outer query, that is, once for each type of book.