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文についてご紹介していきます。