MySQL のサブクエリとネスト操作

はじめに

この実験では、MySQL のサブクエリとネスト操作の威力を探ります。主な焦点は、他のテーブルまたは同じテーブルから導き出された条件に基づいてデータをフィルタリングするために、WHERE 句内でサブクエリを使用することです。

MySQL サーバーに接続し、データベースとテーブル（顧客と注文）を作成し、そして特定の値を超える合計金額の注文を行った顧客を特定するためにサブクエリを利用した SQL クエリを構築する方法を学びます。この実験では、サブクエリとともに EXISTS を使用する方法、相関サブクエリのテスト方法、およびサブクエリのパフォーマンスの比較方法についても取り上げます。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/AdvancedFeaturesGroup(["Advanced Features"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_database("Database Deletion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/drop_table("Table Removal") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/AdvancedFeaturesGroup -.-> mysql/stored_procedures("Procedure Management") subgraph Lab Skills mysql/use_database -.-> lab-550916{{"MySQL のサブクエリとネスト操作"}} mysql/create_database -.-> lab-550916{{"MySQL のサブクエリとネスト操作"}} mysql/drop_database -.-> lab-550916{{"MySQL のサブクエリとネスト操作"}} mysql/create_table -.-> lab-550916{{"MySQL のサブクエリとネスト操作"}} mysql/drop_table -.-> lab-550916{{"MySQL のサブクエリとネスト操作"}} mysql/select -.-> lab-550916{{"MySQL のサブクエリとネスト操作"}} mysql/insert -.-> lab-550916{{"MySQL のサブクエリとネスト操作"}} mysql/stored_procedures -.-> lab-550916{{"MySQL のサブクエリとネスト操作"}} end

WHERE 句でサブクエリを記述する

このステップでは、SQL 文の WHERE 句内でサブクエリを使用する方法を学びます。サブクエリとは、別のクエリの中にネストされたクエリです。他のテーブルまたは同じテーブルから導き出された条件に基づいてデータを取得するための強力なツールです。

サブクエリの理解

サブクエリ（または内部クエリ）は、より大きなクエリの中にネストされた SQL クエリです。サブクエリは最初に実行され、その結果が外部クエリで使用されます。サブクエリは WHERE、SELECT、FROM、および HAVING 句に含まれることがあります。

WHERE 句では、サブクエリは通常、条件に基づいて外部クエリの結果をフィルタリングするために使用されます。サブクエリは単一の値または値のセットを返し、外部クエリはそれを比較に使用します。

シナリオ

customers と orders の 2 つのテーブルがあると想像してみてください。customers テーブルには顧客情報（例：customer_id、name、city）が含まれ、orders テーブルには注文情報（例：order_id、customer_id、order_date、total_amount）が含まれています。

合計金額が 100 ドルを超える注文を少なくとも 1 件行ったすべての顧客を見つけたいとします。

手順

MySQL サーバーに接続する

まず、mysql クライアントを使用して MySQL サーバーに接続します。MySQL サーバーのユーザー名とパスワードが必要です。この実験では、パスワードなしで root ユーザーとして接続できると仮定します。

ターミナルを開き、次のコマンドを実行します。
```
mysql -u root
```
MySQL プロンプト mysql> が表示されるはずです。

データベースとテーブルを作成する

まだデータベースとテーブルがない場合は、今すぐ作成します。labdb という名前のデータベースと customers および orders テーブルを作成しましょう。

CREATE DATABASE IF NOT EXISTS labdb;
USE labdb;

CREATE TABLE IF NOT EXISTS customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(255),
    city VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

サンプルデータを挿入する

テーブルにいくつかのサンプルデータを挿入します。

INSERT INTO customers (customer_id, name, city) VALUES
(1, 'Alice Smith', 'New York'),
(2, 'Bob Johnson', 'Los Angeles'),
(3, 'Charlie Brown', 'Chicago'),
(4, 'David Lee', 'Houston');

INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES
(101, 1, '2023-01-15', 120.00),
(102, 2, '2023-02-20', 80.00),
(103, 1, '2023-03-10', 150.00),
(104, 3, '2023-04-05', 200.00),
(105, 2, '2023-05-12', 110.00),
(106, 4, '2023-06-18', 90.00);

WHERE 句でサブクエリを記述する

ここで、合計金額が 100 ドルを超える注文を行った顧客を見つけるクエリを記述します。
```
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 100);
```
説明
- サブクエリ (SELECT customer_id FROM orders WHERE total_amount > 100) は、total_amount が 100 を超える orders テーブルから customer_id を選択します。
- 外部クエリ SELECT * FROM customers WHERE customer_id IN (...) は、customer_id がサブクエリによって返される customer_id のセットに含まれる customers テーブルのすべての列を選択します。

クエリを実行する

MySQL クライアントでクエリを実行します。次のような出力が表示されるはずです。

+-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
+-------------+-------------+-----------+
3 rows in set (0.00 sec)

これは、Alice Smith、Bob Johnson、および Charlie Brown が合計金額が 100 ドルを超える注文を行ったことを示しています。

EXISTS を使用した代替方法

EXISTS 演算子を使用して同じ結果を得ることもできます。このアプローチは、特に大規模なデータセットでは、場合によってはより効率的です。
```
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id AND o.total_amount > 100
);
```
このクエリは、各顧客について合計金額が 100 を超える注文が少なくとも 1 件存在するかどうかを確認します。

EXISTS クエリを実行する

MySQL クライアントでクエリを実行します。前と同じ出力が表示されるはずです。

+-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
+-------------+-------------+-----------+
3 rows in set (0.00 sec)

クリーンアップ（オプション）

データベースとテーブルをクリーンアップしたい場合は、次のコマンドを実行できます。
```
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
```
最後に、exit と入力して Enter キーを押して MySQL クライアントを終了することを忘れないでください。

サブクエリで EXISTS を使用する

このステップでは、MySQL でサブクエリとともに EXISTS 演算子を使用する方法を学びます。EXISTS 演算子は、サブクエリに行が存在するかどうかをテストするために使用されます。サブクエリが何らかの行を返す場合は TRUE を返し、そうでない場合は FALSE を返します。

EXISTS の理解

EXISTS 演算子は、SQL 文の WHERE 句で、別のテーブルに関連データが存在するかどうかに基づいて結果をフィルタリングするためによく使用されます。IN や JOIN 操作を使用する代替手段として強力で、特に大規模なデータセットを扱う場合には、場合によってはより効率的です。

IN とは異なり、EXISTS は実際にサブクエリからデータを取得しません。単に何らかの行が返されるかどうかを確認するだけです。一致が存在するかどうかを知るだけで、実際の値が必要ない場合、IN よりも高速になることがあります。

シナリオ

前のステップの customers と orders テーブルを引き続き使用して、少なくとも 1 件の注文を行ったすべての顧客を見つけましょう。

前提条件

前のステップ（「WHERE 句でサブクエリを記述する」）を完了し、labdb データベース、customers テーブル、および orders テーブルにデータが入力されていることを確認してください。そうでない場合は、前のステップの指示に従ってデータベースとテーブルを作成し、サンプルデータを挿入してください。

手順

MySQL サーバーに接続する

mysql クライアントを使用して MySQL サーバーに接続します。
```
mysql -u root
```
データベースを選択する

labdb データベースを選択します。
```
USE labdb;
```
EXISTS を使用したクエリを記述する

少なくとも 1 件の注文を行った顧客を見つけるクエリを記述します。
```
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
説明
- 外部クエリ SELECT * FROM customers c は、customers テーブルのすべての列を選択し、エイリアスを c とします。
- WHERE EXISTS (...) 句は、サブクエリが何らかの行を返すかどうかを確認します。
- サブクエリ SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id は、orders テーブル（エイリアスを o とする）から値 1（任意の定数値で構いません）を選択します。このとき、orders テーブルの customer_id が customers テーブルの customer_id と一致する場合に選択されます。
- EXISTS 演算子は、サブクエリが少なくとも 1 行を返す場合に TRUE を返し、これは顧客が少なくとも 1 件の注文を行ったことを示します。

クエリを実行する

MySQL クライアントでクエリを実行します。次のような出力が表示されるはずです。

+-------------+-------------+-----------+
| customer_id | name        | city      |
+-------------+-------------+-----------+
|           1 | Alice Smith | New York  |
|           2 | Bob Johnson | Los Angeles |
|           3 | Charlie Brown | Chicago   |
|           4 | David Lee   | Houston   |
+-------------+-------------+-----------+
4 rows in set (0.00 sec)

これは、4 人の顧客全員が少なくとも 1 件の注文を行ったことを示しています。

クエリを修正する（オプション）

注文を行っていない顧客を見つけるためにクエリを修正しましょう。これは NOT EXISTS を使用することで行えます。
```
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
サンプルデータのすべての顧客が注文を行っているため、このクエリは空の結果セットを返すはずです。
修正したクエリを実行する

MySQL クライアントで修正したクエリを実行します。次のような出力が表示されるはずです。
```
Empty set (0.00 sec)
```
クリーンアップ（オプション）

データベースとテーブルをクリーンアップしたい場合は、次のコマンドを実行できます。
```
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
```
最後に、exit と入力して Enter キーを押して MySQL クライアントを終了することを忘れないでください。

相関サブクエリをテストする

このステップでは、MySQL の相関サブクエリについて学びます。相関サブクエリとは、外部クエリの列を参照するサブクエリです。これは、外部クエリの各行に対してサブクエリが 1 回実行されることを意味します。

相関サブクエリの理解

一度だけ実行される単純なサブクエリとは異なり、相関サブクエリはその値を外部クエリに依存します。サブクエリは外部クエリの現在の行の値を使用して結果を決定します。このため、相関サブクエリは特定の種類のクエリに対してより強力ですが、特に大規模なデータセットの場合、単純なサブクエリよりも潜在的に効率が低くなることがあります。

シナリオ

customers と orders テーブルを引き続き使用して、すべての注文の平均注文金額よりも高い金額の注文を行ったすべての顧客を見つけましょう。

前提条件

前のステップを完了し、labdb データベース、customers テーブル、および orders テーブルにデータが入力されていることを確認してください。そうでない場合は、前のステップの指示に従ってデータベースとテーブルを作成し、サンプルデータを挿入してください。

手順

MySQL サーバーに接続する

mysql クライアントを使用して MySQL サーバーに接続します。
```
mysql -u root
```
データベースを選択する

labdb データベースを選択します。
```
USE labdb;
```
相関サブクエリを記述する

平均注文金額よりも高い金額の注文を行った顧客を見つけるクエリを記述します。
```
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > (SELECT AVG(total_amount) FROM orders)
);
```
説明
- 外部クエリ SELECT c.customer_id, c.name FROM customers c は、customers テーブル（エイリアスを c とする）から customer_id と name を選択します。
- WHERE EXISTS (...) 句は、サブクエリが何らかの行を返すかどうかを確認します。
- 相関サブクエリ SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.total_amount > (SELECT AVG(total_amount) FROM orders) は、orders テーブル（エイリアスを o とする）から値 1 を選択します。このとき、以下の条件が満たされます。
  - o.customer_id = c.customer_id：これが相関部分です。サブクエリは外部クエリの customers テーブルの customer_id を参照しています。
  - o.total_amount > (SELECT AVG(total_amount) FROM orders)：この条件は、注文金額がすべての注文の平均注文金額よりも高いかどうかを確認します。AVG(total_amount) サブクエリは非相関サブクエリであり、平均注文金額を取得するために一度だけ実行されます。

クエリを実行する

MySQL クライアントでクエリを実行します。まず、平均注文金額を求めましょう。

SELECT AVG(total_amount) FROM orders;

出力は次のようになるはずです。

+---------------------+
| AVG(total_amount)   |
+---------------------+
| 125.0000            |
+---------------------+
1 row in set (0.00 sec)

つまり、平均注文金額は 125 です。次に、相関サブクエリを実行します。

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.total_amount > (SELECT AVG(total_amount) FROM orders)
);

次のような出力が表示されるはずです。

+-------------+-------------+
| customer_id | name        |
+-------------+-------------+
|           1 | Alice Smith |
|           3 | Charlie Brown |
+-------------+-------------+
2 rows in set (0.00 sec)

これは、Alice Smith と Charlie Brown が平均注文金額よりも高い金額の注文を行ったことを示しています。

別の例：各顧客の最高注文金額を見つける
```
SELECT c.customer_id, c.name, (
    SELECT MAX(o.total_amount)
    FROM orders o
    WHERE o.customer_id = c.customer_id
) AS highest_order_amount
FROM customers c;
```
このクエリは、各顧客の ID、名前、および最高注文金額を取得します。相関サブクエリは、各顧客の最大注文金額を個別に計算します。

クエリを実行する

MySQL クライアントでクエリを実行します。次のような出力が表示されるはずです。

+-------------+-------------+-----------------------+
| customer_id | name        | highest_order_amount  |
+-------------+-------------+-----------------------+
|           1 | Alice Smith |                150.00 |
|           2 | Bob Johnson |                110.00 |
|           3 | Charlie Brown |                200.00 |
|           4 | David Lee   |                 90.00 |
+-------------+-------------+-----------------------+
4 rows in set (0.00 sec)

クリーンアップ（オプション）

データベースとテーブルをクリーンアップしたい場合は、次のコマンドを実行できます。
```
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
```
最後に、exit と入力して Enter キーを押して MySQL クライアントを終了することを忘れないでください。

サブクエリのパフォーマンスを比較する

このステップでは、MySQL で異なる種類のサブクエリのパフォーマンスを比較する方法を学びます。サブクエリのパフォーマンス特性を理解することは、特に大規模なデータセットを扱う場合に、効率的な SQL クエリを書くために重要です。

パフォーマンスに関する考慮事項の理解

サブクエリのパフォーマンスは、いくつかの要素に影響を受けます。以下がその要素です。

データサイズ：クエリに関係するテーブルのサイズ。
サブクエリの種類：サブクエリが相関サブクエリか非相関サブクエリか。
インデックス：テーブルにインデックスが存在するか、その有効性。
MySQL のバージョン：使用している MySQL の特定のバージョン。クエリの最適化手法はバージョンによって異なる場合があります。

シナリオ

customers と orders テーブルを引き続き使用して、少なくとも 1 件の注文を行ったすべての顧客を見つけるために、IN を使用したサブクエリと EXISTS を使用したサブクエリのパフォーマンスを比較しましょう。

前提条件

前のステップを完了し、labdb データベース、customers テーブル、および orders テーブルにデータが入力されていることを確認してください。パフォーマンス比較をより有意義にするために、orders テーブルにさらにデータを追加します。

手順

MySQL サーバーに接続する

mysql クライアントを使用して MySQL サーバーに接続します。
```
mysql -u root
```
データベースを選択する

labdb データベースを選択します。
```
USE labdb;
```
orders テーブルにさらにデータを追加する

パフォーマンス比較をより現実的なものにするために、orders テーブルに大量のデータを追加しましょう。各顧客に対して 1000 件の注文を挿入します。以下の内容で ~/project/insert_orders.sql という名前のファイルを作成します。
```
USE labdb;
DELIMITER //
CREATE PROCEDURE insert_many_orders()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000 DO
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (1, CURDATE(), 50.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (2, CURDATE(), 75.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (3, CURDATE(), 100.00);
    INSERT INTO orders (customer_id, order_date, total_amount) VALUES (4, CURDATE(), 125.00);
    SET i = i + 1;
  END WHILE;
END//
DELIMITER ;
CALL insert_many_orders();
DROP PROCEDURE insert_many_orders;
```
説明
- この SQL スクリプトは insert_many_orders という名前のストアドプロシージャを作成します。
- このプロシージャは、4 人の顧客それぞれに対して 1000 件の注文を orders テーブルに挿入します。
- データを挿入した後、プロシージャは削除されます。
SQL スクリプトを実行する

次のコマンドを使用して SQL スクリプトを実行します。
```
mysql -u root < ~/project/insert_orders.sql
```
IN を使用したクエリ

IN を使用して、少なくとも 1 件の注文を行ったすべての顧客を見つける次のクエリを実行します。
```
SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
```
EXPLAIN を使用してクエリ実行計画を分析する

クエリを実行する前に、EXPLAIN コマンドを使用してクエリ実行計画を分析します。これにより、MySQL がクエリをどのように実行する予定かを理解し、潜在的なパフォーマンスのボトルネックを特定することができます。
```
EXPLAIN SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
```
EXPLAIN の出力には、アクセスされるテーブル、使用されるインデックス（あれば）、および操作が実行される順序が表示されます。使用される結合またはアクセス方法の種類を示す type 列に注意してください。
クエリを実行し、実行時間を測定する

実行時間を測定するには、BENCHMARK() 関数を使用します。この関数は、指定された回数だけ式を実行し、実行にかかった時間を返します。単一の実行時間を測定したいので、BENCHMARK(1, ...) を使用します。
```
SELECT BENCHMARK(1, (SELECT *
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders)));
```
出力は、ベンチマーク結果が含まれる 1 行になります。実行にかかった時間を記録してください。
EXISTS を使用したクエリ

EXISTS を使用して、少なくとも 1 件の注文を行ったすべての顧客を見つける次のクエリを実行します。
```
SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
EXPLAIN を使用してクエリ実行計画を分析する

EXISTS クエリのクエリ実行計画を分析するために EXPLAIN コマンドを使用します。
```
EXPLAIN SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);
```
IN クエリの実行計画と比較してみましょう。アクセスされるテーブル、使用されるインデックス、またはアクセス方法に違いはありますか？
クエリを実行し、実行時間を測定する

BENCHMARK() 関数を使用して EXISTS クエリの実行時間を測定します。
```
SELECT BENCHMARK(1, (SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
)));
```
IN クエリの実行時間と比較してみましょう。どちらのクエリが速かったですか？
観察結果

一般的に、サブクエリが大量の行を返す場合、EXISTS は IN よりもパフォーマンスが良い傾向があります。これは、IN は外部クエリの値をサブクエリが返すすべての値と比較する必要があるのに対し、EXISTS は一致するものを見つけるとすぐに停止するためです。ただし、実際のパフォーマンスは、特定のクエリ、データ、およびデータベースシステムによって異なる場合があります。
クリーンアップ（オプション）

データベースとテーブルをクリーンアップしたい場合は、次のコマンドを実行できます。
```
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP DATABASE IF EXISTS labdb;
```
最後に、exit と入力して Enter キーを押して MySQL クライアントを終了することを忘れないでください。

まとめ

この実験では、まず SQL 文の WHERE 句内でサブクエリを使用し、他のテーブルまたは同じテーブルから導き出された条件に基づいて結果をフィルタリングする方法を学びます。この実験では、サブクエリが最初に実行され、その結果が外部クエリによって比較に使用されることを強調しています。

最初のステップでは、mysql クライアントを使用して MySQL サーバーに接続し、labdb という名前のデータベースを作成し、customers と orders の 2 つのテーブルをセットアップします。このセットアップは、後続のステップでサブクエリを練習するための基礎を提供します。