副問合せの使用による問合せの解決
問８
次のSALGRADE表を確認してください。
GRADE LOWSAL HIGHSAL
---------- ---------- ----------
1 1000 3999
2 4000 6999
3 7000 9999
4 10000 19999
5 20000 99999
あなたは、給与が最も高い従業員の名前と等級を出力しようと考えています。どのSQL文を使用しますか。すべて選択しなさい。
-
a. SELECT E.last_name,G.grade
FROM employees E JOIN salgrade G
ON (SELECT MAX(salary) FROM employees)
BETWEEN G.lowsal AND G.highsal; -
b. SELECT E.last_name,G.grade
FROM employees E, salgrade G
WHERE E.salary BETWEEN G.lowsal AND G.highsal
AND (SELECT MAX(salary) FROM employees)
BETWEEN G.lowsal AND G.highsal; -
c. SELECT E.last_name,G.grade
FROM employees E, salgrade G
WHERE E.salary BETWEEN G.lowsal AND G.highsal
AND E.salary >= ALL (SELECT salary FROM employees); -
d. SELECT E.last_name,G.grade FROM employees E
JOIN salgrade G
ON E.salary BETWEEN G.lowsal AND G.highsal
ORDER BY E.salary DESC FETCH FIRST 1 ROWS WITH TIES;
この設問で主に伝えたいことは、「副問合せって、BETWEEN AND演算子の比較対象列としても使用できるんですよ」ということですが、他のカテゴリの復習も兼ねて、問8と同様に、1つの結果を求める方法は複数通りあることを織り交ぜました
結合にはイコール「=」以外の演算子を使用する「非等価結合」というのがあります。恐らくその例のほとんどは、BETWEEN AND演算子を使用したものでしょう。本講座の第8回で解説したように、単一行単一列の副問合せは列を指定できるところで使用できるので、選択肢a、bのように「副問合せ BETWEEN 値1 AND 値2」という使い方が可能です。
また、結合の記述の仕方として、選択肢b、cのように、JOIN句を使用せずにFROM句にすべての表名を記述するという方法があります。この記述方法をいま業務で使うべきではありませんが、過去作成された中には、FROM句に複数の表名を記述したSQL文が残っているかもしれません。実務では自分でプログラミングをするばかりではなく、人の作成したプログラムをメンテナンスすることもあるでしょうから、12cSQL試験でも「こういう書き方もあるんだ」ということ知っておいてほしい。そういうオラクル側の思いから、出題される可能性があります。
選択肢aにはSELECT MAX(salary) FROM employeesという副問合せが使用されていることから、「給与が最も高い従業員」を求めていると勘違いをした人がいることでしょう。残念ながらそうはいきません。
EMPLOYEES表からは全行取り出します。なぜか？ それは、ON句の条件は結合条件にはなっていないからです（こういう結合をクロス結合といいます）。また、この条件は、EMPLOYEES表の各行の評価はしていません。ON句で行っている比較は真（TRUE）を返しているだけなので、SALGRADE表からはMAX(salary)の該当等級である5しか取り出しませんが、EMPLOYEES表からは全行が出力されます。結果、次の実行例が示すとおり、選択肢aでは設問の意図を満たすことはできません（選択肢aは不正解）。
SQL> --選択肢a SQL> SELECT E.last_name,G.grade 2 FROM employees E JOIN salgrade G 3 ON (SELECT MAX(salary) FROM employees) 4 BETWEEN G.lowsal AND G.highsal; LAST_NAME GRADE --------------- ---------- Abel 5 Allen 5 （途中省略） Whalen 5 Zlotkey 5 108行が選択されました。
選択肢aとよく似た選択肢bは、設問が求めている結果を得られています。得られた理由は、結合条件E.salary BETWEEN G.lowsal AND G.highsalが記述されているからです。
そのうえで、SALGRADE表の等級は「給与が最も高い従業員」の等級ですよと絞り込みの条件(SELECT MAX(salary) FROM employees) BETWEEN G.lowsal AND G.highsal)が記述されているので、設問の意図を満たすことができるのです。したがって、選択肢bは正解です（以下は選択肢bでの実行例）。
SQL> --選択肢b SQL> SELECT E.last_name,G.grade 2 FROM employees E, salgrade G 3 WHERE E.salary BETWEEN G.lowsal AND G.highsal 4 AND (SELECT MAX(salary) FROM employees) 5 BETWEEN G.lowsal AND G.highsal; LAST_NAME GRADE --------------- ---------- King 5
選択肢cは副問合せの復習です。複数行副問合せの場合、INの他に、比較演算子にANYやALLが使えましたね。>=ALLは副問合せが返すすべての値以上ですから、言い換えれば「副問合せが返す最大値以上」という意味でした。
ということは、E.salary >= ALL (SELECT salary FROM employeesは「給与が最も高い従業員」を絞り込む条件になっているのです。したがって、選択肢cは正解です（以下は選択肢cでの実行例）。
SQL> --選択肢c SQL> SELECT E.last_name,G.grade 2 FROM employees E, salgrade G 3 WHERE E.salary BETWEEN G.lowsal AND G.highsal 4 AND E.salary >= ALL (SELECT salary FROM employees); LAST_NAME GRADE --------------- ---------- King 5
「最も高い給与」を求めるのであれば、ORDER BY句を使用して給与の高い順に並べ替えて、最初の1件だけを取り出すという方法もアリですよね。そこで、ORDER BY句の復習として、FETCH FIRSTを使用したのが選択肢dです。WITH TIESを使用すれば同額の人がいたとしても問題ありませんね。選択肢dは正解です（以下は選択肢dでの実行例）。
SQL> --選択肢d SQL> SELECT E.last_name,G.grade FROM employees E 2 JOIN salgrade G 3 ON E.salary BETWEEN G.lowsal AND G.highsal 4 ORDER BY E.salary DESC FETCH FIRST 1 ROWS WITH TIES; LAST_NAME GRADE --------------- ---------- King 5
