SQL ゼロから始めるデータベース操作 第５章 サブクエリ・相関サブクエリについて
SQL勉強シリーズ、今回はサブクエリ・相関サブクエリについて勉強していきます。
サブクエリ周りがSQLで一番苦手としているので、しっかり理解していきます。
キーワード
サブクエリ スカラサブクエリ 相関サブクエリ
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2013/08/28
- メディア: Kindle版
- この商品を含むブログ (4件) を見る
サンプルテーブル
前回からサンプルテーブルのデータを変えました。(フィールドは別)
日本語を使うと、フィールドの区切りの|がずれてしまうためです。
そのため、すべて英語にしています。
テーブル名はSampleです。
| id | Name | VALUE | Date | Category |
|---|---|---|---|---|
| 0001 | Car | 2000000 | 2009-11-25 | Vehicle |
| 0002 | Pencil | 200 | 2009-11-27 | Stationery |
| 0003 | Cabinet | 12500 | 2009-11-27 | Faniture |
| 0004 | Eraser | 299 | 2009-12-11 | Stationery |
| 0005 | Moto | 195000 | 2009-12-21 | Vehicle |
| 0006 | Chair | 8500 | 2010-05-12 | Faniture |
| 0007 | Ballpoint pen | 230 | 2010-06-08 | Stationery |
| 0008 | Crayon | 210 | 2010-06-15 | Stationery |
| 0009 | Garage | 5282000 | 2010-09-01 | NULL |
| 0010 | MacBookPro | 0 | 2011-02-03 | NULL |
サブクエリとは？
概要
一言で言えば、一時的なビューというもの。
ビューは中にSELECT文が入っていて、それをFROMなどで呼び出す使い方だったが、
サブクエリの場合直接SELECT文内に書く。
以下、例文
mysql> CREATE VIEW v_sample As
SELECT *
FROM Sample
WHERE Category='Stationery';
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM v_sample;
+------+---------------+-------+------------+------------+
| id | Name | VALUE | Date | Category |
+------+---------------+-------+------------+------------+
| 0002 | Pencil | 200 | 2009-11-27 | Stationery |
| 0004 | Eraser | 299 | 2009-12-11 | Stationery |
| 0007 | Ballpoint pen | 230 | 2010-06-08 | Stationery |
| 0008 | Crayon | 210 | 2010-06-15 | Stationery |
+------+---------------+-------+------------+------------+
3 rows in set (0.00 sec)
上のようにビューの場合、CREATE VIEWでビューを作成してから、それを呼び出すことになる。
これをサブクエリで使う場合、
mysql> SELECT *
-> FROM ( SELECT *
-> FROM Sample
-> WHERE Category='Stationery' ) AS SUBQUERY;
+------+---------------+-------+------------+------------+
| id | Name | VALUE | Date | Category |
+------+---------------+-------+------------+------------+
| 0002 | Pencil | 200 | 2009-11-27 | Stationery |
| 0004 | Eraser | 299 | 2009-12-11 | Stationery |
| 0007 | Ballpoint pen | 230 | 2010-06-08 | Stationery |
| 0008 | Crayon | 210 | 2010-06-15 | Stationery |
+------+---------------+-------+------------+------------+
4 rows in set (0.00 sec)
と、以上のように１つのSELECT文で書ける。
（ちなみに上の例はサブクエリの意味は全くありません。良い子は真似しないでください。）
注意点
サブクエリを使うと、SQL文のネストが深くなり、読みにくくなりがち。
また、ビューと同じく、複数のSELECT文が走るため、パフォーマンスが落ちる可能性があります。
また、一時的なデータになるが、必ずAS...で名前をつける必要があります。
文法
SELECT ...
FROM
<ここからサブクエリ>
( SELECT文 ) AS <サブクエリ名>
文法は通常通りのSELECT文と同じになるが、カッコで囲むこと, 最後にサブクエリ名をつける事を忘れずに。
使用例
スカラサブクエリや相関サブクエリではないただのサブクエリを使うことはあまり経験がないので、
なかなか思いつかない。
とりあえず、カテゴリでグループ化したものに対し、列の選択と別名の付与を行う例を以下に。
サブクエリ名は、Category_Heikinとしています。
mysql> SELECT Hanbai_kingaku_heikin, Category As Bunrui -> FROM ( SELECT AVG(VALUE) As Hanbai_kingaku_heikin , Sum(VALUE), Category -> FROM Sample -> GROUP BY Category) AS Category_Heikin -> ORDER BY Hanbai_kingaku_heikin DESC; +-----------------------+-------------+ | Hanbai_kingaku_heikin | Bunrui | +-----------------------+-------------+ | 2641000.0000 | NULL | | 1097500.0000 | Vehicle | | 10500.0000 | Furniture | | 234.7500 | Stationery | +-----------------------+-------------+
スカラサブクエリとは？
概要
一行一列のデータを返すサブクエリ。
一行一列、とは以下の様なデータのことです。
mysql> SELECT AVG(VALUE)
-> FROM Sample;
+-------------+
| AVG(VALUE) |
+-------------+
| 749893.9000 |
+-------------+
1 row in set (0.00 sec)
AVG(VALUE)列の１つのみ、データも１行のみ。
こういったデータを出力するサブクエリをスカラサブクエリと言い、定数や比較条件に使う。
注意点
必ず一行一列であること。
文法
サブクエリと同じ。違いは出力するデータとなる。
使用例
スカラサブクエリは、様々な使い方がある。
名前と値段に加え、値段の平均を表示する例
mysql> SELECT NAME, VALUE,
-> ( SELECT AVG(VALUE)
-> FROM Sample ) AS VALUE_AVG
-> FROM Sample;
+---------------+---------+-------------+
| NAME | VALUE | VALUE_AVG |
+---------------+---------+-------------+
| Car | 2000000 | 749893.9000 |
| Pencil | 200 | 749893.9000 |
| Cabinet | 12500 | 749893.9000 |
| Eraser | 299 | 749893.9000 |
| Moto | 195000 | 749893.9000 |
| Chair | 8500 | 749893.9000 |
| Ballpoint pen | 230 | 749893.9000 |
| Crayon | 210 | 749893.9000 |
| Garage | 5282000 | 749893.9000 |
| MacBookPro | 0 | 749893.9000 |
+---------------+---------+-------------+
値段の平均値以上のものを抽出する例
mysql> SELECT Name, VALUE
FROM Sample
WHERE VALUE > ( SELECT AVG(VALUE)
FROM Sample );
+--------+---------+
| Name | VALUE |
+--------+---------+
| Car | 2000000 |
| Garage | 5282000 |
+--------+---------+
2 rows in set (0.00 sec)
ちなみにこの例、サブクエリ使わなくてもWHEREでAVGを引っ掛ければできそうだなぁと思いました。
以下のようなSQL文です。
mysql> SELECT Name,VALUE
-> FROM Sample
-> WHERE VALUE > AVG(VALUE);
と思ったら、本書に
集約関数はWHERE句に書くことはできないので、エラーになります。
ハッ！そうか！
実行してみると…
mysql> SELECT Name,VALUE
-> FROM Sample
-> WHERE VALUE > AVG(VALUE);
ERROR 1111 (HY000): Invalid use of group function
というわけでグループから出直してこい！と作者に言われた気分。
相関サブクエリとは？
概要
よくわからん！
とよく言われる相関サブクエリの登場です。
ぶっちゃけ自分もちゃんと理解できているのか怪しい
実際に本書でも初心者にはなかなか理解するのが難しい、と書かれています。
本書では、相関サブクエリについて
相関サブクエリは、小分けにしたグループ内での比較をするときに使う。
と、書かれています。
イメージにすると、こんな感じでしょうか？
ちょっとイメージが湧きにくいので、
各カテゴリでそのカテゴリの平均値より高い製品を表示するという例でやってみます。
まず、結果の形を考えながら、SQL文を書いていきます。
結果は、製品名、カテゴリ名、値を表示するようにして、各値がカテゴリの平均値より高ければいいわけです。
mysql> SELECT Name, Category, VALUE
-> FROM Sample
-> WHERE VALUE > <製品のカテゴリの平均値>
ここで、製品のカテゴリの平均値を取り出す方法を考えなければいけません。
AVG(VALUE)は全体の平均値になりますし、結果はそれぞれのデータごとなのでグループ分けはできません。
とりあえず、各カテゴリの平均値を算出するSQL文を考えます。
mysql> SELECT AVG(VALUE)
-> FROM Sample
-> GROUP BY Category;
+--------------+
| AVG(VALUE) |
+--------------+
| 2641000.0000 |
| 10500.0000 |
| 234.7500 |
| 1097500.0000 |
+--------------+
このSQL文をサブクエリとしてWHEREで使えれば、算出できますよね。
この値と、それぞれの製品の値を比較できればいいわけです。
では、実際にサブクエリとしてやってみると、
mysql> SELECT Name, Category, VALUE
-> FROM Sample
-> WHERE VALUE > ( SELECT AVG(VALUE)
-> FROM Sample
-> GROUP BY Category );
ERROR 1242 (21000): Subquery returns more than 1 row
アルェー (・3・)
となりそうですが、検出できない原因は明確です。
WHEREと比較できるのは１つの値(スカラ値)です。
つまり、スカラサブクエリでないといけないわけですね。
このサブクエリは先程の結果通り、４つの行があるので、
比較できないわけです。
じゃあどうすればいいのか。
各カテゴリの平均から今の製品のカテゴリだけ取ってくればいいわけです。
そのための相関サブクエリです。
画像間違えた
さて、相関サブクエリを使ってみましょう。
mysql> SELECT Name, Category, VALUE
-> FROM Sample AS S1
-> WHERE VALUE > ( SELECT AVG(VALUE)
-> FROM Sample AS S2
-> WHERE S1.Category = S2.Category
-> GROUP BY Category );
+---------+------------+---------+
| Name | Category | VALUE |
+---------+------------+---------+
| Car | Vehicle | 2000000 |
| Cabinet | Furniture | 12500 |
| Eraser | Stationery | 299 |
+---------+------------+---------+
ここのキモは、WHERE S1.Category = S2.Category です！
このSQL文は、Sampleテーブルを２つ使いますが、それぞれ別のテーブルとして扱いたいので、
別名をつける必要があります。
そして、 WHERE S1.Category = S2.Category はS1テーブルの現在のデータのカテゴリでS2テーブルのデータを抽出します。
例えば、Carでデータを作る時、S1.CategoryはVehicleになっています。
なので、サブクエリ内の処理は
mysql> SELECT AVG(VALUE)
-> FROM Sample AS S2
-> WHERE 'Vehicle' = Category;
+--------------+
| AVG(VALUE) |
+--------------+
| 1097500.0000 |
+--------------+
1 row in set (0.00 sec)
ということで、ちゃんとスカラサブクエリになっていますね。
つまり、相関サブクエリとは
サブクエリの外からスカラ値を持ってきて比較に使えるものなわけです。
なので、最初に書いた本書の解説
相関サブクエリは、小分けにしたグループ内での比較をするときに使う。
というのは、ちょっとわかりにくいかもしれませんね。
ちょっと気になってグループを使わない相関サブクエリができるのかやってみました。
mysql> SELECT NAME, VALUE, Category,
-> ( SELECT NAME
-> FROM Sample S2
-> WHERE S1.id = S2.id ) As NAME2
-> FROM Sample S1;
+---------------+---------+------------+---------------+
| NAME | VALUE | Category | NAME2 |
+---------------+---------+------------+---------------+
| Car | 2000000 | Vehicle | Car |
| Pencil | 200 | Stationery | Pencil |
| Cabinet | 12500 | Furniture | Cabinet |
| Eraser | 299 | Stationery | Eraser |
| Moto | 195000 | Vehicle | Moto |
| Chair | 8500 | Furniture | Chair |
| Ballpoint pen | 230 | Stationery | Ballpoint pen |
| Crayon | 210 | Stationery | Crayon |
| Garage | 5282000 | NULL | Garage |
| MacBookPro | 0 | NULL | MacBookPro |
+---------------+---------+------------+---------------+
別にグループは関係ないような気がします。
その他使用方法
WHEREの比較だけではなく、列の定数などにも相関サブクエリは使えます。
以下の例は、各製品の名前及び値、カテゴリ名、各カテゴリのの平均値を表示するものです。
mysql> SELECT Name, VALUE, Category,
-> ( SELECT AVG(VALUE)
-> FROM Sample S2
-> WHERE S1.Category = S2.Category
-> GROUP BY Category ) As Category_AVG
-> FROM Sample S1;
+---------------+---------+------------+--------------+
| Name | VALUE | Category | Category_AVG |
+---------------+---------+------------+--------------+
| Car | 2000000 | Vehicle | 1097500.0000 |
| Pencil | 200 | Stationery | 234.7500 |
| Cabinet | 12500 | Furniture | 10500.0000 |
| Eraser | 299 | Stationery | 234.7500 |
| Moto | 195000 | Vehicle | 1097500.0000 |
| Chair | 8500 | Furniture | 10500.0000 |
| Ballpoint pen | 230 | Stationery | 234.7500 |
| Crayon | 210 | Stationery | 234.7500 |
| Garage | 5282000 | NULL | NULL |
| MacBookPro | 0 | NULL | NULL |
+---------------+---------+------------+--------------+
10 rows in set (0.00 sec)
この場合、先に出るSampleテーブルをS2にしていますが、これは外側にあるものが若い番号という俺ルールです。
ちなみにSQLのコーディングルールなどは全く知りません。調べてみようかな。
注意点
相関名のスコープに注意する
プログラマな方ならスコープときいておそらくピンと来るんじゃないかと思います。
mysql> SELECT NAME, VALUE, Category
-> FROM Sample AS S1
-> WHERE S1.Category = S2.Category
-> AND VALUE > ( SELECT AVG(VALUE)
-> FROM Sample AS S2
-> GROUP BY Category );
この場合、WHERE S1.Category = S2.Category がダウトです。
なぜかというと、別名は、宣言したSELECT文より下の階層(サブクエリ）でしか使えません。
なので、サブクエリを呼び出す上の階層では使えません。
実際にやってみると、カラムが見つからないというエラーが発生します。
mysql> SELECT NAME, VALUE, Category
-> FROM Sample AS S1
-> WHERE S1.Category = S2.Category
-> AND VALUE > ( SELECT AVG(VALUE)
-> FROM Sample AS S2
-> GROUP BY Category );
ERROR 1054 (42S22): Unknown column 'S2.Category' in 'where clause'
筆者の環境はMySQLなので、他のDBMSであればまた別のエラー内容になると思います。
まとめ
サブクエリは正しく使えば強力。
相関サブクエリは深く考えすぎないでイメージを膨らませてみると見えてくる。
