はじめに
この記事は､初学者である自分が相関サブクエリについて学習した内容を残す目的で書いています｡記事に書くレベルも初学者相応なのでご留意ください｡
環境について
MySQLを想定しています｡また実行例に記載のテーブルは､SQL 第2版 ゼロからはじめるデータベース操作のshohinテーブルを使用しています｡
相関サブクエリとは
相関サブクエリは､小分けにしたグループ内で比較するためのサブクエリです｡実際の動作を見たほうが早いので､スカラ・サブクエリと比較しつつ､クエリを実行していきます｡例えば各商品情報を格納する以下のようなテーブルがあったとします｡
mysql> SELECT * FROM shohin;
+-----------+-----------------------+--------------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi |
+-----------+-----------------------+--------------------+--------------+--------------+------------+
| 0001 | Tシャツ | 衣服 | 1000 | 500 | 2009-09-20 |
| 0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11 |
| 0003 | カッターシャツ | 衣服 | 4000 | 2800 | NULL |
| 0004 | 包丁 | キッチン用品 | 3000 | 2800 | 2009-09-20 |
| 0005 | 圧力鍋 | キッチン用品 | 6800 | 5000 | 2009-01-15 |
| 0006 | フォーク | キッチン用品 | 500 | NULL | 2009-09-20 |
| 0007 | おろしがね | キッチン用品 | 880 | 790 | 2008-04-28 |
| 0008 | ボールペン | 事務用品 | 100 | NULL | 2009-11-11 |
+-----------+-----------------------+--------------------+--------------+--------------+------------+
テーブルの販売単価の平均よりも高い販売単価をもつレコードを抽出したいといった場合､スカラ・サブクエリを使います｡
実行例
mysql> SELECT shohin_id, shohin_mei, hanbai_tanka
-> FROM shohin
-> WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
-> FROM shohin);
+-----------+-----------------------+--------------+
| shohin_id | shohin_mei | hanbai_tanka |
+-----------+-----------------------+--------------+
| 0003 | カッターシャツ | 4000 |
| 0004 | 包丁 | 3000 |
| 0005 | 圧力鍋 | 6800 |
+-----------+-----------------------+--------------+
販売単価の平均は､2,097.5円((1000 + 500 + 4000 + 3000 + 6800 + 500 + 880 + 100) ÷ 8)なので､上記クエリは以下の SELECT 文が実行されています｡
mysql> SELECT shohin_id, shohin_mei, hanbai_tanka
-> FROM shohin
-> WHERE hanbai_tanka > 2097.5
今度は商品分類毎の平均販売単価を算出し､この平均値より高いレコードを抽出することを考えます｡例えば事務用品の平均販売単価は300円((500 + 100)÷2)なので､同じ商品分類で３００円より高い販売単価を持つ「穴あけパンチ」だけ抽出するといったことを商品分類毎に行うイメージです｡
パッと思いつくのは､GROUP BY句で shohin_bunrui をグループ化し､AVG(集計関数)を実行することです｡
ただし､この方法でサブクエリを実行するとエラーになります
mysql> SELECT shohin_id, shohin_mei, hanbai_tanka
-> FROM shohin
-> WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
-> FROM shohin
-> GROUP BY shohin_bunrui;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5
なぜエラーになるかというと､内側のクエリ(右側)の結果が以下のように1つではないためです｡
mysql> SELECT AVG(hanbai_tanka)
-> FROM shohin
-> GROUP BY shohin_bunrui;
+-------------------+
| AVG(hanbai_tanka) |
+-------------------+
| 2500.0000 |
| 300.0000 |
| 2795.0000 |
+-------------------+
つまり､以下が実行されたのと同じ意味になります｡
SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka
FROM shohin AS s1
WHERE hanbai_tanka > 2500.0000,
300.0000,
2795.0000;
3行の値と比較するので､SQLがどの値を選べば良いか判断できずエラーになります｡「サブクエリの結果が単一になる」というスカラ・サブクエリの条件に違反しているんですね｡
ここで登場するのが相関サブクエリです｡以下のように実行します｡
mysql> SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka
-> FROM shohin AS s1
-> WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)
-> FROM shohin AS s2
-> WHERE s1.shohin_bunrui = s2.shohin_bunrui
-> GROUP BY shohin_bunrui);
+-----------+-----------------------+--------------------+--------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka |
+-----------+-----------------------+--------------------+--------------+
| 0002 | 穴あけパンチ | 事務用品 | 500 |
| 0003 | カッターシャツ | 衣服 | 4000 |
| 0004 | 包丁 | キッチン用品 | 3000 |
| 0005 | 圧力鍋 | キッチン用品 | 6800 |
+-----------+-----------------------+--------------------+--------------+
以下の平均値より高いレコードが算出されました｡
- 事務用品：300
- 衣服：2500
- キッチン用品：2795
一体何が起きたのでしょうか｡ポイントは WHERE s1.shohin_bunrui = s2.shohin_bunrui の記述です｡
この記述を一言で言うなら､「s2のレコードを､s1テーブルのレコードと同じ書品分類のレコードだけにする」です｡これによりサブクエリの結果が1行(しかも外側のレコードと同じ商品分類のAVG(hanbai_tanka)を返す)になってるんですね｡
イメージしやすいよう実行される処理順に動作を見ていきます｡
-
FROM shohin AS s1 が実行され､s1という別名がついたshohinテーブルが外側クエリの実行対象になります｡
-
WHERE hanbai_tanka が実行され､s1テーブルの中の1行が､WHERE句の比較対象に選出されます｡
ここではイメージしやすいよう､ボールペンのレコードが選ばれたとします｡0008 | ボールペン | 事務用品 | 100 | NULL | 2009-11-11 -
WHERE hanbai_tanka の右側(内側)のサブクエリが実行開始します｡
-
FROM shohin AS s2 が実行され､s2という別名がついたshohinテーブルがサブクエリの実行対象になります｡
-
WHERE s1.shohin_bunrui = s2.shohin_bunrui が実行され､s2が '事務用品'のみのレコードになります｡
以下のテーブル情報になったイメージです｡mysql> SELECT * -> FROM shohin -> WHERE shohin_bunrui = '事務用品'; +-----------+--------------------+---------------+--------------+--------------+------------+ | shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi | +-----------+--------------------+---------------+--------------+--------------+------------+ | 0002 | 穴あけパンチ | 事務用品 | 500 | 320 | 2009-09-11 | | 0008 | ボールペン | 事務用品 | 100 | NULL | 2009-11-11 | +-----------+--------------------+---------------+--------------+--------------+------------+ -
GROUP BY shohin_bunrui が実行され､事務用品のレコードがグループされます｡
-
SELECT AVG(hanbai_tanka)が実行され､事務用品グループにおけるhanbai_tankaの平均値がサブクエリの結果として返されます｡以下クエリの結果が返されたのと同じです｡
mysql> SELECT AVG(hanbai_tanka) -> FROM shohin -> WHERE shohin_bunrui = '事務用品' -> GROUP BY shohin_bunrui; +-------------------+ | AVG(hanbai_tanka) | +-------------------+ | 300.0000 | +-------------------+ -
外側クエリに戻り､ボールペンのhanbai_tanka > 300.0000の比較式が評価されます｡ボールペンの販売単価は100円なので､この結果は false となり比較式が終了します｡
-
s1テーブルの別のレコードが選出され､項番3〜8を繰り返します｡
-
WHERE hanbai_tanka > (SELECT AVG(hanbai_tanka)...) が true のレコードに対し､ 外側クエリのSELECT文が実行されます｡
つまり､WHERE s1.shohin_bunrui = s2.shohin_bunruiを記述したことで内側のs2テーブルのレコードが外側クエリの比較レコード(この例ではボールペン)と同じshohin_bunruiとなった結果､以下を実現しています｡
- GROUP BY句で､ボールペンと同じ商品分類のグループが残る｡結果､後続のAVG関数の返り値が単一のレコードとなり､スカラ・サブクエリの条件を満たすようになる｡
- ボールペンと同じ商品分類のグループがAVG関数の対象になる｡
この動作により､小分けにしたグループ内で比較するということを実現しています｡
本記事投稿の背景
相関サブクエリの説明として､ある列Aのデータとそのデータの集約値(今回でいう販売単価の平均値)の比較を同じグループ(今回で言う同じshonhin_bunrui)同士で行うといったものを見かけます｡SQL 第2版 ゼロからはじめるデータベース操作でも同じような表現でした｡ですが､初学者からすると「なんで WHERE テーブル別名1.列 = テーブル別名2.列 と記述するだけで上記説明の動作が実現できるんだ？」という感想でした｡実際に行われる処理の流れをひとつひとつ追っていくことで､この疑問が解消されました｡WHERE句で内側テーブルの情報がフィルタリングされていたんですね｡
最後に
拙い記事ですがここまで見ていただき､ありがとうございました｡なお本記事はわかり易さを優先して作成したため､厳密には正しくないという可能性もあります｡その場合は､ご指摘いただけますと幸いです｡
以上です｡