助け合いフォーラム

Oracle DB

Oracle Master Silver SQL 2019(1Z0-071)
問題ID : 26632
問題を開く
EMPLOYEES表から、上司のいる従業員の名前を表示します。
どの問合せを使用しますか(2つ選択して下さい)。

正解

SELECT e.employee_name FROM employees e
WHERE EXISTS (SELECT * FROM employees m WHERE e.manager_id = m.employee_id);

SELECT employee_name FROM employees
WHERE manager_id IN (SELECT employee_id FROM employees);

解説

EXISTS演算子は、副問合せの結果が1行以上返される場合にTRUEとして評価される演算子です。
主問合せのWHERE句に列名と比較演算子を指定する代りに、EXISTS演算子を指定します。

 WHERE EXISTS (副問合せ)

EXISTS演算子で設問の結果を得るには、主問合せで取り出したEMPLOYEES表 e の各行に対して副問合せを実行し、EMPLOYEES表 m のEMPLOYEE_ID列にMANAGER_ID列と同じ値があればTRUEを返し、上司がいる従業員として主問合せの行を表示します。
このSQL文はIN演算子を使用したSQL文にも置き換えられます。

以上より、
・SELECT e.employee_name FROM employees e
 WHERE EXISTS (SELECT * FROM employees m WHERE e.manager_id = m.employee_id);
・SELECT employee_name FROM employees
 WHERE manager_id IN (SELECT employee_id FROM employees);
が正解となります。

正解のSQL文の実行結果は次のようになります。


SQLを表示
SELECT e.employee_name FROM employees e
WHERE EXISTS (SELECT * FROM employees m WHERE e.manager_id = m.employee_id);



SQLを表示
SELECT employee_name FROM employees
WHERE manager_id IN (SELECT employee_id FROM employees);


その他の選択肢については次のとおりです。

・SELECT m.employee_name FROM employees m
 WHERE EXISTS (SELECT * FROM employees e WHERE e.manager_id = m.employee_id);
主問合せの表にMANAGER_IDを持つ表に見立てたEMPLOYEES表 m を、副問合せの表に全従業員のEMPLOYEES表 e を逆に指定しているため、上司の名前が表示されます。

・SELECT e.employee_name FROM employees e
 WHERE EXISTS SELECT * FROM employees m WHERE e.manager_id = m.employee_id;
EXISTS演算子の後の副問合せが括弧()で囲まれていないため、エラーとなります。

参考

【EXISTS演算子】
EXISTS演算子は、副問合せの結果が1行以上返される場合にTRUEとして評価される演算子です。
主問合せのWHERE句に列名と比較演算子を指定する代りに、EXISTS演算子を指定します。

 WHERE EXISTS (副問合せ)

次の例では、JOBS表から従業員がいる職種の一覧を表示しています。


SQLを表示
SELECT j.job_id, j.job_name FROM jobs j
WHERE EXISTS (SELECT job_id FROM employees e WHERE j.job_id = e.job_id);


主問合せで取り出したJOBS表の各行に対して副問合せを実行し、JOBS表とEMPLOYEES表のJOB_IDが一致する場合に条件が成り立ち、職種名が表示されます。
通常は副問合せ→主問合せの順に実行されますが、EXISTS演算子を使用した場合は、主問合せの各行に対してその都度、副問合せが実行され、条件に合う行が存在するかどうか確認していきます。このような手法は「相関副問合せ」と呼ばれます。副問合せの中でそのFROM句に無い表を参照する場合(副問合せの外側にある表を参照する場合)に、相関副問合せとして処理されます。

【NOT EXISTS演算子】
NOT IN演算子では、比較する副問合せの結果にNULL値が含まれていると主問合せは行を返しませんでした。これに対処する方法の1つとしてNOT EXISTS演算子を利用できます。
NOT EXISTS演算子は、主問合せで取り出した1行が副問合せの条件を満たしていない場合、つまり副問合せの結果が1行も返されない場合にTRUEとして評価され、主問合せの結果が返されます。

 WHERE NOT EXISTS (副問合せ)

次の例では、JOBS表から従業員がいない職種の一覧を表示しています。


SQLを表示
SELECT j.job_id, j.job_name FROM jobs j
WHERE NOT EXISTS (SELECT job_id FROM employees e WHERE j.job_id = e.job_id);
上に戻る

なぜ逆にすると上司の名前が表示されるのか

公開日 2023/08/28

解説の下記部分が理解できないです。

・SELECT m.employee_name FROM employees m
 WHERE EXISTS (SELECT * FROM employees e WHERE e.manager_id = m.employee_id);
主問合せの表にMANAGER_IDを持つ表に見立てたEMPLOYEES表 m を、副問合せの表に全従業員のEMPLOYEES表 e を逆に指定しているため、上司の名前が表示されます。

EXISTS条件を使用した相関副問い合わせですよね。
1:主問い合わせで参照している「employees m」から1行取得
2:副問い合わせの条件「e.manager_id = m.employee_id」に従って
  副問い合わせ内で参照している「employees e」から、manager_idとemployee_idが等しい行を取得
3:2で行が取得できたら、主問い合わせのEXISTS条件が「真」になり、
  その行の「employee_name」が返される

上記の流れであっていますでしょうか。
この途中で、マネージャーの名前と従業員の名前が逆になって表示されてしまう仕組みが
イメージできず理解ができません。

自己結合もイメージができず苦手分野ですので、
今回思い切って質問してみました。

ご回答お待ちしています。よろしくお願いいたします。

2023/08/29 20:35

ping-tさん提供の検証用データに基づいて、順をおって動きを見てみるとこんな感じでしょうか。

SELECT e.employee_name FROM employees e WHERE EXISTS (SELECT * FROM employees m WHERE e.manager_id = m.employee_id);

  1. employees e の1行を取得
EMPLOYEE_ID EMPLOYEE_NAME		   MANAGER_ID
----------- ------------------------------ ----------
       1001 山田二郎
  1. 副問合せの条件は「e.manager_id = m.employee_id」なので、副問合せで参照している employees m 表から、m.employee_id と e.manager_id が等しい行を取得する。
  • 上の 1. で employees e から取得した山田二郎の manager_id は null。
  • 副問合せで参照している employees m 表の m.employee_id と一致する行がない。
    -> 副問合せから行が返されないので、主問合せの EXISTS 条件は FALSE。この行の e.employee_name は返されない。(出力されない)
  1. employees e の他の1行を取得
EMPLOYEE_ID EMPLOYEE_NAME		   MANAGER_ID
----------- ------------------------------ ----------
       1008 中山大輔                             1001
  1. 副問合せの条件は「e.manager_id = m.employee_id」なので、副問合せで参照している employees m 表から、m.employee_id と e.manager_id が等しい行を取得する。
  • 上の 3. で employees e から取得した中山大輔の manager_id は 1001。
  • 副問合せで参照している employees m 表の m.employee_id と一致する行(山田二郎)がある。
    -> 副問合せから行が返されるので、主問合せの EXISTS 条件は TRUE。この行の e.employee_name は返される。(出力される)

=> 結果として、employees 表 の manager_id 列に、同表内に存在する employee_id の値を持つ行(上司のいる従業員)が返される。


SELECT m.employee_name FROM employees m WHERE EXISTS (SELECT * FROM employees e WHERE e.manager_id = m.employee_id);

  1. employees m の1行を取得
EMPLOYEE_ID EMPLOYEE_NAME		   MANAGER_ID
----------- ------------------------------ ----------
       1001 山田二郎
  1. 副問合せの条件は「e.manager_id = m.employee_id」なので、副問合せで参照している employees e 表から、m.employee_id と e.manager_id が等しい行を取得する。
  • 上の 1. で employees m から取得した山田二郎の employee_id は 1001。
  • 副問合せで参照している employees e 表の e.manager_id と一致する行(中山大輔、米村真司、等)がある
    -> 副問合せから行が返されるので、主問合せの EXISTS 条件は TRUE。この行の m.employee_name は返される。(出力される)
  1. employees e の他の1行を取得
EMPLOYEE_ID EMPLOYEE_NAME		   MANAGER_ID
----------- ------------------------------ ----------
       1008 中山大輔                             1001
  1. 副問合せの条件は「e.manager_id = m.employee_id」なので、副問合せで参照している employees m 表から、m.employee_id と e.manager_id が等しい行を取得する。
  • 上の 3. で employees e から取得した中山大輔の employee_id は 1008。
  • 副問合せで参照している employees e 表の e.manager_id と一致する行がない。
    -> 副問合せから行が返されないので、主問合せの EXISTS 条件は FALSE。この行の m.employee_name は返されない。(出力されない)

=> 結果として、employees 表の manager_id 列に存在する値を、employee_id 列の値として持つ行(部下を持つ従業員(上司))が返される。


コメント

c coeri24

2023/08/30 14:05

非常に明瞭に伝わりました。 大感謝です。 ご丁寧で見易いコメントをしていただき ありがとうございました。

この返信に対して
コメントを記入できます

この投稿に対して返信しませんか?