仕事でSQLを書いていると、時々必要な場面が出てくるサブクエリ。ただSQLを勉強している中で、改めてふとサブクエリってそもそもなんだっけというくらい理解が浅かったので、まとめて見ようと思います。サブクエリにもいくつか種類があり、その点の解説も行っていきます。
参考文献: 「SQL 第2版 ゼロからはじめるデータベース操作」
そもそもサブクエリとは...?
サブクエリとはクエリの中に含まれているクエリのことを指します。ん?となった方もいらっしゃるかと思いますが、順を追って説明していきます。そもそもクエリというのはDBに対する命令文のことを指します。この命令文とは皆さんもよく使用される（SELECT * FROM テーブル ～）等が当てはまります。通常ですと、このクエリにWHERE句等の肉付けをしていってデータ検証をしていることと思います。ですが、通常のクエリだけでは取得しづらいケースがあるのも事実で、そんな時に使用したいのが表題のサブクエリです。サブクエリでは上記で説明したクエリの中にもう一段ネストした形でクエリを書きます。論より証拠ということで実際にサブクエリを使用したSQL文を書いてみます。
以下のデータを持った商品テーブルがあるとします。
それではサブクエリを使用せずに、純粋なクエリの形で商品テーブルに記載の「product_category」ごとのレコード数を取得してみようと思います。
SELECT product_category, COUNT(*) FROM product GROUP BY product_category;
実行結果は以下のようになると思います。特に難しいことはありませんね。これをサブクエリで書いてみます。
SELECT product_category, cnt_product FROM(SELECT product_category, COUNT(*) as cnt_product
FROM product GROUP BY product_category) AS product_sum;
取得したカラム名は多少変化していますが、実行結果は同じです。ではこのサブクエリでは何をしているのかを解説していきたいと思います。見ての通り、SELECT文が入れ子のような形になっていますが、サブクエリの場合内側のクエリから順に実行されていきます。今回の場合、「product_category」ごとのレコード数を取得し、その実行結果を一つのテーブル(※asを使用して、product_sum)と見なしながら、メインのクエリでそのテーブルからデータを取得しているような運びになっています。このような入れ子構造がサブクエリの特徴で、階層を増やしていくことも可能です。
スカラ・サブクエリとは?
これまでは純粋なサブクエリについて学んできましたが、サブクエリにもいくつか種類があり、その1つが表題のスカラ・サブクエリと呼ばれるものです。通常のサブクエリでは基本的には複数行を結果として返しますが、スカラ・サブクエリでは必ず単一の値を返します。それではこのスカラ・サブクエリがどういったケースの場合有効なのか実際に見ていきます。
ケースとして、上記の商品テーブルから「販売価格が、全体の平均販売価格より高い商品だけを取得」といった具合でデータを取得したいとします。この場合サブクエリの知識がないと以下のSQLを想像してしまうかもしれません。
SELECT product_id, product_mei, hanbai_kakaku FROM product WHERE hanbai_kakaku > AVG(hanbai_kakaku);
一見すると意味的には合っているような気もしてしまいますが、AVG(平均を求める関数)のような集約関数をWhere旬に書くことはできない為、エラーとなってしまいます。ここで活躍するのがスカラ・サブクエリです。
SELECT product_id, product_mei, hanbai_kakaku FROM product WHERE hanbai_kakaku >
(SELECT AVG(hanbai_kakaku) FROM product);
実行結果は以下の通りです。
ここでは先程取得したかった平均販売単価をスカラ・サブクエリにて取得しています。スカラ・サブクエリでは単一の値を返しますので、その結果をWhere旬に入れてやることで取得したかった結果を取得することができるようになりました。これがスカラ・サブクエリの1つの使い方です。
相関サブクエリとは?
相関サブクエリとは、小分けにしたグループ内での比較をするときに使用されます。これまでは「販売価格が、全体の平均販売価格より高い商品」というケースを見てきましたが、「販売価格が、各商品分類ごとの平均販売価格より高い商品」というケースになった場合、この相関サブクエリが活躍します。それではまず上記に習ったスカラ・サブクエリを用いて今回のケースを書いてみます。
SELECT product_id, product_mei, hanbai_kakaku FROM product WHERE hanbai_kakaku >
(SELECT AVG(hanbai_kakaku) FROM product GROUP BY product_category);
意味合い的には良さそうですが、この書き方ではエラーになってしまいます。上記のサブクエリではGROUP BY実行後の結果として、3行を返してしまい、スカラ・サブクエリとならず、WHERE句でサブクエリを使用する場合は、結果は1行である必要がある為です。それでは上記のケースを相関サブクエリで書いてみましょう。
SELECT product_category, product_mei, hanbai_kakaku FROM product AS P1
WHERE hanbai_kakaku > (SELECT AVG(hanbai_kakaku) FROM product AS P2
WHERE P1.product_category = P2.product_category GROUP BY product_category);
実行結果は以下の通りです。
上記のSQLによって、目的の結果を取得することができました。ここで注目したいのがWHERE句の条件ですが、簡潔に言うと、「各商品の販売価格と平均価格の比較を、同じ商品分類の中で行う」という意味になります。相関サブクエリではGROUP BYと同じく「商品分類」という集合ごとに切り分け、各商品分類ごとに平均販売価格が計算され、その結果が商品テーブルの各レコードと比較される為、レコードに対して実質的に1行しか返していないと見なされます。これが相関サブクエリの動きになります。
終わりに
そもそもサブクエリって何?というところから様々なサブクエリの使い方について学んでこれたと思います。サブクエリにも種類があり、用途に応じてサブクエリを使い分けていくことによって、データ分析などの調査が非常にしやすくなると思います。SQLはまだまだ奥が深いと思いますので、引き続き勉強していきます。