その他の場所でのサブクエリ / 相関サブクエリ / EXISTS,ANY,ALL


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

サブクエリは、条件句である WHERE 句や HAVING 句のみだけでなく、SELECT のすぐ後ろに続けて使用することもできます。

サブクエリの結果を、メインクエリで直接表示させることができます。

/* サブクエリ */

 SELECT 顧客コード,AVG(受注個数),
        (SELECT AVG(受注個数) FROM 受注表)
        FROM 受注表
        GROUP BY 顧客コード;
/* 実行結果 */
顧客コード AVG(受注個数)  AVG(受注個数)
───── ─────── ───────
001        280            155
002        155            155
003        57             155
004        135            155

 

/* サブクエリ */

 SELECT MAX(AVG_J) AS MAX_AVG
   FROM (SELECT AVG(受注個数) AS AVG_J
        FROM 受注表 GROUP BY 顧客コード) ;
/* 実行結果 */
MAX_AVG
────
280

相関サブクエリ

SQL にサブクエリを使用する場合、内部クエリから外部クエリの FROM 句のテーブルを参照する、相関サブクエリを構成することができます。

この場合、メインクエリのテーブルの行ごとに、サブクエリが繰り返し実行されます。

/* 相関サブクエリ */

 SELECT * FROM 商品表 AA
          WHERE '20010401' IN
          (SELECT 納品日
           FROM 受注表 BB
           WHERE AA.商品コード = BB.商品コード);
/* 実行結果 */
商品コード 商品名 単価
───── ─── ──
101        BEER   500
102        JUICE  400

少し複雑なので簡単に説明すると、

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

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

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

/* サブクエリ */

 SELECT 受注番号,受注個数 FROM 受注表 XX
   WHERE 受注個数 >
     (SELECT AVG(受注個数)
        FROM 受注表 YY);
/* 実行結果 */
受注番号 受注個数
──── ────
10001    300
10002    200
10006    250
10010    290
10012    175

HAVING 句にも相関サブクエリを用いることができます。

/* サブクエリ */

 SELECT SUM(受注個数),納品日
        FROM 受注表 PP
        GROUP BY 納品日
        HAVING SUM(受注個数) >
        (SELECT 150 + MAX(受注個数)
         FROM 受注表 QQ
         WHERE PP.納品日 = QQ.納品日);
/* 実行結果 */
SUM(受注個数) 納品日
─────── ────
650           20010401
485           20010402
490           20010403

EXISTS/ANY/ALL

演算子 EXISTS, ANY, ALL はサブクエリが返す値に対して、「TRUE」または「FALSE」の評価を行い、メインクエリの WHERE 句で使用されます。

サブクエリの返す値の集合を結果セットと呼び、EXISTS 演算子は結果セットの有無を評価します。

  • ANY 演算子は結果セットのいずれかの値を評価対象とする
  • ALL 演算子は結果セットの全ての値を評価対象とする

EXISTS

EXIST 演算子はサブクエリの生成した値が存在する場合は「TRUE」、存在しない場合は「FALSE」を返します。

ANY 演算子、ALL 演算子は「=」「<」等の比較演算子と共に用いて、全体で「TRUE」または「FALSE」の評価しますが、EXISTS 演算子だけは単独で「TRUE」または「FALSE」の評価を行います。

また、サブクエリの返す値は複数の行のものでも、複数の列のものでも構いません。

/* EXISTS */

 SELECT * FROM 商品表
        WHERE EXISTS
        (SELECT * FROM 商品表
          WHERE 商品名 = ‘BEER’);

/* 実行結果 */
商品コード 商品名 単価
───── ─── ──
101        BEER   500
102        JUICE  400
103        TEA    400
104        WINE   650

サブクエリで商品表から商品名が ‘BEER’ と一致するものを出力し、値が出力されれば、商品表の全てを表示します。
商品表には商品名が ‘BEER’ というものがあるので、商品表の全てが参照されます。

相関サブクエリについても EXISTS を使用することが出来ます。

/* EXISTS */

 SELECT 受注番号,顧客コード FROM 受注表 JJ
        WHERE EXISTS
        (SELECT * FROM 商品表 SS
          WHERE 商品名 = ‘JUICE’
                AND JJ.商品コード = SS.商品コード);

/* 実行結果 */
受注番号 顧客コード
──── ─────
10001    001
10004    004
10007    003

受注表から’JUICE’を受注した受注番号と顧客コードを参照します。

相関サブクエリでは、メインクエリから受け取った各行ごとに EXISTS 演算子は「TRUE」か「FALSE」の評価を行います。

まず受注表から受注番号が ’10001′ である行をサブクエリで使用します。
この行の商品コードを商品表の商品コードと比較します。
また同時にその商品名が ‘JUICE’ であるかもチェックします。
この行の商品コードは ’102′ で、同時に商品表の商品名は ‘JUICE’ となります。
この行と商品表の各行の比較が終わったとき、サブクエリは商品表の商品名が ‘JUICE’ の行について、全ての列の値を出力します。

EXISTS 演算子はこれを受け取って「TRUE」と評価します。
そして受注番号が ’10001′ の行は出力されることが決まり、受注番号と顧客コードが表示されるということになります。

同じことが受注表の全ての行に対して行われ、結局 ‘JUICE’ を注文したときの行についてのみ出力されることになります。

通常のサブクエリでは、EXISTS 演算子は結果の存在有無を一度しか評価しませんが、相関サブクエリではそれぞれの行に対して評価を行います。

EXISTS 演算子には、NOT EXISTS と記述することで EXISTS 演算子と反対の役割を果たすようになります。

すなわち、サブクエリの値が存在するときは「FALSE」、存在しないときは「TRUE」と評価します。

ANY

ANY 演算子はサブクエリの生成した値のいずれかの値を評価対象とします。
ANY 演算子は比較演算子と共に用いられ、結果セットの値のいずれかが比較演算子の関係を満たす場合に「TRUE」、満たさなければ「FALSE」を返します。

また、サブクエリが生成する値は複数でも構いません。

次のSQL文は受注表において納品日が顧客コードが ’002′ のものと同じであるものを参照します。

/* ANY */

 SELECT 顧客コード,納品日 FROM 受注表
        WHERE 納品日 = ANY
        (SELECT 納品日 FROM 受注表
          WHERE 顧客コード = '002');
/* 実行結果 */
顧客コード 納品日
───── ───
001        20010401
002        20010401
004        20010402
002        20010402
001        20010402
003        20010402

サブクエリでは、顧客コードが ’002′ の納品日を出力します。納品日が [20010401, 20010402] という結果セットが作られます。

この結果セットの中のいずれかについてメインクエリの WHERE 句で「TRUE」になるものについて、顧客コードと納品日が出力されます。
これは、前に説明した IN を用いた場合と同じ効果が得られます。

次のSQL文は IN を使用して同じ出力を得るものです。

/* IN */

 SELECT 顧客コード,納品日 FROM 受注表
        WHERE 納品日 IN
        (SELECT 納品日 FROM 受注表
          WHERE 顧客コード = '002');
/* 実行結果 */
顧客コード 納品日
───── ───
001        20010401
002        20010401
004        20010402
002        20010402
001        20010402
003        20010402

しかし、ANY 演算子は IN 演算子では出来なかったことが出来るようになっています。
ANY 演算子は比較演算子として、「=」だけでなく「>」「<=」などの不等号も使用できるのである。

次のSQL文では受注表において、受注個数が顧客コード ’004′ の受注個数の最大よりも小さなものについて参照します。

/* ANY */

 SELECT 受注番号,顧客コード,受注個数 FROM 受注表
        WHERE 受注個数 < ANY
        (SELECT 受注個数 FROM 受注表
          WHERE 顧客コード = '004');
/* 実行結果 */
受注番号 顧客コード 受注個数
──── ───── ────
10003    004        150
10004    004        80
10005    002        110
10007    003        45
10008    004        135
10009    003        65
10011    003        60

サブクエリで受注表から顧客コードが ’004′ のものについて、受注個数を出力しています。

この結果セットは [80, 135, 150, 175] です。これらの値のうちいずれかについて、受注表の各行の受注個数が小さくなる行が出力されます。
受注個数が 175 未満であるものについて出力されることになります。

また ANY 演算子と全く同じ機能を持つ演算子があります。
SOME 演算子を用いることにより、ANY 演算子と同じ効果が得られます。

ALL

ALL 演算子はサブクエリの生成した値の全てを評価対象とします。
ANY 演算子と同じように、ALL 演算子も比較演算子と共に用いられます。

結果セットの値の全てが比較演算子との関係を満たす場合に「TRUE」、満たさなければ「FALSE」を返します。

また、ANY 演算子と同様に、サブクエリが生成する値は複数でも構いません。

次のSQL文は、前回の ANY を ALL に変えただけです。
受注表から受注個数が顧客コード ’004′ の受注個数の最小値より小さなものについて、受注番号、顧客コード及び受注個数を参照します。

/* ALL */

 SELECT 受注番号,顧客コード,受注個数 FROM 受注表
        WHERE 受注個数 < ALL
        (SELECT 受注個数 FROM 受注表
          WHERE 顧客コード = '004');
/* 実行結果 */
受注番号 顧客コード 受注個数
──── ───── ────
10007    003        45
10009    003        65
10011    003        60

サブクエリで受注表から顧客コードが ’004′ のものについて、受注個数を出力しています

この結果セットは [80, 135, 150, 175] です。
これらの値のうち全てについて、受注表の各行の受注個数が小さくなる行が出力されます。
受注個数が 80 未満であるものについて出力されることになります。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です


5 − = 4

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>