11-03.サブクエリ（Subquery）【Multiple-Row Subquery】

■Multiple-Row Subqueryとは？
　-１つ以上の行を返すSubquery
　-論理演算子(IN, NOT IN, ANY, ALL, EXISTS)のみ使用可能

■IN演算子
　-IN演算子は１つのコラムに複数の'='条件を持つ場合使用
　-ORはINを含む。INで表現できるのはORでも表現できる。
　-しかし、ORでの表現をINで表現出来ない場合もある。（ORでLIKEなどを使用した場合）
　-INは必ず１つのコラムが比較されるので後々INDEX構成の参考に有利
　-結論的にはORよりINを使用するのが望ましい

--部署別に給与が一番高い社員の情報を出力
SQL> SELECT empno,ename,sal,deptno
  2  FROM emp
  3  WHERE sal IN (SELECT MAX(sal)
  4                FROM emp
  5                GROUP BY deptno);

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7698 BLAKE            2850         30
      7788 SCOTT            3000         20
      7839 KING             5000         10
      7902 FORD             3000         20

■ANY演算子
　-ANY演算子はSubqueryの複数の結果からどれか１つだけでも条件に合えば行を返す

--SALEMANより給与が高い社員の名前と給与を出力
SQL> SELECT ename, sal
  2  FROM emp
  3  WHERE deptno != 20
  4  AND sal > ANY(SELECT sal
  5                FROM emp
  6                WHERE job = 'SALESMAN');

ENAME             SAL
---------- ----------
KING             5000
BLAKE            2850
CLARK            2450
ALLEN            1600
TURNER           1500
MILLER           1300

■ALL演算子
　-ALL演算子はSubquery結果すべてが条件に合えば行を返す

--すべてのSALESMANより給与が高い社員の社員名と給与を出力
SQL> SELECT ename, sal
  2  FROM emp
  3  WHERE deptno != 20
  4  AND sal > ALL(SELECT sal
  5                FROM emp
  6                WHERE job = 'SALESMAN');

ENAME             SAL
---------- ----------
CLARK            2450
BLAKE            2850
KING             5000

■EXISTS演算子
　-EXISTS演算子はSubqueryデータが存在するかをチェックしTRUE/FALSEを返す。
　-EXISTSには必ずメインクエリと繋がるJOIN条件が必要。
　-subqueryで結果行が見つかったら、inner queryを中断し、TRUEを返す。

--empテーブルで社員が所属した部署番号のみ検索する場合、
--検索しようとする対象はdeptテーブルだが、empテーブルとJoinし部署番号をチェックする必要がある。
--２つのテーブルの関係が1:Mなのでempテーブルをすべてアクセスし、DISTINCTで重複を除去しなければならない。
SQL> SELECT DISTINCT d.deptno, d.dname
  2  FROM dept d, emp e
  3  WHERE d.deptno = e.deptno;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES


--EXISTSを使用するSubqueryに変更！
--検索する対象のみFROMに入れてempテーブルはチェックだけするためにEXISTSに入れる
--これにより処理速度が早くなる。
SQL> SELECT d.deptno, d.dname
  2  FROM dept d
  3  WHERE EXISTS
  4      (SELECT 1
  5       FROM emp e
  6       WHERE e.deptno = d.deptno);

    DEPTNO DNAME
---------- --------------
        20 RESEARCH
        30 SALES
        10 ACCOUNTING