とある日
問題を解いていくなかで、出てくるINとEXISTSの選択肢。
違いがわからず苦戦したが、現在はその違いを理解している。
基本情報技術者試験を解くためにはもちろんSQLを触る上で、必須となる知識であると思う。
今回使用するサンプル表。
- employees
SQL> SELECT * FROM employees;
EMPNO ENAME YOMI JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- -------------------- -------- ---------- -------- ---------- ---------- ----------
1001 佐藤 sato 社長 01-02-25 500000 10
1002 鈴木 suzuki 事務 1013 00-03-26 200000 20
1003 高橋 takahashi 営業 1007 00-05-30 300000 30000 30
1004 田中 tanaka 営業 1007 02-06-02 355000 50000 30
1005 渡辺 watanabe 部長 1001 02-07-11 280000 20
1006 伊藤 ito 営業 1007 08-01-06 300000 140000 30
1007 山本 yamamoto 部長 1001 00-08-09 285000 30
1008 中村 nakamura 部長 1001 00-09-17 245000 10
1009 小林 kobayashi 主任 1005 06-10-21 300000 20
1010 斉藤 saito 営業 1007 01-12-17 150000 0 30
1011 加藤 kato 事務 1009 06-10-21 110000 20
1012 吉田 yoshida 事務 1007 09-03-13 295000 30
1013 山田 yamada 主任 1005 01-03-13 280000 20
1014 佐々木 sasaki 事務 1008 04-05-02 230000 10
- departments
SQL> SELECT * FROM departments;
DEPTNO DNAME LOC
---------- -------------- ----------
10 管理 大手町
20 研究開発 横浜
30 営業 品川
40 財務 東京
INとEXISTSの概要
INの場合
条件
INは、表の副問合せと式（または式のリスト）を比較します。式（または式のリスト）が副問合せによって返された値と等しい場合、IN条件はTRUEと評価されます。
SQL例
select * from departments where departments.deptno in (select deptno from employees);
DEPTNO DNAME LOC
---------- -------------- ----------
10 管理 大手町
20 研究開発 横浜
30 営業 品川
EXISTSの場合
条件
EXISTSは、表の副問合せの有無を確認します。EXISTSの場合は副問合せで1行以上を返す場合、NOT EXISTSの場合は行を返さない場合に、条件がTRUEと評価されます。
SQL例
select * from departments where exists (select * from employees where departments.deptno = employees.deptno);
DEPTNO DNAME LOC
---------- -------------- ----------
10 管理 大手町
20 研究開発 横浜
30 営業 品川
INとEXISTSの違い
1.構文
IN句は、列名 INだが、EXISTS句だとEXISTSだけになる。
IN : WHERE dept_id IN ... EXISTS : WHERE EXISTS ...
2.サブクリエ
EXISTS句の方にはサブクエリにWHERE句で外表と内表のIDを比較している。IN句ではサブクリエにに関して条件の指定がない。
IN : (SELECT dept_id FROM employees) EXISTS : (SELECT * FROM employees WHERE dept.dept_id = employees.dept_id)
3.サブクリエの結果行
IN句は、データ型と指定した列の個数が同じでないとエラーとなる。
- データ型の不一致バージョン
SQL> select * from departments where departments.deptno in (select hiredate from employees);
select * from departments where departments.deptno in (select hiredate from employees)
*
行1でエラーが発生しました。:
ORA-00932: データ型が一致しません: NUMBERが予想されましたがDATEです。
- 列の個数の不一致バージョン
SQL> select * from departments where (departments.deptno,1) in (select deptno from employees);
select * from departments where (departments.deptno,1) in (select deptno from employees)
*
行1でエラーが発生しました。:
ORA-00947: 値の個数が不足しています。
- データ型の不一致と列の個数の不一致の複合バージョン
SQL> select * from departments where (departments.deptno,1) in (select deptno,hiredate from employees);
select * from departments where (departments.deptno,1) in (select deptno,hiredate from employees)
*
行1でエラーが発生しました。:
ORA-00932: データ型が一致しません: NUMBERが予想されましたがDATEです。
複合の場合、データ型のエラーが優先されるみたいですな。
EXISTS句は、サブクリエが返す値はなんでもよい。
- 文字リテラルバージョン
select * from departments where exists (select 'nandemo' from employees where departments.deptno = employees.deptno);
DEPTNO DNAME LOC
---------- -------------- ----------
10 管理 大手町
20 研究開発 横浜
30 営業 品川
- 複数列バージョン
SQL> select * from departments where exists (select * from employees where departments.deptno = employees.deptno);
DEPTNO DNAME LOC
---------- -------------- ----------
10 管理 大手町
20 研究開発 横浜
30 営業 品川
- NULLバージョン
SQL> select * from departments where exists (select null from employees where departments.deptno = employees.deptno);
DEPTNO DNAME LOC
---------- -------------- ----------
10 管理 大手町
20 研究開発 横浜
30 営業 品川
4.実行順序
INとEXISTSでは、メインクエリとサブクリエの実行順序が違う。
下記は、実行順序のイメージである。
※EXISTSの例をわかりやすくするためSQL例とは違いlenght(dname) = 2を追加している。
lenght関数は、引数の文字数を返してくれる。
IN:サブクリエ→メインクエリ
select * from departments where departments.deptno in (select distinct deptno from employees) and lenght(dname) = 2;
↓ 1.サブクリエのselect distinct deptno from employees;を実行する。
select * from departments where departments.deptno in (10,20,30);
↓ 2.メインクリエのWHEREを比較する。
select * from departments where 10 in (10,20,30) and lenght('管理')= 2 → True
select * from departments where 20 in (10,20,30) and lenght('研究開発')= 2 → False
select * from departments where 30 in (10,20,30) and lenght('営業')= 2 → True
select * from departments where 40 in (10,20,30) and lenght('財務')= 2 → False
3.2でTrueと評価された行だけ出力される。
EXISTS:メインクリエ→サブクエリ
select * from departments where exists (select * from employees where departments.deptno = employees.deptno) and length(dname) = 2;
↓ 1.メインクリエのselect * from departments where length(dname) = 2を実行する。
select * from departments where length(dname) = 2;
DEPTNO DNAME LOC
---------- -------------- ----------
10 管理 大手町
30 営業 品川
40 財務 東京
↓ 2.サブクリエ実行する。
select * from employees where 10 = 10 → True select * from employees where 10 = 20 → False select * from employees where 10 = 30 → False select * from employees where 10 = 40 → False select * from employees where 30 = 10 → False select * from employees where 30 = 20 → False select * from employees where 30 = 30 → True select * from employees where 30 = 40 → False select * from employees where 40 = 10 → False select * from employees where 40 = 20 → False select * from employees where 40 = 30 → False select * from employees where 40 = 40 → True
3.2でTrueと評価された行だけ出力される。
INとEXISTSでは、それぞれのメインクエリとサブクリエにない行が存在している。これは、メインクエリのWHERE句によって行が絞れられた行がサブクリエに。サブクリエで絞られた行がメインクエリによって評価されるためである。
まとめ
INとEXISTSの違いを判別するポイント
サブクリエのSELECTのデータ型、列の個数がメインクエリと同じかどうか。
サブクリエにメインクエリテーブルとサブクリエテーブルの比較している箇所があるかどうか。
これらのポイントを見ていくと問題を解きやすくなる。
個人的に、基本情報技術者試験を解いていくなかで、INとEXISTSの答えの割合は体感で６：４ぐらいな気がします。
INとEXISTSの違いは、NOTがつくとまた複雑になるので気をつけてください。
違いがわからず苦戦した頃を思い出すと懐かしく感じた、今では違いに苦戦することはなくなった。 INとEXISTSの違いで悩む人がこの世からいなくなることを願ってます。
