Home

	Expand Search

Transact-SQL User's Guide

Chapter 5 Subqueries: Using Queries Within Other Queries

Using correlated subqueries

Correlated subqueries in a having clause

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.