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 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. You need not group by type explicitly, because the rows for which the average price is calculated are restricted by the where clause in the subquery.