サブクエリー②(複雑なサブクエリー)

1 サブクエリー②(複雑なサブクエリー)

ここでは、サブクエリーをいろいろな技法と組み合わせることによって、全体としてより複雑なクエリーを作り上げる方法を見ていきます。

1.集計関数の利用

前回説明したように、結果が1つに定まらないサブクエリーはエラーになりますが、結果を1つに定めるために集計関数を使うことができます。集計関数のSUM、COUNT、MIN、MAX、AVGは、いずれも１つの結果を返します。次のSQLは、サブクエリーの中で受注数量の平均を求めて、その平均受注数量より
大きい受注合計の情報を抽出しています。

SELECT J.受注日, J.得意先名, J.数量 * S.単価受注合計
FROM 受注 J,商品 S
WHERE J.商品コード = S.商品コード
AND J.数量 * S.単価 >
(SELECT AVG(J.数量 * S.単価) FROM 受注 J,商品 S WHERE J.商品コード = S.商品コード);

まず、サブクエリの結果を見てみます。
SELECT AVG(J.数量 * S.単価) FROM 受注 J,商品 S WHERE J.商品コード = S.商品コード

結果は以下のようになります。

なので、最初のSQLは、この平均受注合計より大きいデータが対象となります。
結果は以下のようになります。

WHERE句を外して全件抽出した際の結果

ということで、最初のSQLの結果が正しく平均受注合計より大きいデータを抽出していることが分かります。

2.サブクエリーのネスト

サブクエリーの中に別のサブクエリーを埋め込むことができます。このように、サブクエリーの中にサブクエリーを埋め込むことをサブクエリーのネストといいます。ここでは、サブクエリーのネストを利用して、平均受注額を超える受注のあった得意先に特別な通知を送付するためのクエリーを作成してみます。

商品表

得意先表

受注表

クエリー全体は以下のようになります。このクエリーでは、2つのサブクエリーをネストして使って、該当する得意先の情報を出力しています。

SELECT T.得意先名, T.住所, T.電話番号
FROM 得意先 T
WHERE T.得意先名 IN
(SELECT J.得意先名 FROM 受注 J, 商品 S WHERE J.商品コード = S.商品コード
AND
J.数量 * S.単価 >
(SELECT AVG(J.数量 * S.単価) FROM 受注 J,商品 S WHERE J.商品コード = S.商品コード)
);

結果は以下のようになります。