Contents Index Using subqueries in the WHERE clause Subquery tests

ASA SQL User's Guide
  Using Subqueries

Subqueries in the HAVING clause


Although you usually use subqueries as search conditions in the WHERE clause, sometimes you can also use them in the HAVING clause of a query. When a subquery appears in the HAVING clause, like any expression in the HAVING clause, it 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 more 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(product.quantity)
Tee Shirt 52.333333
Baseball Cap 62
Shorts 80

The query executes as follows:

You can also use outer references in a HAVING clause, as shown in the following example, a slight variation on the one above.

Example 

"Find the product ID numbers and line ID numbers of those products whose average ordered quantities is 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
401 2
401 1
401 4
501 3
... ...

In this example, the subquery must produce the 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. Since the id field of the product table is a primary key, there is only one record in the product table corresponding to any particular product id.


Subquery tests

Contents Index Using subqueries in the WHERE clause Subquery tests