SQLでサブクエリを上手に使う6パターン

Published in

Nyle Engineering Blog

11 min readFeb 17, 2014

はじめまして。Souki.Tです。
SQLを書く上で、使いどころが難しいのがサブクエリです。何でもかんでもJOINして運用するのは格好わるい、サブクエリを使ったら何かカッコいい、結局サブクエリを使いすぎて訳の分からなくなり、作った自分でも手が出せなくなった経験は私だけではないはずです。今回はサブクエリを使う場面をパターンに分けて上手なつき合い方を考えてみたいと思います。

サブクエリとは何かということを説明するのは私には手に余るので誰か説明の上手な人に任せます。どこかにいい解説があったら教えてください。

サブクエリを使わない理由

サブクエリの特徴を一言でいうと「重い」。ともかく重い。使い方を間違えたら劇的に重くなることはもちろんのこと、適切に使ったとしても重いものは重いです。普通にJOINで結合して解決するのであれば、使うべきではありません。

それでもサブクエリを使うパターン

とはいえ、サブクエリを使う場面は結構多いものです。今までSQLを使ってきて、サブクエリに頼ることになった場面を3つの理由と、6つのパターンに分類してみました。1クエリでやらなければ解決策はもっとたくさんあるのですが、なんとか1クエリで解決することを目指します。

理由1. 使う以外に選択肢がない
二段階以上の集計を行う場合
理由2. 使った方が実行速度が速い
サブクエリで集計する場合
サブクエリにorderとlimitを入れる場合
理由3. 使った方が正しいSQLが早く書ける
演算子IN+サブクエリを使う場合
パフォーマンスチューニング済みSQLがある場合
SQLだけで、複雑なことがしたい場合

理由1. 使う以外に選択肢がない

サブクエリを使わない書き方がない場合は仕方ありません。諦めてサブクエリを使いましょう。アプリケーションでの処理を通して、サブクエリを使わない選択肢もありますが、クエリの数が増えて遅くなってしまいます。結局サブクエリを使うことになるのがこれらのパターンです。

二段階以上の集計を行う場合

具体的には下のようなクエリです。学生一覧からクラスの平均在籍数と最小/最大在籍数を算出します。

SELECT AVG(`count`), MIN(`count`), MAX(`count`) FROM (
  SELECT COUNT(*) AS `count` FROM `students` GROUP BY `class`
) AS sub;

平均在籍数だけであれば、

SELECT COUNT(*) / COUNT(DISTINCT `class`) FROM `students`;

で出ますし、最小/最大在籍数もORDER BYとLIMITを使えばそれぞれ出す方法はあります。しかし、同時に出そうとするとサブクエリ以外の選択肢は無くなります。もっと複雑な集計を行う場合サブクエリは避けられません。(別々に出せよって言うツッコミは受け付けておりません)

理由2. 使った方が実行速度が速い

サブクエリは速くありません。それでもJOINしたらもっと遅いという場合のパターンがこのケースです。このパターンは元となるデータ次第で、大きく変わります。絶対に速くなるということはありません。多くの場合テーブルのレコードにノイズが多かったり、対象のなるデータは本来多くない場合に発生することが多いです。基本は動かしてみてから考えます。やる前に遅すぎるクエリの止め方は確認しておきましょう。

サブクエリで集計する場合

1対他の関係のあるテーブルを結合して集計を行う際に使えることが多いです。サブクエリの特性上必ず速くなるとは保証されないところが悲しいですが。

例えば、下のような集計の場合を考えます。

SELECT `student`.*, SUM(`scores`.`score`) AS `sum` 
FROM `student` LEFT JOIN `scores` ON `student`.`id` = `scores`.`student_id`
GROUP BY `student`.`id`;

先に結合をした大きなテンプテーブルを作った上で、集計が行われることになり、パフォーマンス上の問題が出てくることがあります。このような場合、サブクエリを使うことでパフォーマンスを改善できる可能性があります。

SELECT `student`.*, `scores`.`sum` FROM (
  SELECT `student_id`, SUM(`score`) AS `sum` 
  FROM `student` GROUP BY `student_id`
) AS `scores` RIGHT JOIN `student` ON `student`.`id` = `scores`.`student_id`;

あくまでも可能性がある程度の話ですが、テンプテーブルをディスクに書き込む必要が出るような場合、劇的に高速化することがあります。

サブクエリにorderとlimitを入れる場合

上の例をさらに、最優秀者だけを取り出す場合になると

SELECT `student`.*, SUM(`scores`.`score`) AS `sum` 
FROM `student` LEFT JOIN `scores` ON `student`.`id` = `scores`.`student_id`
GROUP BY `student`.`id` ORDER BY `sum` DSEC LIMIT 1;

これも、サブクエリに組み込んでしまうと

SELECT `student`.*, `scores`.`sum` 
FROM (
  SELECT `student_id`, SUM(`score`) AS `sum` 
  FROM `student` GROUP BY `student_id` ORDER BY `sum` DSEC LIMIT 1
) AS `scores` RIGHT JOIN `student` ON `student`.`id` = `scores`.`student_id`;

扱うデータ量がかなり減るので高速化することが出来ます。

上の節と共通して言えることはサブクエリで生成されるテーブルが小さくなることで、特に最終的な出力とレコードの数が一致する場合には、高速化出来ることが多いと思います。曖昧な表現になりますが、実際のところ本当に高速化できるかどうかは動かしてみないと分かりません。

理由3. 使った方が正しいSQLが早く書ける

ともかく早く書きたいというパターンです。とりあえず一回データを出したい、集計結果を見てみたい場合が当てはまります。この使い方のサブクエリはは常用するものではなく、いつも使うなら他の方法で記述しましょう。もしくは、記述できるようにテーブル構造を変えましょう。

演算子IN+サブクエリを使う場合

サブクエリの使い方としてはかなり一般的な使い方です。サブクエリを独立して実行し、中身を確認できるのがこの利用方法のメリットになります。確認できることによってデバッグがしやすくなり、結果早くSQLが書き終わることが出来るのがこのパターンです。

逆に言うと時間的余裕がある場合は、このサブクエリを使わずに、JOINを使ったり、EXISTS(<サブクエリ>)を使った方が速くなります。もちろんパフォーマンスチューニングをしてインデックスを過不足なく張ってある場合に限定されます。特にEXISTS(<サブクエリ>)については、インデックスなどの調整をしないと、IN演算子を使うよりも遅くなります。この違いはEXISTSとIN演算子で使われるサブクエリの種類が異なるためです。EXISTSは相関サブクエリを利用して、IN演算子は非相関サブクエリを利用します。この差は大きく、EXISTSの方がパフォーマンスチューニングの影響を強く受けます。

ともかく手早くSQLを書きたいだけならIN演算子サブクエリを使うと早く書けますし、速く動きます。

パフォーマンスチューニング済みSQLがある場合

一回だけでいいから、集計が行いたいという場合に使えるパターンになります。
考え方はごくごく単純でサブクエリの中身をパフォーマンスチェックが終わっているクエリをそのまま入れてしまいます。

SELECT * FROM (<パフォーマンスチューニング済みクエリ>) AS `sub` JOIN …..

こうすると、複雑なクエリであってもかなり現実的なパフォーマンスが得られることが多いです。何も気にせず、とりあえず使うことが出来るのでかなり考えなくても使えるので楽です。これが使えるととっさに出して欲しいと言われたデータを短時間で取り出すことが出来るようになります。

SQLだけで、複雑なことがしたい場合

ここまで書いてきた例も十分に複雑なことはしていますが、もっと複雑なアクロバティックなことをSQLにさせることは出来ます。
アプリケーションでやった方がいい処理も一度しか使わないのであれば、SQLだけで全部処理するにはサブクエリは避けられません。サブクエリを駆使すれば時間としては早くできるはずです。やる時は複雑になりすぎないように注意しよう。

サブクエリが使えないパターン

おまけとして、稀にしか見ることのない特殊なケースですが、自己集計を使ったUPDATEを行う場合はサブクエリが使えません。稀とはいえ、出るとかなり頭を悩ますことになるパターンです。解決法はサブクエリ内にサブクエリを入れるか、サブクエリをJOINします。

さて、実例ですが、点数一覧に偏差値を計算するクエリは下のようになります。

SELECT `score`, (
  SELECT (main.`score` - AVG(sub.`score`)) / STDDEV(sub.`score`) * 10 + 50 
  FROM `scores` as `sub`
) AS `deviation ` FROM `scores` as main;

(時間がかかることは別として)計算できたので、この結果をUPDATEで保存しようとすると事故が起こります。

UPDATE `scores` as main SET `deviation ` = (
  SELECT (main.`score` - AVG(sub.`score`)) / STDDEV(sub.`score`) * 10 + 50
  FROM `scores` as `sub`
);

MySQLで実行する下のようなエラーが出ます。

#1093 - You can't specify target table 'main' for update in FROM clause

これを見ると私は逃げ腰になります。複雑なクエリの場合、非常に面倒なことになります。

単純な対策をするならFROM句をサブクエリにすると動きます。

UPDATE `scores` as main SET `deviation ` = (
  SELECT (main.`score` - AVG(sub.`score`)) / STDDEV(sub.`score`) * 10 + 50 
  FROM (SELECT * FROM `scores`) as `sub`
);

動くには動くけど、あまりにも面倒なクエリになったので書き換えをします。

UPDATE `scores` as main JOIN (
  SELECT AVG(`score`) AS `avg`, STDDEV(`score`) AS `sdev` 
  FROM `scores`
) AS `sub` SET `deviation` = (main.`score` - `avg`) / `sdev` * 10 + 50;

サブクエリをJOINします。ONは使わない形になります。毎回こういった書き換えが出来るわけではないので、多くの場合サブクエリ内にサブクエリが発生します。結局サブクエリを避けることができません。

結局サブクエリとどうつき合うか

サブクエリが実行されるたびにテーブルが作られるとイメージしましょう。そのテーブルにはインデックスは張られていない。そのような状況で、まともな速度で動くデータ量であることが予想できれば使いどころとして間違っていないと言えます。

クエリを実行したら、テンプテーブルの作成に時間がかかっていないか、ソートに時間がかかっていないかに注目しましょう。実際に出力しているテーブルより大きなテーブルを内部的に必要にしないように調整するとサブクエリは上手に使えるようになるはずです。

使いどころさえ間違えなければサブクエリは非常に強力です。ただし、あまりにも使う機会が多い場合はそもそもデータベース設計から考え直した方がいいでしょう。それでも、今すぐ欲しいデータがある場合使ってしまいます。いつものクエリには使わないようにしよう。