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.
