[SQL]今更ながらSQLのサブクエリに向き合ってみる

皆さんはSQLのサブクエリ書いてますか？

最近は分析目的でSQLを書く機会が増えていますが、意外と生のSQLを書く機会がなかったので意外と初歩的なところでつまづいたりしました。特に、SQLのサブクエリについての理解がかなり曖昧だったので、この機会にしっかりと向き合ってみようと思い基礎から学び直してみました！
## 本稿の対応
* ビュー
* サブクエリ
* 相関サブクエリ

## 環境設定
docker上で真っさらなmysqlを立ち上げ、下記のテーブルを作成しました。
検証環境は[github](https://github.com/halkt/docker_mysql)にも載せてます。

```sql
-- テーブル生成
CREATE TABLE foods
(
  id				BIGINT			NOT NULL,
  name				VARCHAR(100)	NOT NULL,
  type				VARCHAR(32)		NOT NULL,
  sales_price		INTEGER					,
  purchase_price	INTEGER					,
  created_at        DATETIME                ,
  updated_at        DATETIME                ,
  PRIMARY KEY (id)
);
```

一旦サンプルとして下記のデータを流し込んでいます。

```sql
-- データ生成
INSERT INTO `foods` (`id`, `name`, `type`, `sales_price`, `purchase_price`, `created_at`, `updated_at`)
VALUES
	(1, 'うどん', '麺類', 400, 50, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(2, 'そば', '麺類', 500, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(3, 'ラーメン', '麺類', 800, 300, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(4, 'ごはん', 'ご飯類', 200, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(5, 'おにぎり', 'ご飯類', 250, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(6, 'チャーハン', 'ご飯類', 400, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(7, '餃子', 'おかず', 300, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(8, '卵焼き', 'おかず', 400, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(9, 'お茶', '飲み物', 200, 60, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(10, 'ジンジャエール', '飲み物', 250, 80, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
	(11, 'ビール', '飲み物', 400, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52');

```

## ビュー
ビューを一言で言うと「仮想テーブル」です。テーブルが実データを保持しているのに対して、ビューが保持しているのは**SELECT文そのもの**を保持しています。

利点としては、データ領域を使う必要がないので節約ができることや、頻繁に使うSELECT文を使い回すことができる点です。例えば、毎回複雑な検索条件で絞った結果を、複数の場所で取り扱うような場合はビューの出番です。汎用的に使うSELECT文をビューにしておくことで、メリットを享受することができます。

### ビューの定義方法
今回は、foodsテーブルの種別ごとに個数を表示するビューを作成していきます。

```sql
CREATE VIEW sum_foods (type, count_foods)
AS
SELECT type, COUNT(*)
FROM foods
GROUP BY type;
```

DBを実際に見てみるとViewというテーブルが作られています。

```
mysql> show full tables;
+-----------------------------+------------+
| Tables_in_development_mysql | Table_type |
+-----------------------------+------------+
| foods                       | BASE TABLE |
| sum_foods                   | VIEW       |
+-----------------------------+------------+
2 rows in set (0.00 sec)
```

ただ、データを格納しているわけではなく、foodsテーブルを参照するSELECT文なのでfoodsテーブルの実データが追加されたり更新されると結果も自動的に変更されます。

```
mysql> select *
    -> from sum_foods;
+-----------+-------------+
| type      | count_foods |
+-----------+-------------+
| おかず    |           2 |
| ご飯類    |           3 |
| 飲み物    |           3 |
| 麺類      |           3 |
+-----------+-------------+
4 rows in set (0.00 sec)
```

## サブクエリ
本題のサブクエリです。

これを一言でいうと「使い捨てのビュー」です。ビューのように仮想テーブルをDBのなかに作るのではなく、一時的に使うために使うビューのことです。先ほどビューを定義するときのAS以降の文章をそのままFROM句に使うと結果を得ることができます。(FROM句内で使うサブクエリを**インラインビュー**と呼んだりもします）

```sql
SELECT type, count_product
FROM (SELECT type, COUNT(*)
      FROM foods
      GROUP BY type) AS foods_sum;
```

実行結果は下記のようになります。

```
mysql> SELECT type, count_foods
    -> FROM (SELECT type, COUNT(*) as count_foods
    ->       FROM foods
    ->       GROUP BY type) AS foods_sum;
+-----------+-------------+
| type      | count_foods |
+-----------+-------------+
| おかず    |           2 |
| ご飯類    |           3 |
| 飲み物    |           3 |
| 麺類      |           3 |
+-----------+-------------+
4 rows in set (0.00 sec)
```

### スカラサブクエリ
スカラサブクエリは1行しか返さないサブクエリのことです。これを利用して比較演算子の入力値として利用する際に活用することがよくあります。以下は金額の平均値を取得して平均より高い食べ物のみを取得しています。

```sql
SELECT *
FROM foods
WHERE sales_price > (SELECT AVG(foods.sales_price)
                     FROM foods);
```
スカラサブクエリのルールとして、**単一の結果を返さないサブクエリを書いてしまうとエラーとなる**ので注意が必要です。

## 相関サブクエリ
上記のように平均より金額よりも高い食べ物を抽出することはサブクエリでできました。今度はさらに**「種別ごとに平均金額より高い食べ物を抽出する」**として見たときに使えるのが相関サブクエリです。まずはSQLを書いてみます。

```sql
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (SELECT AVG(sales_price)
                     FROM foods as T2
                     WHERE T1.type = T2.type
                     GROUP BY type);
```

結果はこうなります。

```
mysql> SELECT type, name, sales_price
    -> FROM foods as T1
    -> WHERE sales_price > (SELECT AVG(sales_price)
    ->                      FROM foods as T2
    ->                      WHERE T1.type = T2.type
    ->                      GROUP BY type);
+-----------+-----------------+-------------+
| type      | name            | sales_price |
+-----------+-----------------+-------------+
| 麺類      | ラーメン        |         800 |
| ご飯類    | チャーハン       |         400 |
| おかず    | 卵焼き          |         400 |
| 飲み物    | ビール          |         400 |
+-----------+-----------------+-------------+
4 rows in set (0.00 sec)
```
なんとなく結果が取れていそうですね！
ただ、一見わかりづらいので少し分解して解説していきます。
まず、WHERE文なしのサブクエリをみていきます。


```
mysql> SELECT AVG(sales_price)
    -> FROM foods
    -> GROUP BY type;
+------------------+
| AVG(sales_price) |
+------------------+
|         350.0000 |
|         283.3333 |
|         283.3333 |
|         566.6667 |
+------------------+
4 rows in set (0.00 sec)
```
ふむ、、、種別ごとの平均金額が取れてそうです。
ただこのまま実行してしまうと比較演算子の入力値に複数の値を返すサブクエリとなってしまいます。

```sql
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (350.0000, 283.3333, 283.3333, 566.6667);
-- これではエラーになってしまう。。。
```
これを解決するため、WHERE文を追加します。
このWHERE文によって、**「金額と平均金額の比較を、同じ種別ごとに行う」**ようにしています。また、テーブルに別名をつけているのはサブクエリ内部のfoodsテーブルと外側のfoodsテーブルを区別するために付与しています。

```sql
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (SELECT AVG(sales_price)
                     FROM foods as T2
                     WHERE T1.type = T2.type -- <--これを追加する
                     GROUP BY type);
```

一見内部動作が見えづらいですが、実際は商品種別ごとに分割し、それぞれの平均値と比較しているだけです。

また、このWHERE文をサブクエリの外側に書くとエラーになってしまいます。それは、サブクエリの外側からサブクエリ内部のテーブルの別名を参照できないからです。

```sql
SELECT type, name, sales_price
FROM foods as T1
WHERE T1.type = T2.type --T2が参照できずエラーになる
AND sales_price > (SELECT AVG(sales_price)
                     FROM foods as T2
                     GROUP BY type);
```

## サブクエリはパフォーマンスが悪いのか？
結論、確かにサブクエリを使うとパフォーマンスは良いとはいえないようです。

ビューのところでも説明しましたが、サブクエリはいわば一時的にビューのような仮想テーブルを生成することになります。さらにいうと、このビューにはインデックスも貼られていないため、**パフォーマンス自体は通常のクエリと比べると悪い**といえます。

そのため、なんでもかんでもサブクエリにするというよりも、集計した結果を条件にしたいなど、サブクエリでなければ実現できない場合時の手段として捉えた上で、使うべきかどうかを考えるようにすると良さそうです。

## まとめ
かなり初歩的な内容になりましたが、曖昧になっていた部分が個人的に解消できました。同じくサブクエリをなんとなく使っていた人がいたら、少しでも力になれば幸いです。

最近はパフォーマンス改善を行う機会も多いので、サブクエリのことを意識しつつ知見が溜まったらまた何か書きたいと思います！それではまた！

## 参考文献
* [プログラミング学習シリーズ SQL ゼロからはじめるデータベース操作（ミック）｜翔泳社の本](https://www.shoeisha.co.jp/book/detail/9784798118819)
* [SQLでサブクエリを上手に使う6パターン \| by Nyle \| Nyle Engineering Blog \| Medium](https://medium.com/nyle-engineering-blog/sql%E3%81%A7%E3%82%B5%E3%83%96%E3%82%AF%E3%82%A8%E3%83%AA%E3%82%92%E4%B8%8A%E6%89%8B%E3%81%AB%E4%BD%BF%E3%81%866%E3%83%91%E3%82%BF%E3%83%BC%E3%83%B3-bae5ae35b962)