Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Correlated Subqueries with Comparison Operators

To find sales where the quantity ordered is less than the average order for sales of that title, use the following query:

SQL

select s1.sonum, s1.title_id, s1.qty_ordered
from salesdetails s1
where qty_ordered <
						  (select avg(qty_ordered)
						   from salesdetails s2
						   where s1.title_id = s2.title_id)
order by title_id
      sonum title_id qty_ordered
=========== ======== ===========
          8 BU1032             5
          5 MC3021            15
          2 PS2091            10
          3 PS2091            20
          7 PS2091             3
[5 rows]

The outer query selects the rows of the salesdetails table (that is, of s1) one by one. The subquery calculates the average quantity for each order being considered for selection in the outer query. For each possible value of s1, the system evaluates the subquery ...