Lesson 12

副問合せ

Lesson 12 Chapter 1
本レッスンについて

本レッスンでは「副問合せ」というものについて学びます。
初見では難しく思えるかもしれませんが、基本を理解すればそんなに難しいものではありません。
システム開発においてもよく使用されるため、このレッスンで、仕組みと使い方をしっかりと理解しましょう。

副問合せとは

まず、言葉の意味ですが、「問合せ」というのはデータベースに対する命令（SQL 文）のことであり、クエリ（query）を日本語に訳したものです。
そして、「副問合せ」というのは、問合せの中に入れ子で指定する問合せ（SQL 文）のことであり、サブクエリ（subquery）ともいいます。

具体的な SQL 文がどのようになるか見てみましょう。 

SELECT * FROM item WHERE id IN (SELECT DISTINCT item_id FROM sales);

上記の SELECT 文の中に、括弧書で (SELECT DISTINCT item_id FROM sales) という SELECT 文が入っているのが分かると思います。

この括弧書の中の SQL 文が「副問合せ（サブクエリ）」ということになります。
この SQL 文でどのような結果が取得できるかは、後ほど説明いたします。

テーブルの準備

本レッスンで使用するテーブルを作成します。
作成するのは、item テーブル（商品テーブル）と sales テーブル（販売テーブル）の 2 つです。

データベースの選択

まず、test データベースを選択します。

PowerShell
mysql> use test;

item テーブル（商品テーブル）の作成

次のように、本レッスン用の item テーブルを作成しましょう。

PowerShell
mysql> CREATE TABLE item (
  id varchar(8) PRIMARY KEY,
  name varchar(20),
  price int,
  category varchar(20)
);

mysql> INSERT INTO item VALUES
  ('A001', 'ノートPC', 200000, '電化製品'),
  ('A002', 'テーブル', 48000, '家具'),
  ('A003', '冷蔵庫', 80000, '電化製品'),
  ('A004', '電子レンジ', 28000, '電化製品'),
  ('A005', '椅子', 28000, '家具');


mysql> SELECT * FROM item;
+------+------------+--------+----------+
| id   | name       | price  | category |
+------+------------+--------+----------+
| A001 | ノートPC   | 200000 | 電化製品 |
| A002 | テーブル   |  48000 | 家具     |
| A003 | 冷蔵庫     |  80000 | 電化製品 |
| A004 | 電子レンジ |  28000 | 電化製品 |
| A005 | 椅子       |  28000 | 家具     |
+------+------------+--------+----------+
5 rows in set (0.02 sec)

以上のように、商品ID（id）、商品名（name）、価格（price）、カテゴリ（category）という 4 つのカラムに持ったテーブルになります。

sales テーブル（販売テーブル）の作成

続いて、sales テーブルを作成します。

PowerShell
mysql> CREATE TABLE sales (
  id int PRIMARY KEY,
  item_id varchar(8),
  quantity int,
  sale_date date,
  foreign key fk_item (item_id) references item(id)
);

mysql> INSERT INTO sales VALUES
  (1, 'A001', 1, '2023-01-10'),
  (2, 'A005', 4, '2023-01-10'),
  (3, 'A004', 4, '2023-01-10'),
  (4, 'A005', 2, '2023-01-11'),
  (5, 'A002', 1, '2023-01-11'),
  (6, 'A001', 1, '2023-01-12'),
  (7, 'A005', 1, '2023-01-12'),
  (8, 'A001', 2, '2023-01-12'),
  (9, 'A002', 1, '2023-01-13');

mysql> SELECT * FROM sales;
+----+---------+----------+------------+
| id | item_id | quantity | sale_date  |
+----+---------+----------+------------+
|  1 | A001    |        1 | 2023-01-10 |
|  2 | A005    |        4 | 2023-01-10 |
|  3 | A004    |        4 | 2023-01-10 |
|  4 | A005    |        2 | 2023-01-11 |
|  5 | A002    |        1 | 2023-01-11 |
|  6 | A001    |        1 | 2023-01-12 |
|  7 | A005    |        1 | 2023-01-12 |
|  8 | A001    |        2 | 2023-01-12 |
|  9 | A002    |        1 | 2023-01-13 |
+----+---------+----------+------------+
9 rows in set (0.00 sec)

販売ID（id）、商品ID（item_id）、販売数（quantity）、販売日（sale_date）という 4 つのカラムに持ったテーブルができていれば OK です。
なお、商品ID（item_id）は、商品テーブルの id と紐づけられている（外部キー制約がある）ことに注意しておいてください。

Lesson 12 Chapter 2
副問合せ文の書き方

副問合せは SELECT 文の中だけでなく、INSERT 文、UPDATE 文、DLETE 文の中でも使用できますが、 ここでは、利用頻度の高い「SELECT 文の中における副問合せ」について学習していきます。

基本的な副問合せ文

まず、冒頭にも上げた次の例について、詳しく見ていきます。
この SQL 文は「販売テーブルに登録された商品の一覧」を取得するものとなります。 

SELECT * FROM item WHERE id IN (SELECT DISTINCT item_id FROM sales);

副問合せの部分は (SELECT DISTINCT item_id FROM sales) のところです。
この副問合せは、単純に sales テーブルの「item_id」カラムを取得する SQL 文ですが、DISTINCT で重複排除を行っていますので、取得されるデータは「A001，A002，A004，A005」の 4 つとなります（※）。

SQL の実行時は、副問合せが先に処理されますので、その時点で次のような状態が生じます。 

SELECT * FROM item WHERE id IN (A001，A002，A004，A005);

つまり、item（商品）テーブルのうち id が「sales（販売）テーブルの item_id」に含まれるレコードのみを取得する SQL 文ということになります。

DISTINCT で重複データを除外する

SELECT 文中に DISTINCT を使用することで、重複データを除外して、一意（ユニーク）のデータとすることができます。

DISTINCT の具体例

例えば、本レッスンの sales テーブル（下記）の「item_id」は、全部で 9 レコード分ありますが、同じ値のものが重複している状態です。 

+----+---------+----------+------------+
| id | item_id | quantity | sale_date  |
+----+---------+----------+------------+
|  1 | A001    |        1 | 2023-01-10 |
|  2 | A005    |        4 | 2023-01-10 |
|  3 | A004    |        4 | 2023-01-10 |
|  4 | A005    |        2 | 2023-01-11 |
|  5 | A002    |        1 | 2023-01-11 |
|  6 | A001    |        1 | 2023-01-12 |
|  7 | A005    |        1 | 2023-01-12 |
|  8 | A001    |        2 | 2023-01-12 |
|  9 | A002    |        1 | 2023-01-13 |
+----+---------+----------+------------+

単純に SELECT item_id FROM sales; で「item_id」を取得すると、そのまま 9 個のデータが取得されます。
しかし、次のように 重複排除したいカラム名の前に DISTINCT を記述して SQL を実行することで、簡単に重複排除したデータを取得することができます。

PowerShell
mysql> SELECT DISTINCT item_id FROM sales;
+---------+
| item_id |
+---------+
| A001    |
| A002    |
| A004    |
| A005    |
+---------+
4 rows in set (0.00 sec)

DISTINCT もよく使用されますので、覚えておくようにしましょう。

それでは、実際に SQL 文を実行してみましょう。 

mysql> SELECT * FROM item WHERE id IN (SELECT DISTINCT item_id FROM sales);
+------+------------+--------+----------+
| id   | name       | price  | category |
+------+------------+--------+----------+
| A001 | ノートPC   | 200000 | 電化製品 |
| A002 | テーブル   |  48000 | 家具     |
| A004 | 電子レンジ |  28000 | 電化製品 |
| A005 | 椅子       |  28000 | 家具     |
+------+------------+--------+----------+
4 rows in set (0.00 sec)

販売テーブルに登録された商品の一覧を取得することができました。

副問合せ（サブクエリ）は、SELECT 文の中の様々な場所で使用することができます。
ここでは、以下の 3 つのパターンにつき、それぞれ詳細を見ていきます。

  • WHERE 句：条件指定にサブクエリを使用する
  • FROM 句：テーブル指定にサブクエリを使用する
  • SELECT 句：カラム値にサブクエリを使用する
なお、HAVING 句内でも副問合せは使用できますが、ここでは特に触れません。

WHERE 句内で副問合せ

先ほどの副問合せの例も WHERE 句を使用しました。その中で、IN 演算子を使用しました。
IN 演算子の他にも、副問合せにおいて定番で使用される演算子（述語）があります。主に次のようなものです。

No 演算子 説明
1 IN 副問合せの結果（配列）に一致する値がある
2 NOT IN 副問合せの結果（配列）に一致する値がない
3 EXISTS 副問合せの結果が 1 つでも存在する（真偽値）
4 NOT EXISTS 副問合せの結果が 1 つも存在しない（真偽値）
5 ANY 副問合せの結果（配列）を比較演算子で判定
どれか 1 つが true であれば true
6 ALL 副問合せの結果（配列）を比較演算子で判定
全てが true であれば true

IN 演算子については、既に何度も使用していますのでここでの説明は省略します。
また、ANY 演算子、ALL 演算子については、次の Chapter で解説するため、こちらも省略します。
というわけで、ここでは EXISTS 演算子について、具体例を見ていきます。

EXISTS 演算子の例

ここでも、item（商品）テーブルにつき、sales（販売）テーブルに登録されたもの（つまり、item.id = sales.item_id のレコードが存在するもの）を取得します。
EXISTS 演算子を使用して SQL 文を書くと次のようになります。 

SELECT * FROM item WHERE EXISTS (SELECT * FROM sales WHERE item.id = sales.item_id);

ここで、特殊な状況が 1 つ出てきています。それは、外側の SQL 文で指定した item テーブルの値を、内側の副問合せの中で item.id として使用していることです。
このように内側から外側の属性（検索結果）を使用することも可能です（これを「相関副問合せ」と呼んでいます）。

それでは、SQL を実行してみましょう。

PowerShell
mysql> SELECT * FROM item WHERE EXISTS (SELECT * FROM sales WHERE item.id = sales.item_id);
+------+------------+--------+----------+
| id   | name       | price  | category |
+------+------------+--------+----------+
| A001 | ノートPC   | 200000 | 電化製品 |
| A002 | テーブル   |  48000 | 家具     |
| A004 | 電子レンジ |  28000 | 電化製品 |
| A005 | 椅子       |  28000 | 家具     |
+------+------------+--------+----------+
4 rows in set (0.00 sec)

また、次のように NOT EXIXTS 演算子でも実行してみましょう。

PowerShell
mysql> SELECT * FROM item WHERE NOT EXISTS (SELECT * FROM sales WHERE item.id = sales.item_id);
+------+--------+-------+----------+
| id   | name   | price | category |
+------+--------+-------+----------+
| A003 | 冷蔵庫 | 80000 | 電化製品 |
+------+--------+-------+----------+
1 row in set (0.00 sec)

EXIXTS 演算子の場合と逆の結果が取得できることを確認できたと思います。

FROM 句内で副問合せ

FROM 句で副問合せを使用することで、加工・結合したテーブルを一つのテーブルとして扱うことができます。
具体例で確認してみましょう。 

SELECT * FROM
  (SELECT item_id, SUM(quantity) AS total_quantity FROM sales GROUP BY item_id) AS s
  ORDER BY s.total_quantity DESC;

長く見えますが、副問合せの部分以外を見ると次のようなシンプルな SQL です。
SELECT * FROM ([副問合せ]) AS s ORDER BY s.total_quantity DESC;

続いて、副問合せの部分を確認してみましょう。抜粋すると、次のようになっています。 

SELECT item_id, SUM(quantity) AS total_quantity FROM sales GROUP BY item_id

これは、sales テーブルを item_id でグループ化した上で、カラムとして item_id と quantity（販売数）の合計値を取得しているものとなります。
これをテーブルとして扱い、外側（主たる問合せ）の部分で、販売数の降順に並べる、という構造になっています。
では、SQL を実行して、結果を確認してみましょう。

PowerShell
mysql> SELECT * FROM
  (SELECT item_id, SUM(quantity) AS total_quantity FROM sales GROUP BY item_id) AS s
  ORDER BY s.total_quantity DESC;
+---------+----------------+
| item_id | total_quantity |
+---------+----------------+
| A005    |              7 |
| A001    |              4 |
| A004    |              4 |
| A002    |              2 |
+---------+----------------+
4 rows in set (0.00 sec)

なお、副問合せの中でセレクトされたカラムを外側（主たる問合せ）で使用する場合は、副問合せのテーブルに AS 句で別名を付けてから呼び出す必要があります。
上の例でいうと AS s で別名を付け、s.total_quantity という形で呼出しをしています。

SELECT 句内で副問合せ

SELECT 句内でも副問合せを使用することができます。
具体例で確認してみましょう。 

SELECT
  id,
  item_id,
  (SELECT price FROM item AS i WHERE i.id = s.item_id) AS price,
  quantity
FROM sales AS s;

見てのとおりですが、SELECT 句で 1 つ副問合せを使用しています。
item（商品）テーブルから該当する price（価格）を取得して、カラムとして追加しています。
それでは、実行してみましょう。主たる問合せで使用するテーブルは、sales（販売）テーブルです。

PowerShell
mysql> SELECT
  id,
  item_id,
  (SELECT price FROM item AS i WHERE i.id = s.item_id) AS price,
  quantity
FROM sales AS s;
+----+---------+--------+----------+
| id | item_id | price  | quantity |
+----+---------+--------+----------+
|  1 | A001    | 200000 |        1 |
|  2 | A005    |  28000 |        4 |
|  3 | A004    |  28000 |        4 |
|  4 | A005    |  28000 |        2 |
|  5 | A002    |  48000 |        1 |
|  6 | A001    | 200000 |        1 |
|  7 | A005    |  28000 |        1 |
|  8 | A001    | 200000 |        2 |
|  9 | A002    |  48000 |        1 |
+----+---------+--------+----------+
9 rows in set (0.00 sec)

以上のように、関連する price（価格）を使用することができました。

Lesson 12 Chapter 3
限定述語

前の Chapter でも触れましたが、副問合せで使用される演算子のうち ANY と ALL について見ていきます。
これらは「限定述語」と呼ばれており、いずれも比較演算子（=，>，，>=， など）の後に指定します。

構文

構文は次のようになります。 

WHERE [対象カラム名] [比較演算子] ANY (副問合せ)
WHERE [対象カラム名] [比較演算子] ALL (副問合せ)

副問合せで取得された結果（値）全てと比較を行い、次のルールで判定を行います。

  • 「ANY」は、条件が 1 つでも true の場合は true を返す。
  • 「ALL」は、全ての条件が true の場合にのみ true を返す。
以下、具体例で確認していきましょう。

ANY を使用した副問合せ

次のように、ANY を使用した副問合せを作成します。 

SELECT * FROM sales WHERE quantity > ANY (SELECT quantity FROM sales WHERE item_id = 'A001');

副問合せの部分は SELECT quantity FROM sales WHERE item_id = 'A001' です。
これで取得される値（quantity）は「1，1，2」の 3 つとなります。

主問合せでは ANY を使用して quantity を比較していますので、「1，1，2」のいずれかより大きい値（つまり、最低で 1 より大きい値）であれば OK ということになります。
では、SQL を実行してみましょう。 

mysql> SELECT * FROM sales WHERE quantity > ANY (SELECT quantity FROM sales WHERE item_id = 'A001');
+----+---------+----------+------------+
| id | item_id | quantity | sale_date  |
+----+---------+----------+------------+
|  2 | A005    |        4 | 2023-01-10 |
|  3 | A004    |        4 | 2023-01-10 |
|  4 | A005    |        2 | 2023-01-11 |
|  8 | A001    |        2 | 2023-01-12 |
+----+---------+----------+------------+
4 rows in set (0.00 sec)

以上のように、quantity が 1 より大きいレコードを取得することができました。

ALL を使用した副問合せ

次のように、ALL を使用した副問合せを作成します。 

SELECT * FROM sales WHERE quantity > ALL (SELECT quantity FROM sales WHERE item_id = 'A001');

副問合せは先ほどと同じで、取得される値（quantity）は「1，1，2」の 3 つとなります。

主問合せでは ALL を使用して quantity を比較していますので、「1，1，2」の全てより大きい値（つまり、2 より大きい値）であれば OK ということになります。
では、SQL を実行してみましょう。 

mysql> SELECT * FROM sales WHERE quantity > ALL (SELECT quantity FROM sales WHERE item_id = 'A001');
+----+---------+----------+------------+
| id | item_id | quantity | sale_date  |
+----+---------+----------+------------+
|  2 | A005    |        4 | 2023-01-10 |
|  3 | A004    |        4 | 2023-01-10 |
+----+---------+----------+------------+
2 rows in set (0.00 sec)

想定したとおり、quantity が 2 より大きいレコードを取得することができました。

Lesson 12 Chapter 4
WITH 句

WITH 句は「共通テーブル式」ともいい、テーブル（副問合せ）を一時的に保持し後から参照できるようにするものです。
副問合せの SQL 文は複雑になりやすいですが、WITH 句を使用することで、可読性を落とさずに SQL 文を作成することができます。

※ WITH 句は MySQL8.0 からの新機能となります。それ以前のバージョンでは実行できませんのでご注意ください。

構文

WITH 句の基本構文は次のとおりです。 

WITH [共通テーブル式名] AS (副問合せ)

複数指定する場合は、コンマ区切りで指定できます。 

WITH [共通テーブル式名] AS (副問合せ), [共通テーブル式名] AS (副問合せ)

WITH 句を使用した SQL 文

Chapter 2 の FROM 句の副問合せのところで作成した SQL 文は次のように可読性の悪いものでした。 

SELECT * FROM
  (SELECT item_id, SUM(quantity) AS total_quantity FROM sales GROUP BY item_id) AS s
  ORDER BY s.total_quantity DESC;

ここで、WITH 句を使って改善してみましょう。
次のように SQL を実行してみてください。 

mysql> WITH total_sales AS (
  SELECT item_id, SUM(quantity) AS total_quantity FROM sales GROUP BY item_id
)

SELECT * FROM total_sales ORDER BY total_quantity DESC;
+---------+----------------+
| item_id | total_quantity |
+---------+----------------+
| A005    |              7 |
| A001    |              4 |
| A004    |              4 |
| A002    |              2 |
+---------+----------------+
4 rows in set (0.00 sec)

以上のように見通しの良い形になりました。
なお、total_sales に AS 句で別名を付けなくとも、そのまま普通のテーブルとして使用できます。これも良いところですね。

本レッスンの内容は、以上のとおりです。
副問合せは理解できるまでは難しく感じますが、仕組みが分かると SQL で自由な組合せをすることができますので、繰り返し学習してご自身のものとしていただければと思います。