相関副問い合わせとは
副問い合わせについてはこちらの記事を参照してください。
副問い合わせのSELECT文で主問い合わせのテーブルの列を参照するものを相関副問い合わせ(相関サブクエリ)と言います。
SQLでは同じ行内の列同士を比較することは簡単にできます。単にWHERE句にid 1 = id 2 と書けばよいからです。
一方、異なる行間で列同士を比較することは簡単ではありません。ただ、SQLで行間比較ができないということではありません。行間比較をする際に便利なのが相関副問い合わせになります。
では実際に相関副問い合わせを実行してみましょう。
以下テーブル(テーブル名:teachers)を使用します。
相関副問い合わせを使って、addressの平均年齢より若い人を表示します。
ここでポイントなのが、「address」ということです。例えば東京都の教師であれば、東京都に属する教師の平均年齢が基準となります。つまり上記テーブルの場合、東京都に属するのは「タロウ、イチロー、タカシ」の3名です。
この3人の中で、3人の平均年齢より下の人を表示します。(埼玉県も同様です。)
SELECT name, age, address FROM teachers as t1 WHERE t1.age < (SELECT AVG(age) FROM teachers as t2 WHERE t1.address = t2.address) ;
ここで注目していただきたいのが、WHERE t1.address = t2.address の部分です。
WHERE句で指定された副問い合わせは、外側のクエリの結果の1行ごとに実行され、その外側のクエリの結果のaddress の値を副問い合わせ内で利用しています。
教師全体の平均年齢より若い人を表示したい場合は、相関副問い合わせではない副問い合わせを利用します。
SELECT name, age, address FROM teachers WHERE age < (SELECT AVG(age) FROM teachers) ;
こちらは相関副問い合わせではないので、単に全体の平均年齢より若い人を表示しただけになります。
今回の学習ポイント
・相関副問い合わせでは、内側のクエリは外側のクエリの結果1行ごとに実行される。
・行間の値を比較する時に相関副問い合わせを利用すると便利。
練習問題
問題として使用するデータは「SQL練習問題」のものを使用しています。
以下からダウンロード可能です。
以下のようにしてworldcup2014データベースを作成し、インポートしてください。
※worldcup2014.zip解凍してできるworldcup2014.sqlファイルをCドライブ直下に配置した場合。
CREATE DATABASE worldcup2014; USE worldcup2014; Source C</span>/worldcup2014.sql
- countriesテーブルの中から平均ランキングより低い国を表示する。
以上、相関副問い合わせについてでした。