助け合いフォーラム

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 m.employee_name FROM employees m
WHERE EXISTS (SELECT * FROM employees e WHERE e.manager_id = m.employee_id);

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);

正解

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);

参考URL

Oracle Database SQL言語リファレンス「EXISTS条件」

上に戻る

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

c coeri24

公開日 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条件を使用した相関副問い合わせですよね。
１：主問い合わせで参照している「employees m」から１行取得
２：副問い合わせの条件「e.manager_id = m.employee_id」に従って
　　副問い合わせ内で参照している「employees e」から、manager_idとemployee_idが等しい行を取得
３：２で行が取得できたら、主問い合わせのEXISTS条件が「真」になり、
　　その行の「employee_name」が返される

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

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

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

r rink_rew

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);

employees e の1行を取得

EMPLOYEE_ID EMPLOYEE_NAME		   MANAGER_ID
----------- ------------------------------ ----------
       1001 山田二郎

副問合せの条件は「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 は返されない。（出力されない）

employees e の他の1行を取得

EMPLOYEE_ID EMPLOYEE_NAME		   MANAGER_ID
----------- ------------------------------ ----------
       1008 中山大輔                             1001

副問合せの条件は「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);

employees m の1行を取得

EMPLOYEE_ID EMPLOYEE_NAME		   MANAGER_ID
----------- ------------------------------ ----------
       1001 山田二郎

副問合せの条件は「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 は返される。（出力される）

employees e の他の1行を取得

EMPLOYEE_ID EMPLOYEE_NAME		   MANAGER_ID
----------- ------------------------------ ----------
       1008 中山大輔                             1001

副問合せの条件は「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

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

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

助け合いフォーラム