今度こそサブクエリを理解する
どうも。入社しプログラミングに触れ始めてから、いつの間にかもうすぐ一年になりつつあることに焦燥感を隠せない知苑です。
この機会にこれまで学習してきたことで理解が曖昧なままになっていることを無くしていきたい(願望)と思い、チャンスがあれば改めて自分なりに学習し直した内容をここで記事にしていこうと思った次第です。
今回はSQLのサブクエリについて触れていきたいと思います。
研修時代にも何度か目にする機会はあったのですが、複雑なSQL文への苦手意識から、詳しい用法に関する知識などがまだフワッとしたままだったので… じゃなんで研修中に潰しておかなかった
システムはMySQLを使用します。
サブクエリとは
定義についての再確認。
サブクエリ 【 subquery 】 副問い合わせ
サブクエリとは、データベースなどの問い合わせ(クエリ)文の内部に含まれる、別の問い合わせ文のこと。
IT用語辞典 e-Words
ざっくり言うとSQL文の中にあるSQL文。入れ子になっているということですね。
普通にやると複数書かなきゃいけないSQLを一文でまとめられたりすることに使えます。
大体色んなステートメントで使えるようです(サブクエリ自体は基本的にSELECT句)。
SQL文内で括弧で囲って書くという使い方をします。
構文例
booksテーブル
カラムはID、本のタイトル、著者、値段といった感じで
mysql> select * from `books`;
+----+--------+--------+-------+
| id | name | author | price |
+----+--------+--------+-------+
| 1 | Book A | John | 2000 |
| 2 | Book B | John | 3000 |
| 3 | Book C | Taro | 7000 |
+----+--------+--------+-------+
雑に作ったものではありますが、上記のテーブルを使って
SELECT・FROM・WHERE
のそれぞれの句の中でサブクエリを用いたサンプルSQL文を書いていきたいと思います。
SELECT内のサブクエリ
SQL文とその実行結果
SELECT
*,
(SELECT AVG(`price`) FROM `books`) as avg_price
FROM
`books`
;
+----+--------+--------+-------+-----------+
| id | name | author | price | avg_price |
+----+--------+--------+-------+-----------+
| 1 | Book A | John | 2000 | 4000.0000 |
| 2 | Book B | John | 3000 | 4000.0000 |
| 3 | Book C | Taro | 7000 | 4000.0000 |
+----+--------+--------+-------+-----------+
普通のSELECT *で得た各レコードに、サブクエリを使ってpriceの平均値をくっつけた例です。
単一のSELECT文では、AVG()やMAX()といった集約関数はグループ化に伴った結果を取得するため、全体を取得するステートメントとの併用はできませんが、サブクエリを使用することで同時取得が可能になります。
FROM内のサブクエリ
SELECT
MAX(`avg_price`) as max_avg_price
FROM
(SELECT AVG(`price`) as avg_price FROM `books` GROUP BY `author`) as avg_price_by_author
;
+---------------+
| max_avg_price |
+---------------+
| 7000.0000 |
+---------------+
やっていることとしては、著者ごとの平均値段を取り、その中で最も高い値を取得しています。
FROM句のサブクエリは個人的に最初はパッと見て「…?!」な感じだったのですが、段階を分けて処理を追えば多少は理解できるようになったかなと思います。
この例の場合だと、
まずサブクエリ「SELECT AVG(price) as avg_price FROM books GROUP BY author」で取得した結果↓
+-----------+
| avg_price |
+-----------+
| 2500.0000 |
| 7000.0000 |
+-----------+
この結果をテーブルとして、このテーブルに対してメインのクエリ(親クエリ)「SELECT MAX(avg_price) as max_avg_price…」を実行しているといった具合です。
WHERE内のサブクエリ
SELECT
*
FROM
`books`
WHERE
`price` > (SELECT AVG(`price`) FROM `books`)
;
+----+--------+--------+-------+
| id | name | author | price |
+----+--------+--------+-------+
| 3 | Book C | Taro | 7000 |
+----+--------+--------+-------+
値段が全体の平均より高いレコードを取得しています。
HAVING句でも同様の使用方法が使えます。
ちなみに、この例のサブクエリはpriceの全体平均値である「4000.0000」という単一の結果を返しますが、このように「1行1列の戻り値を返す」サブクエリのことをスカラサブクエリと言います。
相関サブクエリ
サブクエリについて調べていたら出てきた初めて聞いた用語。
サブクエリは大きく二種類に分けられるようで、
- 相関サブクエリ: 親のSQL文で定義されたオブジェクトを参照(外部参照)しているサブクエリ
- 非相関サブクエリ: 外部参照していないサブクエリ
があります。僕なりの見分け方としては、
- サブクエリだけ抜き出すと意味の通らないSQL文になる →
相関サブクエリ - サブクエリ単体でも実行が可能 →
非相関サブクエリ
相関サブクエリは外部参照しているため、それだけを実行しようとすると存在しないオブジェクトを呼んでいることとなり、エラーになってしまうからです。(あくまで僕が今思いついたので、この見分け方が絶対という確証はないです←)
上で書いたサンプルは今のところ全て非相関サブクエリとなるっぽいですね。
であれば相関サブクエリを含んだSQLをbooksテーブルを使って書いてみましょう。
分かりやすい実行結果を得るためにいくつかレコードを足しておきます。
mysql> select * from `books`;
+----+--------+--------+-------+
| id | name | author | price |
+----+--------+--------+-------+
| 1 | Book A | John | 2000 |
| 2 | Book B | John | 3000 |
| 3 | Book C | Taro | 7000 |
| 4 | Book D | John | 3500 |
| 5 | Book E | Taro | 1000 |
+----+--------+--------+-------+
さて、上記のWHERE句の例では、「値段が全体の平均より高い」レコードを取得していましたが、これを今度は「値段がその著者の本の中での平均より高い」レコードを取得するSQLを相関サブクエリを使って書きます。
SELECT
*
FROM
`books` as b1
WHERE
`price` > ( SELECT
AVG(`price`)
FROM
`books` as b2
WHERE
b1.`author` = b2.`author`
GROUP BY
`author`
)
;
+----+--------+--------+-------+
| id | name | author | price |
+----+--------+--------+-------+
| 2 | Book B | John | 3000 |
| 3 | Book C | Taro | 7000 |
| 4 | Book D | John | 3500 |
+----+--------+--------+-------+
著者がJohnである本の平均値段が2833.3333、著者がTaroである本の平均値段が4000.0000なので期待通りの結果が取れていますね。
肝としてはサブクエリのWHERE句で、親クエリで呼び出したbooksテーブル(b1)とサブクエリで呼び出したbooksテーブル(b2)のauthorの値を照合している所でしょうか。
まず親クエリでの各レコードに対してそのauthorの値を参照し、その値と等しいauthor値を持つレコード群の平均priceをサブクエリで取得する。あとはサブクエリで得た平均値の結果と親クエリの各レコードのpriceと比較するという形です。
他の言い方をすると、親クエリのレコード一つ一つに対して都度サブクエリを実行しているイメージになります。プログラミング言語におけるループ処理で関数を実行することと感覚が近いような気がします。
つまり処理内容やテーブルの容量によっては膨大な実行時間を要する場合もあるので使い所は気をつけた方がいいかも知れないです。
まとめ
自身の苦手意識の払拭を目的とした本記事ですが、普段の業務ではフレームワークやワークベンチを使用している関係で生のSQL文を書く機会があまりなかったので、その点ではいい復習にもなったと思います。
サブクエリに関しては、今回は簡単な構造のものしか扱わなかったので、完璧に理解…とまでは行かないかもです。例では一つのテーブルしか扱いませんでしたが、実際のサブクエリは別のテーブルにまたがった形のものも多いですし、他にも様々な使い方が当然あります。でも基本的な部分は一緒なので今回の内容を抑えられれば上出来ではないかと。少なくとも、サブクエリが目に入った時点でファイルを即閉じすることはなくなったので、自分的には良しとします←
ただ、サブクエリは一応何段階にも入れ子にすることが可能なので、もし(まだ見たことはないですが)ネストの深いSQL文と遭遇した場合のショック指数に関しては未知数です(
補足として、使いこなせれば便利そうなサブクエリですが、調べた限りでは、複数のテーブルを扱う場合などはJOINを使用したステートメントに置き換えられるパターンも多くあるようで、どちらを使うのがベターかはケースバイケースだそう。
データベースのパフォーマンスやそのクエリで実現したいことを表現するのに適切かどうかといった要素と相談して決めるのが理想でしょう。
参考
