User's Guide
   PART 1. Working with Databases
     CHAPTER 7. Using Subqueries       

Subqueries in the HAVING clause

Although subqueries are usually used as search conditions in the WHERE clause, they are occasionally found in the HAVING clause of a query. When a subquery appears in the HAVING clause, it, like any expression in the HAVING clause, is used as part of the row group selection.

Here is a request that lends itself naturally to a query with a subquery in the HAVING clause: "Which products' average in-stock quantity is less than double the average number of each item ordered per customer?"

Example 

SELECT name, avg (quantity)
FROM product 
GROUP BY name
HAVING avg (quantity) > 2* (
   SELECT avg (quantity) 
   FROM sales_order_items
   )

name	avg(quantity)
Baseball Cap	62.000000
Shorts	80.000000
Tee Shirt	52.333333

The query is executed as follows:

• The subquery calculates the average quantity of items in the sales_order_items table.

• The main query then goes through the product table, calculating the average quantity product, grouping by product name.

• The HAVING clause then checks if that quantity is more than double the quantity found by the subquery. If so, the main query returns that row group; otherwise, it doesn't.

• The SELECT clause produces one summary row for each group, showing the name of each product and its in-stock quantity.

Outer references in the HAVING clause 

You can also use outer references in a HAVING clause, as shown in this request, a slight variation on the one above:

Example 

"Find the ID numbers and line ID numbers of those products whose average ordered quantities is less more than half the in-stock quantities of those products."

SELECT prod_id, line_id
FROM sales_order_items
GROUP BY prod_id, line_id
HAVING 2* avg (quantity) > (
   SELECT quantity 
   FROM product 
   WHERE product.id = sales_order_items.prod_id)

prod_id	line_id
300	1
401	2
500	1
501	2
600	1

In this example, the subquery must produce the average in-stock quantity of the product corresponding to the row group being tested by the HAVING clause. The subquery selects records for that particular product, using the outer reference sales_order_items.prod_id.

A subquery with a comparison returns a single value 

This query uses the comparison ">", suggesting that the subquery must return exactly one value. In this case, it does, as the id field of the product table is a primary key, so there is only one record in the product table corresponding to any particular product id.

  Subquery tests

The chapter Queries: Selecting Data from a Table describes simple search conditions that can be used in the HAVING clause. As a subquery is just an expression that appears in the WHERE or HAVING clauses, the search conditions on subqueries may look familiar.

They are:

• Subquery comparison test     For each record in the table(s) in the main query, compares the value of an expression to a single value produced by the subquery.

• Quantified comparison test     Compares the value of an expression to each of the set of values produced by a subquery.

• Subquery set membership test     Checks if the value of an expression matches one of the set of values produced by a subquery.

• Existence test     Checks if the subquery produces any rows of query results.