Correlated Subqueries with Comparison Operators

Use a correlated subquery with a comparison operator to find sales where the quantity is less than the average quantity for sales of that title.

USE pubs

SELECT s1.ord_num, s1.title_id, s1.qty

FROM sales s1

WHERE s1.qty <

    (SELECT AVG(s2.qty)

    FROM sales s2

    WHERE s2.title_id = s1.title_id)

  

Here is the result set:

ord_num              title_id qty   

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

6871                 BU1032   5     

722a                 PS2091   3     

D4482                PS2091   10    

N914008              PS2091   20    

423LL922             MC3021   15    

  

(5 row(s) affected)

  

The outer query selects the rows of sales (that is, of 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, Microsoft® SQL Server™ 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 clause. This example finds all titles that have a price greater than the average for books of its type.

USE pubs

SELECT t1.type, t1.title

FROM titles t1

WHERE t1.price >

    (SELECT AVG(t2.price)

    FROM titles t2

    WHERE t1.type = t2.type)

  

Here is the result set:

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 row(s) affected)

  

For each possible value of t1, SQL Server 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 average price is calculated are restricted by the WHERE clause in the subquery.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.