副問合せ

これは超使います。ちゃんと理解しましょうね。
SQL文の中にSELECT文を入れ子にすることができます。
この入れ子になっている内側のSELECT文を副問合せと言います。
もちろん、外側を主問合せと言います。

SELECT 列名
FROM 表名
WHERE 列名 比較演算子 ( SELECT 列名 FROM 表名
                        [WHERE 条件] );

()で囲んで指定します。
WHERE句だけでなく、HAVINGやFROMにも使えます。
SELECTだけでなく、INSERT文やUPDATE文にも使えます。（10章参照）

例を見たほうがイメージし易いですかね。

SELECT empno, ename, sal
FROM EMPLOYEES
WHERE sal >= 
( SELECT sal FROM EMPLOYEES
  WHERE empno = 1003
);

副問合せのSELECT文が1回実行された後に、その実行結果を元に主問合せが実行されます。

異なる表の副問合せ

使用例を学んでいきましょう。
早速ですが、以下をやってみてください。

・DEPATMENTSテーブル.DNAMEが営業の社員番号、名前、deptnoを表示

主問合せと副問合せのテーブルが違う、の意味がわかりましたか？

SELECT empno, ename, deptno
FROM EMPLOYEES
WHERE deptno = 
( SELECT deptno FROM DEPATMENTS
  WHERE dname = '営業'
);

こんな感じになっていればOKです。

複数の副問合せ

前項同様、なんとなくわかりませんか？やってみましょう。

・DEPATMENTSテーブル.DNAMEが営業で、EMPLOYEESテーブル.EMPNOが1003の
　社員よりも多くの給与をもらっている社員を抽出

なんか無理やり感は否めませんが、複数の副問合せを使いましょうｗ

SELECT empno, ename, sal, deptno
FROM EMPLOYEES
WHERE sal >= 
( SELECT sal FROM EMPLOYEES
  WHERE empno = 1003
)
AND deptno =
( SELECT deptno FROM DEPATMENTS
  WHERE dname = '営業'
);

できました？

WHERE句以外の副問合せ

副問合せは、HAVINGやFROMでも使えます。
では、HAVINGに使ってみましょう。

・部門ごとの平均給与が、全社員の平均給与よりも高い部門名と
　その平均給与

以前にやった事を見直しながらやってみてください。

ちなみに副問合せの実行結果が0件の場合、主問合せにはNULLが返ります。

単一行副問合せ／複数行副問合せ

これまでやってきたのは単一行副問合せですね。
比較演算子には、= , > , >= , < , <= , <> , != , ^= が使えます。

複数行副問合せ

複数件のデータを戻す副問合せです。
WHERE句の比較演算子には複数行演算子を使います。

比較演算子	説明
IN( 値のリスト )	リスト内のいずれかと一致すればTRUE
単一行演算子＋ANY(値のリスト)	リスト内のいずれかの値が条件を満たすとTRUE 例）=ANY(…)、>ANY(…)　など
単一行演算子＋ALL(値のリスト)	リスト内の全ての値が条件を満たすとTRUE 例）<>ALL(…)、<ANY(…)　など

IN

INは以前やりましたよね。それとまぁ同じです。
やってみましょう。

・伊藤さんと山田さんの上司（MGR)を抽出
　empno, ename, job, mgr, deptnoを表示

なんか無理やり感は否めませんが、複数の副問合せを使いましょうｗ

SELECT empno, ename, job, mgr, deptno
FROM EMPLOYEES
WHERE empno IN 
( SELECT mgr FROM EMPLOYEES
  WHERE ename IN ('山田','伊藤')
);

一つ目のINが今回のINですね。

ANY

リストのいずれかの値が(指定された)条件を満たす。
以下の例題は先ほどのものと全く同じです。ただ、回答のINだったところが、=ANYになっています。結果は同じです。
ANY以下のリストのどれかが=ならTRUEってことなので、INと意味は同じになるからです。

・伊藤さんと山田さんの上司（MGR)を抽出
　empno, ename, job, mgr, deptnoを表示

SELECT empno, ename, job, mgr, deptno
FROM EMPLOYEES
WHERE empno =ANY 
( SELECT mgr FROM EMPLOYEES
  WHERE ename IN ('山田','伊藤')
);

ちなみに、>ANY(リスト値)だった場合は、リストのいずれか一つよりも大きい場合にTRUEとなるので、最小値よりも大きい場合と同義になります。
逆だったらどうなるかは言うまでもないでしょう。

ALL

リストの全ての値が(指定された)条件を満たす。
ANYと似たようなものですが、一応解説しておくと。。
<ALL(…)の場合、リスト内の全ての値より小さい場合にTRUEとなります。

・平均給与がもっとも高い部門の平均給与よりも給与が高い社員を抽出
　empno, ename, sal, deptno を表示

SELECT empno, ename, sal, deptno
FROM EMPLOYEES
WHERE sal >ALL 
( SELECT avg(sal) FROM EMPLOYEES
  GROUP BY deptno
);

 

INとNULL値

副問合せがNULLを戻した際の動作として主問合せは0件になる。と説明しました。IN（いずれかに一致でTRUE）の場合はその限りではありませんね。

では、これにNOTを付けたらどうなるでしょう？
次のSQL文を見てください。

SELECT empno, ename
FROM EMPLOYEES
WHERE empno NOT IN
( SELECT mgr FROM EMPLOYEES
);

mgrは上司のempnoです。それがNOT INということは、部下のいない人の一覧を作りたいという意思が窺えますよね。
ですが、この結果は0件です。

「NOT IN(…)」は「<>ALL(…)」と同等になります。つまり、リストに対して全てFALSEにならないといけません。しかし列値とNULLを比較すると結果はNULLになってしまいFALSEになりません。このため、実行結果は0件となってしまいます。
わかりましたか？この点についてはしっかりと理解しておきましょう。

・先ほどの結果が0件のSQLですが、希望の通りに結果を取得するには
　どうしたらよいと思いますか？
　（ちょっと付け足せば出来るのですが、わかりますか？）