トップ > SQL入門 > 20章
20章 副問合せ
それでは今回は副問合せについて学んでいきます。

さっそくですが、Kamataさんと同じ年収の従業員を求めたいとします。
このような問い合わせの場合、Kamataさんの年収が分かりませんので、
Kamataさんの年収を調べる問合せを行います。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE first_name = 'Kamata';

  FIRST_NAME                         SALARY
  ------------------------------ ----------
  Kamata                            4000000


これでKamataさんの年収が分かりましたので、その値を使用して検索を行います。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE salary = 4000000;

  FIRST_NAME                         SALARY
  ------------------------------ ----------
  Kuroda                            4000000
  Nakamura                          4000000
  Kamata                            4000000


上記がKamataさんと同じ年収の従業員の求め方となりますが、
今回のように条件があいまいな場合、
一度のSQLではデータを取得できない場合があります。
ただこのようなあいまいな条件でも副問合せを使用すると、
一度のSQL文で問合せを行うことができるようになります。

では構文です。左側のSELECT文を主問合せ、右側のSELECT文を副問合せと言います。

[構文] 副問合せ
 SELECT 列名,列名 …
 FROM  表名
 WHERE 列名 = ( SELECT 列名
            FROM  表名
           [WHERE]   );

副問合せの構文は、WHERE句の値は今までは定数を指定していましたが、
定数の代わりにSELECT文を指定します。

副問合せを使用することにより、先に副問合せ部分のSELECT文が実行され、
その実行結果がWHERE句の値で使用されます。

また副問合せは必ずカッコで囲む必要がありますのでご注意ください。

では、この構文を使用して先ほどのKamataさんと同じ年収の従業員を表示してみましょう。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE  salary = ( SELECT salary
    3                    FROM employees
    4                    WHERE first_name = 'Kamata');

  FIRST_NAME                         SALARY
  ------------------------------ ----------
  Kuroda                            4000000
  Nakamura                          4000000
  Kamata                            4000000


副問合せを使用した場合、まず副問合せ部分が実行されます。


  SELECT salary
  FROM employees
  WHERE first_name = 'Kamata'


この副問合せの部分でKamataさんのsalaryを求め、
その値(4000000)を主問合せのWHERE句の値で使用しています。

実行例ではイコールを使用しましたが、
今回のような副問合せの結果が1行だけ返ってくるものを、

単一行副問合せといいます。また副問合せの結果が
複数行返ってくるものを複数行副問合せと言います。

単一行副問合せの場合、以下の演算子が使用できます。

単一行服問合せで使用可能な演算子
等しい
より大きい
より小さい
<= 以下
>= 以上
!= 以外

では、色々な実行例を確認していきましょう。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE  salary > ( SELECT salary
    3                    FROM employees
    4                    WHERE first_name = 'Kamata');

  FIRST_NAME                         SALARY
  ------------------------------ ----------
  Suzuki                           10000000
  Sato                              8000000
  Yamamoto                          5000000
  Watanabe                          6000000
  Watanabe                          5500000
  Kataoka                           6500000
  Harada                            6200000
  Koda                              6400000
  Saito                             6700000
  Akagi                             6400000
  Sato                              5400000
  Tao                               5400000
  Kaneko                            5400000
  Totsuka                           5400000
  Kato                              6500000
  Sinagawa                          6500000

  16 rows selected.


上記例では、比較演算子が>となるので、
条件としてはsalaryが4000000より大きい従業員が表示されました。
では次に平均年収より高い給料の従業員を検索したいとします。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE salary >= avg(salary);
  WHERE salary >= avg(salary)
                  *
  ERROR at line 2:
  ORA-00934: group function is not allowed here


条件句の値にグループ関数を使用してみましたが、
エラーとなりました使用できないようです。
この場合も副問合せを使用します。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE salary >= ( SELECT avg(salary)
    3  FROM employees);
    
  FIRST_NAME                         SALARY
  ------------------------------ ----------
  Suzuki                           10000000
  Sato                              8000000
  Watanabe                          6000000
  Kataoka                           6500000
  Harada                            6200000
  Koda                              6400000
  Saito                             6700000
  Akagi                             6400000
  Kato                              6500000
  Sinagawa                          6500000

  10 rows selected.


条件句の値は定数を使用する必要があり、
このようにグループ関数を使用することは出来ません。
また副問合せはWHERE句だけでなく色々な部分で使用することができます。

・WHERE句
・FROM句
・HAVING句

では、次の実行例を見ていきましょう。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE  salary > ( SELECT salary
    3                    FROM employees
    4                    WHERE first_name = 'Matsuoka');

  no rows selected


上記実行例では一行も表示されませんでした。
今回の副問合せ部分はfirst_nameがMatsuokaという条件で検索を行っています。

しかし、従業員の中にMatsuokaという従業員は存在しないため、
副問合せの結果はNULLが返されます。

その結果、主問合せの条件句の値にNULLが使用されるため、
「no rows selected」となります。

以前、紹介しましたがNULLは値がない状態となるため、比較できないため、
条件句の値にNULLが入ってしまうと正しい結果が返されなくなります。

では、次の実行例です。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE  salary = ( SELECT salary
    3                    FROM employees
    4                    WHERE first_name LIKE 'K%');
  WHERE  salary = ( SELECT salary
                    *
  ERROR at line 2:
  ORA-01427: single-row subquery returns more than one row


上記SQLを実行したところ、エラーが発生しました。
なぜエラーになったのでしょうか。

副問合せの条件句はfirst_nameが先頭Kがつくとい条件となっています。
Kから始まる従業員は複数存在するため、

副問合せの結果は複数行返されます。つまり複数行副問合せとなっているのです。

単一行副問合せでは=を使用することが出来ましたが、

複数行副問合せでは=は使用できないため、エラーとなっています。
複数行副問合せで使用できる演算子としてINがありますので、
=をINに変更して実行してみます。

  SQL> SELECT first_name,salary FROM employees
    2  WHERE  salary IN ( SELECT salary
    3                     FROM employees
    4                     WHERE first_name LIKE 'K%');

  FIRST_NAME                         SALARY
  ------------------------------ ----------
  Sinagawa                          6500000
  Kato                              6500000
  Kataoka                           6500000
  Akagi                             6400000
  Koda                              6400000
  Totsuka                           5400000
  Kaneko                            5400000
  Tao                               5400000
  Sato                              5400000
  Kamata                            4000000
  Nakamura                          4000000
  Kuroda                            4000000

  12 rows selected.


副問合せ部分のfirst_nameがKから始まる従業員のsalaryは、

  FIRST_NAME                         SALARY
  ------------------------------ ----------
  Kataoka                           6500000
  Koda                              6400000
  Kaneko                            5400000
  Kato                              6500000
  Kuroda                            4000000
  Kamata                            4000000


となっています。そのため上記結果と同じsalaryの値を持つ従業員が表示されます。
複数行副問合せでは、単一行副問合せで使用していた比較演算子は
使用できませんのでご注意ください。

では複数行副問合せで使用できる演算子を確認していきましょう。

複数行服問合せで使用可能な演算子
IN 等しい
<ANY より大きい
>ANY より小さい
<ALL 以下
>ALL 以上

複数行返される時に使用できる演算子として、
INがありますがもうこちらは大丈夫ですね。
INは複数の値を持つことが出来る演算子です。

次にANYがありますが、これはどのような範囲を持つ演算子でしょうか。

例えば副問合せの結果で4000,6000が返ってきたとします。
ANYは日本語では「いずれか」という意味です。<は「小なり」です。

グラフで表すと、小なりなので4000より小さい、6000より小さい範囲です。
 -------------------------------+
 ------------+                  |
             |                  |
 +-----------+------------------+--------
 0         4000               6000

上記のグラフからANYはいずれかという意味なので、
4000,6000のいずれも含むの範囲とは、6000以下の範囲となります。
※4000は6000以下,6000も6000以下のためいずれも含まれます。

結論としては上記の例での範囲は6000以下という条件となります。
             +---------------------------
             |                  +--------            
             |                  |
 +-----------+------------------+--------
 0         4000               6000
いずれも含む範囲は、4000以上となります。

また=ANYという演算子もあります。イコールは等しいという意味ですから、
4000,6000のいずれかと等しいという意味となるので、INと同じ意味となります。

次にALLですが、考え方はANY演算子と同じです。ALLはすべてという意味の為、
上記の例と同じ考え方と同じです。

>ALLは6000より大きいとなります。

=ANYは演算子としてありましたが、=ALLはありません。ALLは全てという意味で、
4000,6000すべてを含む範囲というのはないからです。
ただし、!=ALLはNOT INと同じ意味となります。

では実行例です。

  SQL> SELECT first_name,salary 
    2  FROM   employees
    3  WHERE  salary <ANY ( SELECT salary
    4                        FROM employees
    5                        WHERE first_name LIKE 'K%');

  FIRST_NAME                         SALARY
  ------------------------------ ----------
  Kuroda                            4000000
  Kamata                            4000000
  Nakamura                          4000000
  Yamamoto                          5000000
  Sato                              5400000
  Kaneko                            5400000
  Totsuka                           5400000
  Tao                               5400000
  Watanabe                          5500000
  Watanabe                          6000000
  Harada                            6200000
  Koda                              6400000
  Akagi                             6400000

  13 rows selected.


上記の副問合せの結果は、
6500000,6400000,5400000,4000000の4つの結果が返ってきました。
演算子は「小なり」なので、上記値のいずれも含む範囲は6500000以下となります。

そのため、上記の結果はsalaryの値が6500000以下の従業員が表示されています。

  SQL> SELECT first_name,salary
    2  FROM   employees
    3  WHERE  salary <ALL ( SELECT salary
    4                        FROM employees
    5                        WHERE first_name LIKE 'K%');

  no rows selected


上記の例では、ALL演算子を使用しています。
6500000,6400000,5400000,4000000の4つの結果が返ってきました。
演算子は「小なり」なので、上記値のすべてを含む範囲は4000000より小さいとなります。
salaryが4000000より小さい従業員は存在しないため結果は
「no rows selected」となりました。

ANY,ALL演算子は頭ですぐに理解することは難しいですが、
上記のようにグラフを書いてみると理解しやすいと思いますので、
悩んだときは試してみてください。

以上が副問合せとなります。長い期間SQL文のSELECTに関する構文を見ていきましたが、SELECT文は以上となります。
ここまで覚えてきた内容を押さえておけば、
色々なデータ取得にも対応できるようになっていると思います。

次回はDML文についてご紹介していきます。