7. サブクエリ

7.1. サブクエリの基本

複数のクエリを組み合わせて、1つのクエリが生成した出力で、他のクエリの出力を制御することができます。少し分かりやすく言うと、クエリを入れ子にして、内側のクエリが値を生成し、それを外側のクエリの述語が評価して TRUE かどうか判断します。

次の図はサブクエリの最も標準的な形です。図では背景がオレンジ色の部分が内側のクエリで、ピンクの部分が外側のクエリということになります。内側のクエリで生成された値を外側のクエリが受け取り、WHERE 句で TRUE となるものについて、値を参照します。そして、内側のクエリのことをサブクエリといいます。

前の図からも予想がつくように、等号や不等号といった関係演算子をベースとする述語に使用するサブクエリは、必ず出力として 1行のみを生成するものでなければなりません。サブクエリが値をまったく生成しない場合、述語は UNKNOWN と評価するが、複数の値を生成する場合、メインクエリ (外側のクエリ) は評価不能に陥ってしまい、ステートメントがエラーを生成するという状況になります。

sample.18-1 は、商品名が 'JUICE' である受注番号を参照するものです。

/* サブクエリ */

  SELECT 受注番号 FROM 受注表
         WHERE 商品コード =
         (SELECT 商品コード FROM 商品表
          WHERE 商品名 = 'JUICE');

 受注番号
 ────
 10001
 10004
 10007

7.2. WHERE 句でのサブクエリ

前節の図でも示したように、基本的なサブクエリは WHERE 句の述語によく用いられます。先ほどの説明で、等号や不等号といった関係演算子をベースとする述語では、サブクエリは 1 行のみを生成するものでなければならないことを示しました。しかし、IN 演算子を使用すれば任意数の行を生成するサブクエリを使用することができるようになります。IN 演算子は含まれる値と含まれない値の集合を明示的に定義するものです。

sample.18-2 は受注した商品の単価が 400 円である受注番号を参照するものです。

/* サブクエリ */

  SELECT 受注番号 FROM 受注表
         WHERE 商品コード IN
         (SELECT 商品コード FROM 商品表
          WHERE 単価 = 400);

 受注番号
 ────
 10001
 10004
 10006
 10007
 10009
 10011

実習課題 1

販売管理データベースから、支払い金額が全ての受注 (受注番号が 100101018) に関する支払い金額の平均より大きい顧客の氏名と住所を表示する SQL 文を記述しなさい。支払い金額については、6章 1節の実習課題 2 を参照すること。

解答例はこちら

7.3. HAVING 句でのサブクエリ

HAVING 句にもサブクエリを使用することが可能です。使用方法は WHERE 句で使用していたのと同じです。

sample.18-3 は受注個数が顧客コード '002' の最小値よりも小さい注文をした顧客コードとその件数を参照するものです。

/* サブクエリ */

  SELECT 顧客コード,COUNT(DISTINCT 受注番号) FROM 受注表
         GROUP BY 顧客コード
         HAVING 受注個数 <
         (SELECT MIN(受注個数) FROM 受注表
          WHERE 顧客コード = '002');

 顧客コード COUNT
 ───────── ──────
 004        1
 003        3

7.4. その他の場所でのサブクエリ

サブクエリは、条件句である WHERE 句や HAVING 句のみだけでなく、SELECT のすぐ後ろに続けて使用することもできます。サブクエリの結果を、メインクエリで直接表示させることができます。たとえば、受注表における全受注個数の平均値と、各顧客の受注個数の平均値を比べて見たい場合は、sample.18-4 のように記述します。

/* サブクエリ */

  SELECT 顧客コード,AVG(受注個数),
         (SELECT AVG(受注個数) FROM 受注表)
         FROM 受注表
         GROUP BY 顧客コード;

 顧客コード AVG(受注個数)  AVG(受注個数)
 ───────── ───────────── ─────────────
 001        280            155
 002        155            155
 003        57             155
 004        135            155

また、サブクエリは FROM 句の後に続けて使用することもできます。サブクエリの結果セットを一つのテーブルとして、利用するものです。sample.18-5 は、受注表における各顧客の受注個数の平均の中で、最も大きい値を出力するものです。

/* サブクエリ */

  SELECT MAX(AVG_J) AS MAX_AVG
    FROM (SELECT AVG(受注個数) AS AVG_J
         FROM 受注表 GROUP BY 顧客コード) ;

 MAX_AVG
 ────
 280

実習課題 2

販売管理データベースから、各営業所の売上ランキング表を作成する SQL 文を記述しなさい。各営業所が管理する顧客の支払い金額を合計したものを各営業所の売上とする。各営業所が管理する顧客は、customer テーブルを参照すること。表示する項目は、営業所名及び売上金額とする。

解答例はこちら

7.5. 相関サブクエリ

SQL にサブクエリを使用する場合、内部クエリから外部クエリの FROM 句のテーブルを参照する、相関サブクエリを構成することができます。この場合、メインクエリのテーブルの行ごとに、サブクエリが繰り返し実行されます。

sample.18-6 では納品日が '20010401' の商品を、相関サブクエリを用いて参照します。

/* 相関サブクエリ */

  SELECT * FROM 商品表 AA
           WHERE '20010401' IN
           (SELECT 納品日
            FROM 受注表 BB
            WHERE AA.商品コード = BB.商品コード);

 商品コード 商品名 単価
 ───────── ────── ────
 101        BEER   500
 102        JUICE  400

sample.18-6は少し複雑なので簡単に説明します。

1. 商品表から商品コードが '101' の行を選択する。
2. この行を、エイリアス AA の候補行として保存する。
3. 次にサブクエリを実行する。サブクエリは、受注表全体から商品コード列が AA.商品コード列 (商品コード '101') と一致する行を検索する。次に受注表からこれが TRUE となる行の納品日列を取り出し、納品日値の結果セットを構築する。
4. 商品コードが '101' である納品日値の値の集合を構築したら、この集合に '20010401' が含まれているかどうかを確認するため、メインクエリの述語を評価する。含まれている場合は (この場合は含まれている)、'101' の行をメインクエリの出力として選択する。
5. 次に、商品コードが '102' の行を候補行として手続き全体を繰り返す。これを商品表の行が 1つ残らず評価されるまで繰り返す。

このような手続きの結果、重複するデータがあっても表示されなくなります。

メインクエリとサブクエリで同じテーブルを使用する相関サブクエリというものもあります。テーブルの自己相関といいますが、複雑な形式の派生情報を抽出することができます。

sample.18-7 では、受注したものの中から受注個数が平均個数を超えるものを検索します。ちなみに、平均個数は 155個です。

/* サブクエリ */

  SELECT 受注番号,受注個数 FROM 受注表 XX
    WHERE 受注個数 >
      (SELECT AVG(受注個数)
         FROM 受注表 YY);

 受注番号 受注個数
 ─────── ───────
 10001    300
 10002    200
 10006    250
 10010    290
 10012    175

HAVING 句にも相関サブクエリを用いることができます。sample.18-8 では、受注個数を納品日別に集計し、それぞれの合計が受注個数の最大値を 150 上回るものだけを参照するものです。

/* サブクエリ */

  SELECT SUM(受注個数),納品日
         FROM 受注表 PP
         GROUP BY 納品日
         HAVING SUM(受注個数) >
         (SELECT 150 + MAX(受注個数)
          FROM 受注表 QQ
          WHERE PP.納品日 = QQ.納品日);

 SUM(受注個数) 納品日
 ───────────── ────────
 650           20010401
 485           20010402
 490           20010403

実習課題 3

販売管理データベースから、3台以上の自動車を販売した従業員の名前を表示する SQL 文を記述しなさい。

解答例はこちら