相関副問い合わせ

【score2】テーブルから、『subject_id=’A01’の科目で、自分のクラスの平均点を上回ったレコード』を取り出すSQLを考えてみます。

順番に組み立てていきましょう。
まず、『subject_id=’A01’の科目で、50点を上回ったレコード』を取り出すならば

SQL
SELECT * FROM score2
WHERE subject_id='A01' AND
score>=50;

です。

次に、『クラス毎のsubject_id=’A01’の科目の平均点』を求めるには、

SQL
SELECT AVG(score) FROM score2 WHERE subject_id='A01' GROUP BY class;

とすればよいでしょう。

しかし、これらを組み合わせて

SQL
SELECT * FROM score2
WHERE subject_id='A01' AND
score>=(SELECT AVG(score) FROM score2 WHERE subject_id='A01' GROUP BY class);

と記述してもエラーになってしまいます。
なぜなら、副問い合わせの結果が

と複数行になってしまっているからです。
かといって、IN演算子を使って

SQL
SELECT * FROM score2
WHERE subject_id='A01' AND
score IN (SELECT AVG(score) FROM score2 WHERE subject_id='A01' GROUP BY class);

とすると、エラーにはなりませんが結果が1件も出てきません。
IN演算子は、『値が副問い合わせの結果（この例だと49または62）のいずれかに＜等しい＞』という意味です。これではたまたま平均点ちょうどをとった学生だけが抽出されることになり、大小の比較が出来ません。
このような場合は、以下のようにします。

SQL
SELECT * FROM score2 t1
WHERE subject_id='A01' AND
score >= (SELECT AVG(score) FROM score2 t2
WHERE subject_id='A01' AND t1.class=t2.class);

ちょっとややこしいのですが、これで以下のような結果を得ることが出来ます。

この構文では、
『t1.class=t2.class』
の部分で副問い合わせの条件式に主問い合わせのレコードの値を用いている点に特徴があります。

このように主問い合わせと副問い合わせの関係を記述する物を『相関副問い合わせ』といいます。

副問い合わせの実行手順

相関副問い合わせは、内部的な実行の手順も通常の副問い合わせとはまったく違います。
『＜自分のクラスの＞平均点以上のレコード』ではなく、『全体の平均点以上のレコード』を得る

SQL
SELECT * FROM score2
WHERE subject_id='A01' AND
score >= (SELECT AVG(score) FROM score2
WHERE subject_id='A01');

と比較して考えてみます。一見よく似たSQLですが、『全体の平均点以上のレコード』を求めるSQLは普通の副問い合わせを用いたSQLです。
この場合、副問い合わせである

SQL
SELECT AVG(score) FROM score2 WHERE subject_id='A01';

は、主問い合わせから独立して実行することが出来ます。
普通の副問い合わせでは、副問い合わせの部分が先に実行されて

という結果が得られます。
これを用いて、主問い合わせでは

SQL
SELECT * FROM score2
     WHERE subject_id='A01' AND
           score >= 55.5;

という内容が実行されて

という結果を得る、という順番で実行されます。
副問い合わせ・主問い合わせの順に、それぞれ1度ずつしか実行されません。

それに対し、相関副問い合わせを用いた

SQL
SELECT * FROM score2 t1
     WHERE subject_id='A01' AND
           score >= (SELECT AVG(score) FROM score2 t2 
                     WHERE subject_id='A01' AND t1.class=t2.class);

の場合は、

・まず主問い合わせで【t1（score2のエイリアス）】テーブルのレコード1件に注目し、
・そのレコードのclassカラムの値（t1.class）を使って副問い合わせを実行し、
・副問い合わせの結果を使って注目するt1のレコードが抽出対象となるか判断する
・以上の操作を、t1テーブルのすべてのレコードについて行う

という複雑な手順が実行されます。
つまり、通常の副問い合わせと異なり、主問い合わせを評価する間に副問い合わせが何度も評価されているのです。

多くの場合、主問い合わせと副問い合わせで同じテーブルの別のレコードを参照することになるため、副問い合わせの条件式の記述ではテーブルの別名の指定が必要になります。

テーブルの結合

相関副問い合わせを用いる場合でもテーブルを結合することが出来ます。
たとえば、該当する学生の名前を表示するならば、【student_list2】テーブルを結合して、

SQL
SELECT * FROM score2 t1
     NATURAL JOIN student_list2
     WHERE subject_id='A01' AND
           score >= (SELECT AVG(score) FROM score2 t2 
                     WHERE subject_id='A01' AND t1.class=t2.class);

とすれば、

練習

score2およびstudent_list2を用い、いずれかの科目で平均点以上の点をとった学生を抽出するSQLを記述しなさい。classおよびnumで昇順に並べ替えること。

コメント

タイトルとURLをコピーしました