副問合せが使用できる場所
私が「12cSQL試験は難易度高いなぁ〜」と思ったのは、次のような設問を見かけたときです。さあ、今回最後の問題！ 十分に悩んでください。
問3
次の中で実行時にエラーになるSQLはどれですか
- a. SELECT (SELECT AVG(salary) FROM employees),last_name,salary FROM employees;
-
b. INSERT INTO employees (employee_id,last_name,email,hire_date,job_id)
VALUES((SELECT MAX(employee_id)+1 FROM employees),'SCOTT','SCOTT',SYSDATE,'SA_REP'); -
c. UPDATE employees SET (salary,manager_id) =
(SELECT salary,manager_id FROM employees WHERE employee_id = 108)
WHERE employee_id = 200; -
d. DELETE FROM employees WHERE salary >=
(SELECT MAX(salary) FROM employees GROUP BY department_id)
実行結果を確認する前に、この設問において重要なことを先にお話しします。
FROM句、WHERE句では、複数列、複数行を含むすべての副問合せを記述できる。
UPDATEのSET句においては、単一列または複数列で、単一行の副問合せを記述することができます。
そして、単一列、単一行を戻す副問合せは、FROM句、WHERE句だけでなく、次に記述することができます。
-
SELECT句 -
INSERTのVALUES句 -
ORDER BY句
| 使用可能な句 | 単一列 | 複数列 | 単一行 | 複数行 |
|---|---|---|---|---|
FROM句 |
○ | ○ | ○ | ○ |
WHERE句 |
○ | ○ | ○ | ○ |
UPDATEのSET句 |
○ | ○ | ○ | × |
SELECT句 |
○ | × | ○ | × |
INSERTのVALUES句 |
○ | × | ○ | × |
ORDER BY句 |
○ | × | ○ | × |
単一列とはただ1つの列を副問合せが戻す場合をいい、複数列とは2つ以上のの値を戻す副問合せのことをいいます。
複数列の場合、副問合せから戻される値と比較する列は、必ず括弧()で囲む必要があります。したがって、選択肢a、b、cは正解です。
SQL> -- 選択肢a
SQL> SELECT (SELECT AVG(salary) FROM employees),
2 last_name,salary FROM employees;
(SELECTAVG(SALARY)FROMEMPLOYEES) LAST_NAME SALARY
-------------------------------- --------------- ----------
6461.83178 King 24000
6461.83178 Kochhar 17000
(途中省略)
6461.83178 Gietz 8300
6461.83178 Allen
108行が選択されました。
SQL> -- 選択肢b
SQL> INSERT INTO employees (employee_id,last_name,email,
2 hire_date,job_id)
3 VALUES((SELECT MAX(employee_id)+1 FROM employees),
4 'SCOTT','SCOTT',SYSDATE,'SA_REP');
1行が作成されました。
SQL> -- 選択肢c
QL> UPDATE employees SET (salary,manager_id) =
2 (SELECT salary,manager_id FROM employees WHERE employee_id = 108)
3 WHERE employee_id = 200;
1行が更新されました。
選択肢dは副問合せから複数行戻されるのに、主問合せの比較演算子が>=ANY、または>=ALLではなく、1行としか比較できない>=を使用しているため、実行時にエラーになります。
SQL> DELETE FROM employees WHERE salary >= 2 (SELECT MAX(salary) FROM employees GROUP BY department_id); (SELECT MAX(salary) FROM employees GROUP BY department_id) * 行2でエラーが発生しました。: ORA-01427: 単一行副問合せにより2つ以上の行が戻されます
いかがでしたか？ 副問合せは、単一行のときは=演算子で比較して、複数行のときはIN演算子で比較する。これさえ分かっていれば正解できるよ、というレベルの出題ではないことがお分かりいただけたでしょうか。
結合や副問合せは実務の中でも重要ですので、投げ出さずに頑張ってください。
