HAVING 句でのサブクエリ

サブクエリは通常は WHERE 句内で探索条件として使用しますが、クエリの HAVING 句で使用することもできます。HAVING 句内のサブクエリは、HAVING 句内の他の式と同様に、ロー・グループの選択の一部として使用されます。

「どの製品の平均在庫数が、顧客ごとの各品目の平均注文数の 2 倍以上あるのか」という要求は、当然 HAVING 句内にサブクエリを持つクエリになります。

例

SELECT Name, AVG( Quantity )
FROM Products
GROUP BY Name
HAVING AVG( Quantity ) > 2* (
   SELECT AVG( Quantity )
   FROM SalesOrderItems 
 );

Name	AVG( Products.Quantity )
Baseball Cap	62.000000
Shorts	80.000000
Tee Shirt	52.333333

クエリは次のように実行します。

サブクエリは SalesOrderItems テーブルにある品目の平均数を計算します。
メイン・クエリは Products テーブルを調べて、製品ごとの平均数を計算し、製品名でグループ化します。
HAVING 句は、各平均数がサブクエリで検索された数量の 2 倍を超えるかどうかを確認します。超える場合、メイン・クエリはそのロー・グループを返します。超えない場合は返しません。
SELECT 句は、グループごとに 1 つの計算ローを生成し、各製品の名前と在庫の平均数を示します。

次の例で示すように、HAVING 句には外部参照も使用できます。この例は、前述の例を若干変更したものです。

例

この例では、平均注文数が在庫数の半分よりも多い製品の ID 番号とライン ID 番号を検索します。

SELECT ProductID, LineID
FROM SalesOrderItems
GROUP BY ProductID, LineID
HAVING 2* AVG( Quantity ) > (
   SELECT Quantity
   FROM Products
   WHERE Products.ID = SalesOrderItems.ProductID );

ProductID	LineID
601	3
601	2
601	1
600	2
...	...

この例では、サブクエリは、HAVING 句にテストされるロー・グループに対応する製品の在庫数を生成します。サブクエリは外部参照 SalesOrderItems.ProductID を使用して、その特定製品のレコードを選択します。

比較演算子を持つサブクエリは 1 つの値を返す

このクエリは比較演算子 > を使用するので、サブクエリは 1 つの値を返します。この場合は、1 つの値を返します。Products テーブルの ID フィールドがプライマリ・キーなので、特定の製品 ID に対応する Products テーブルのレコードは 1 つだけになります。