サブクエリ ( 副問合せ )

　サブクエリとは、以下のように SELECT、INSERT、UPDATE、DELETE といったクエリの中で SELECT といったクエリを実行するものです。

サブクエリ

> SELECT * FROM sales_slip
 	WHERE goods_id = ( SELECT id FROM goods WHERE name = 'candy');

　サブクエリは必ずカッコ(と)で囲み先に実行させます。ここでは、サブクエリを含むクエリをメインクエリと呼ぶことにします。

　サブクエリは、SELECT によってデータを抽出する際に保存されたデータを検索条件にしたいときや取得したいデータに平均値や最大値を含めたいとき、FROM の仮テーブルとして、フィルター条件などと利用できます。

　サブクエリは SELECT 以外にも INSERT、UPDATE、DELETE も使用できます。

EXISTS 演算子

サブクエリについて

　サブクエリは副問合せとも呼ばれ、SELECT や INSERT、UPDATE、DELETE クエリの中で SELECT といったクエリを実行するものです。

　以下の例では、 SELECT サブクエリを検索条件にしています。サブクエリを使用することで保存されたデータをメインクエリの検索条件とすることができます。

サブクエリ

> SELECT * FROM sales_slip
 	WHERE goods_id = ( SELECT id FROM goods WHERE name = 'candy');

　サブクエリを先に実行するために必ずカッコ(と)で囲むようにします。

　上記の例での実行は以下のように進みます。

　まずサブクエリが実行されます。

SELECT id FROM goods WHERE name = 'candy';
+----+
| id |
+----+
|  1 |
+----+

　上記の結果から以下のようなクエリが実行されます。

SELECT * FROM sales_slip
	WHERE goods_id = 1;
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  1 | 2023-10-29 07:17:00 |        1 |          3 |    100 |       1 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
+----+---------------------+----------+------------+--------+---------+

　サブクエリによって作成されたテーブルデータはメインクエリの実行の終了とともにクリアされます。

　メインクエリが INSERT や UPDATE、DELETE といったクエリである場合もあります。

　サブクエリは取得したいデータに平均値や最大値を含めたいときにも使用することができます。

サブクエリによる平均値

> SELECT *,( SELECT AVG(amount) FROM sales_slip ) as Avg FROM sales_slip;
+----+---------------------+----------+------------+--------+---------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id | Avg     |
+----+---------------------+----------+------------+--------+---------+---------+
|  1 | 2023-10-29 07:17:00 |        1 |          3 |    100 |       1 | 54.0000 |
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 | 54.0000 |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |       1 | 54.0000 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 | 54.0000 |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |       3 | 54.0000 |
+----+---------------------+----------+------------+--------+---------+---------+

　上記の例のように同一のテーブルである場合には AVG(amount) のみでも問題はありません。

　検索条件として平均以上の行を抽出する場合にも使用できます。

サブクエリを検索条件に

> SELECT * FROM sales_slip WHERE amount >= ( SELECT AVG(amount) FROM sales_slip );
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  1 | 2023-10-29 07:17:00 |        1 |          3 |    100 |       1 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
+----+---------------------+----------+------------+--------+---------+

　このようにサブクエリとはメインクエリの実行の前にクエリを実行します。

サブクエリが返す値

　サブクエリの返り値には以下のような場合があることが考えられます。

列が１つで行が１つ
列が１つで複数の行
複数の列の複数の行

　サブクエリの結果を比較演算子（=、<>、<=、>=、<、>）のみで比較できるのは列が１つで行が１つを返す場合のみです。
　サブクエリが列が１つで複数の行や複数の列の複数の行を返す場合に比較演算子のみではエラーになります。

サブクエリの返り値が複数

> SELECT * FROM sales_slip
 	WHERE goods_id = ( SELECT id FROM goods );
ERROR 1242 (21000): Subquery returns more than 1 row

　比較演算子のみではエラーになりますが、IN 演算子や ANY 演算子、 ALL 演算子を使用することでサブクエリが複数の値を返す場合でもエラーなく結果を得ることができます。詳しくは以下のサブクエリの利用方法（非相関サブクエリ）で解説しています。

非相関サブクエリと相関サブクエリ

　サブクエリの利用方法には、サブクエリとメインクエリの関係によって以下のような場合があります。

サブクエリはメインクエリと同じテーブルのカラムを参照しない（非相関サブクエリ）
サブクエリがメインクエリと同じテーブルのカラムが参照されている（相関サブクエリ）

　非相関サブクエリも相関サブクエリもサブクエリの利用方法なのですが、サブクエリとメインクエリが同じテーブルのカラムを参照しているという点があるか／ないかの違いだけです。

　例えば、相関サブクエリは以下のようにサブクエリ内でメインクエリと同じテーブルのカラムを参照している場合です。

相関サブクエリ

> SELECT *, ( SELECT unit_price FROM goods WHERE id = s.goods_id ) AS goods_price
	FROM sales_slip AS s
	WHERE goods_id <> ANY ( SELECT id FROM goods AS g WHERE s.unit_price = g.unit_price );
+----+---------------------+----------+------------+--------+---------+-------------+
| id | date_time           | goods_id | unit_price | amount | user_id | goods_price |
+----+---------------------+----------+------------+--------+---------+-------------+
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |           5 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |           3 |
+----+---------------------+----------+------------+--------+---------+-------------+

　非相関サブクエリはサブクエリ内でメインクエリと同じテーブルのカラムを参照しているという点を省けばサブクエリの利用方法として解説できてしまいます。

　非相関サブクエリは以下のサブクエリの利用方法（非相関サブクエリ）を参照してください。相関サブクエリについては以下の相関サブクエリを参照してください。

サブクエリと比較演算（非相関サブクエリ）

　以下はサブクエリとしての解説の続きとなります。
　非相関サブクエリはサブクエリ内でメインクエリと同じテーブルのカラムを参照しているという点を省けばサブクエリの利用方法として解説できますので重複を避けるためにここではサブクエリの利用方法としています。

サブクエリにおける注意点

　サブクエリの結果を比較演算子（=、<>、<=、>=、<、>）のみで比較できるのは、サブクエリが列が１つで行が１つを返す場合のみです。
　サブクエリが列が１つで複数の行や複数の列の複数の行を返す場合に比較演算子のみではエラーになります。

　エラーが帰らないようにするには IN 演算子を使用するか ANY、ALL 演算子を使用します。以下はこの点について解説します。

サブクエリの返り値が１つ①

> SELECT * FROM sales_slip
 	WHERE goods_id = ( SELECT id FROM goods WHERE name = 'candy');
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  1 | 2023-10-29 07:17:00 |        1 |          3 |    100 |       1 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
+----+---------------------+----------+------------+--------+---------+

　サブクエリが１つしかデータを返さない場合は以下の場合でもエラーになりません。

サブクエリの返り値が１つ②

> SELECT * FROM sales_slip
	WHERE goods_id <> ( SELECT id FROM goods WHERE name = 'candy' );
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |       1 |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |       3 |
+----+---------------------+----------+------------+--------+---------+

　複数の行を返すようなサブクエリはSubquery returns more than 1 rowといった1つ以上の行は比較できないというエラーになります。

サブクエリの返り値が複数

> SELECT * FROM sales_slip
 	WHERE goods_id = ( SELECT id FROM goods );
ERROR 1242 (21000): Subquery returns more than 1 row

　このようにサブクエリが列が１つで複数の行を返すような場合でもメインクエリがエラーなく実行する方法があります。

列が１つで複数行を返すサブクエリ

IN 演算子

　上記の例のメインクエリの比較演算子を IN 演算子に変更するとエラーが起きません。

サブクエリの返り値が複数行で IN 演算子

> SELECT * FROM sales_slip
	WHERE goods_id IN ( SELECT id FROM goods );
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  1 | 2023-10-29 07:17:00 |        1 |          3 |    100 |       1 |
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |       1 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |       3 |
+----+---------------------+----------+------------+--------+---------+

　NOT IN 演算子はサブクエリの結果以外に該当するデータを抽出できます。

NOT IN 演算子

> SELECT * FROM sales_slip
 	WHERE goods_id NOT IN ( SELECT id FROM goods WHERE name = 'candy');
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |       1 |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |       3 |
+----+---------------------+----------+------------+--------+---------+

　IN 演算子についてはWHERE 句ページの範囲条件、IN 演算子と NOT IN 演算子を参照してください。

ANY 演算子

　似た機能をもつ演算子に ANY 演算子があります。
　これらの演算子には比較演算子（=、<>、<=、>=、<、>）と合わせて使用します。比較演算子に ANY 演算子を付けることで結果が望み通りかを検証することができます。

　ANY では以下のようになります。

> SELECT * FROM sales_slip
	WHERE goods_id = ANY ( SELECT id FROM goods );
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  1 | 2023-10-29 07:17:00 |        1 |          3 |    100 |       1 |
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |       1 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |       3 |
+----+---------------------+----------+------------+--------+---------+

　IN 演算子での結果と同じで、想定された結果であると思います。

ALL 演算子

　比較演算子（=、<>、<=、>=、<、>）と合わせて使用する演算子に ALL があります。

　ALL 演算子を使用するとサブクエリが複数のデータを返してもエラーにはなりません。

> SELECT * FROM sales_slip
	WHERE goods_id = ALL ( SELECT id FROM goods );
Empty set (0.00 sec)

　しかし、結果は空であることが返ってきます。

　ALL 演算子はサブクエリが返す値とメインクエリのカラム値（上記の例では goods_id ）の比較において、比較できない（ UNKOWN ）があると結果が空になります。
　上記の例はサブクエリが複数のデータを返した時にメインクエリのカラムと比較ができないために結果が空になったのです。

　サブクエリとの比較対象が１つのときは想定通りの結果が返ってきます。

> SELECT * FROM sales_slip
	WHERE goods_id = ALL ( SELECT id FROM goods WHERE name = 'candy' );
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  1 | 2023-10-29 07:17:00 |        1 |          3 |    100 |       1 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |
+----+---------------------+----------+------------+--------+---------+

　サブクエリが返す値が複数の場合には想定通りの結果が返ってきません。

> SELECT * FROM sales_slip
	WHERE goods_id = ALL ( SELECT id FROM goods WHERE name = 'candy' OR name = 'snack' );
Empty set (0.00 sec)

　ALL 演算子はサブクエリが複数の値を返すと比較ができないようです。例えば以下のようにサブクエリが返す値が１つのときは想定通りの結果になります。

> SELECT * FROM sales_slip
	WHERE goods_id <> ALL ( SELECT id FROM goods WHERE name = 'candy' );
+----+---------------------+----------+------------+--------+---------+
| id | date_time           | goods_id | unit_price | amount | user_id |
+----+---------------------+----------+------------+--------+---------+
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |
|  3 | 2023-09-28 22:42:10 |        2 |          5 |     30 |       1 |
|  5 | 2023-09-28 22:42:53 |        3 |         10 |     10 |       3 |
+----+---------------------+----------+------------+--------+---------+

　このことは ALL 演算子がなくても同じことが言えます。ただ、 ALL 演算子を使うとエラーとして終了することはありません。
　ALL 演算子は、API でサブクエリを利用するときにデータベースでのエラーで処理が終了するといったことを防ぐことができます。

列が複数で複数行を返すサブクエリ

　列が複数で複数行を返すようなサブクエリを検索条件として比較するには IN 演算子を使用します。
　IN 演算子には複数のカラムと値を比較する機能があります。

　もしくは検索条件の中で個々に比較することも考えられます。

相関サブクエリ

　相関サブクエリは、非相関サブクエリにサブクエリとメインクエリが同じテーブルのカラムを参照しているという条件があるサブクエリの利用方法です。

相関サブクエリ

> SELECT *, ( SELECT unit_price FROM goods WHERE id = s.goods_id ) AS goods_price
	FROM sales_slip AS s
	WHERE goods_id <> ANY ( SELECT id FROM goods AS g WHERE s.unit_price = g.unit_price );
+----+---------------------+----------+------------+--------+---------+-------------+
| id | date_time           | goods_id | unit_price | amount | user_id | goods_price |
+----+---------------------+----------+------------+--------+---------+-------------+
|  2 | 2023-09-26 22:29:58 |        2 |          8 |     50 |       1 |           5 |
|  4 | 2023-09-28 22:42:10 |        1 |          5 |     80 |       2 |           3 |
+----+---------------------+----------+------------+--------+---------+-------------+

　上記の例は、商品テーブルに記録された単価と売上時の単価が違うものを抽出しています。

　相関サブクエリではs.unit_price = g.unit_priceのようにサブクエリの検索条件にメインクエリと同じテーブルのカラムの参照が含まれています。このようにメインクエリと同じテーブルのカラムをサブクエリで参照しているような場合を相関サブクエリといいます。

　相関サブクエリと EXISTS 演算子を利用すればマスターテーブルと関連付けれたデータのあるか／無いかを確認することができます。

EXISTS 演算子

　EXISTS 演算子とは、この演算子に続くサブクエリがデータを返すか／否かを判定する演算子です。

　例えば以下は、商品マスターに記録された商品名の中から売上があったものだけを抽出しています。

EXISTS 演算子

> SELECT * FROM goods AS g
WHERE EXISTS (SELECT 1 FROM sales_slip AS s WHERE s.goods_id = g.id);
+----+-----------+------------+
| id | name      | unit_price |
+----+-----------+------------+
|  1 | candy     |          3 |
|  2 | snack     |          5 |
|  3 | chocolate |          8 |
+----+-----------+------------+

　このように、EXISTS 演算子によってマスターに関連付けられたテーブルにデータがあるか／無いかを確認することができます。

　マスターテーブルとの関連付けは FOREIGN KEY（外部キー制約）によって作成します。

　上記の例とは逆に NOT EXISTS 演算子を利用すれば売上がなかった商品を確認することができます。

EXISTS 演算子

> SELECT * FROM goods AS g
WHERE NOT EXISTS (SELECT 1 FROM sales_slip AS s WHERE s.goods_id = g.id);
+----+----------------+------------+
| id | name           | unit_price |
+----+----------------+------------+
|  4 | ice            |          8 |
|  5 | lollipop candy |         15 |
+----+----------------+------------+

　MySQL や PostgreSQL で EXISTS 演算子はサブクエリのみが処理できるようです。

　相関サブクエリには、UPDATE や DELETE を行う場合にサブクエリも同じテーブルを参照する場合もあります。

　例えば、伝票を取引ごとに記録するためにメインの取引名や日付を記録したテーブルと詳細な取引内容をテーブルがあるとします。
　メインは作成したけど詳細データは作成していない場合にメインのデータを削除したいとします。
　そのような場合は、以下のように詳細データのない場合は該当するメインデータを抽出削除します。

DELETE と EXISTS 演算子

> DELETE FROM main_slip AS m
WHERE NOT EXISTS (SELECT 1 FROM sales_slip AS s WHERE s.main_id = m.id);

　上記は例だけでテストデータは作成していません。

　注意点としては UPDATE や DELETE とサブクエリ組み合わせでは AS でのテーブルエリアスは機能しない場合があります。