MySQL サブクエリとネストされた操作

はじめに

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

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

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 件行ったすべての顧客を見つけたいとします。

手順

1. MySQL サーバーへの接続：

   ターミナルを開き、以下のコマンドを実行して root ユーザーとして MySQL サーバーに接続します。

   sudo mysql -u root
   

   MySQL プロンプト mysql> が表示されるはずです。

2. データベースとテーブルの作成：

   まだデータベースとテーブルがない場合は、今すぐ作成してください。labdb という名前のデータベースと customers および orders テーブルを作成しましょう。MySQL プロンプトで以下の SQL コマンドを実行します。

   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 AUTO_INCREMENT,
       customer_id INT,
       order_date DATE,
       total_amount DECIMAL(10, 2),
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );
   

3. サンプルデータの挿入：

   テーブルにサンプルデータを挿入します。MySQL プロンプトで以下の SQL コマンドを実行します。

   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 (customer_id, order_date, total_amount) VALUES
   (1, '2023-01-15', 120.00),
   (2, '2023-02-20', 80.00),
   (1, '2023-03-10', 150.00),
   (3, '2023-04-05', 200.00),
   (2, '2023-05-12', 110.00),
   (4, '2023-06-18', 90.00);
   

4. WHERE 句にサブクエリを記述：

   次に、合計金額が 100 ドルを超える注文を行った顧客を見つけるクエリを記述します。MySQL プロンプトで以下の SQL コマンドを実行します。

   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 テーブルからすべての列を選択します。

5. 出力の確認：

   合計金額が 100 ドルを超える注文を行った顧客を示す以下の出力が表示されるはずです。

   +-------------+-------------+-----------+
   | 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)
   

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

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

EXISTS の理解

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

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

シナリオ

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

前提条件

前のステップ（「WHERE 句にサブクエリを記述する」）を完了しており、labdb データベース、customers テーブル、および orders テーブルにデータが入力されていることを確認してください。

手順

1. EXISTS を使用したクエリの記述：

   少なくとも 1 件の注文を行った顧客を見つけるクエリを記述します。MySQL プロンプトで以下の SQL コマンドを実行します。

   SELECT *
   FROM customers c
   WHERE EXISTS (
       SELECT 1
       FROM orders o
       WHERE o.customer_id = c.customer_id
   );
   

   説明：

   • 外部クエリ SELECT * FROM customers c は、エイリアス c を付けた customers テーブルからすべての列を選択します。
   • WHERE EXISTS (...) 句は、サブクエリが行を返すかどうかをチェックします。
   • サブクエリ SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id は、orders テーブルの customer_id が customers テーブルの customer_id と一致する場合に、エイリアス o を付けた orders テーブルから値 1（任意の定数値を指定できます）を選択します。
   • EXISTS 演算子は、サブクエリが少なくとも 1 行を返した場合に TRUE を返し、その顧客が少なくとも 1 件の注文を行ったことを示します。

2. 出力の確認：

   少なくとも 1 件の注文を行ったすべての顧客を示す以下の出力が表示されるはずです。

   +-------------+-------------+-----------+
   | 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)
   

3. クエリの変更（オプション）:

   注文をまったく行っていない顧客を見つけるためにクエリを変更してみましょう。これは NOT EXISTS を使用して行うことができます。MySQL プロンプトで以下の SQL コマンドを実行します。

   SELECT *
   FROM customers c
   WHERE NOT EXISTS (
       SELECT 1
       FROM orders o
       WHERE o.customer_id = c.customer_id
   );
   

4. 出力の確認：

   サンプルデータ内のすべての顧客が注文を行っているため、このクエリは空の結果セットを返すはずです。

   Empty set (0.00 sec)
   

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

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

相関サブクエリの理解

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

シナリオ

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

前提条件

前のステップを完了しており、labdb データベース、customers テーブル、および orders テーブルにデータが入力されていることを確認してください。

手順

1. 相関サブクエリの記述：

   平均注文金額よりも金額が大きい注文を行った顧客を見つけるクエリを記述します。MySQL プロンプトで以下の SQL コマンドを実行します。

   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 は、エイリアス c を付けた customers テーブルから 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) は、エイリアス o を付けた orders テーブルから値 1 を選択します。ここで：
   • o.customer_id = c.customer_id: これが相関です。サブクエリは、外部クエリの customers テーブルから customer_id を参照します。
   • o.total_amount > (SELECT AVG(total_amount) FROM orders): この条件は、注文金額が すべての 注文の平均注文金額よりも大きいかどうかをチェックします。AVG(total_amount) サブクエリは、平均注文金額を取得するために一度だけ実行される非相関サブクエリです。

2. 出力の確認：

   平均注文金額よりも金額が大きい注文を行った顧客を示す以下の出力が表示されるはずです。

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

3. 別の例：各顧客の最高注文金額を見つける

   各顧客の ID、名前、および最高注文金額を取得するには、MySQL プロンプトで以下の SQL コマンドを実行します。相関サブクエリは、各顧客の最大注文金額を個別に計算します。

   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;
   

4. 出力の確認：

   以下の出力が表示されるはずです。

   +-------------+-------------+-----------------------+
   | 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)
   

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

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

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

サブクエリのパフォーマンスは、いくつかの要因によって影響を受ける可能性があります。これには以下が含まれます。

• データサイズ： クエリに関与するテーブルのサイズ。
• サブクエリのタイプ： サブクエリが相関サブクエリか非相関サブクエリか。
• インデックス： テーブルにインデックスが存在するかどうか、およびその有効性。
• MySQL のバージョン： クエリ最適化手法は異なる可能性があるため、使用している MySQL の特定のバージョン。

シナリオ

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

前提条件

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

手順

1. orders テーブルにデータを追加:

   パフォーマンス比較をより現実的にするために、orders テーブルに大量のデータを追加しましょう。ストアドプロシージャを使用して、各顧客に対して 1000 件の注文を挿入します。MySQL プロンプトで以下の SQL コマンドを実行します。

   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 テーブルに挿入します。
   • データの挿入後、プロシージャは削除されます。

2. IN を使用したクエリ:

   少なくとも 1 件の注文を行ったすべての顧客を見つけるために、IN を使用した以下のクエリを実行します。

   SELECT *
   FROM customers
   WHERE customer_id IN (SELECT customer_id FROM orders);
   

3. EXPLAIN を使用したクエリ実行計画の分析:

   クエリを実行する前に、EXPLAIN コマンドを使用してクエリ実行計画を分析します。これにより、MySQL がクエリをどのように実行するかについての洞察が得られ、潜在的なパフォーマンスのボトルネックを特定できます。MySQL プロンプトで以下の SQL コマンドを実行します。

   EXPLAIN SELECT *
   FROM customers
   WHERE customer_id IN (SELECT customer_id FROM orders);
   

   EXPLAIN の出力は、アクセスされるテーブル、使用されるインデックス（存在する場合）、および操作が実行される順序を示します。type 列に注意してください。これは、使用される結合またはアクセス方法のタイプを示します。

4. EXISTS を使用したクエリ:

   少なくとも 1 件の注文を行ったすべての顧客を見つけるために、EXISTS を使用した以下のクエリを実行します。

   SELECT *
   FROM customers c
   WHERE EXISTS (
       SELECT 1
       FROM orders o
       WHERE o.customer_id = c.customer_id
   );
   

5. EXPLAIN を使用したクエリ実行計画の分析:

   EXPLAIN コマンドを使用して、EXISTS クエリのクエリ実行計画を分析します。MySQL プロンプトで以下の SQL コマンドを実行します。

   EXPLAIN SELECT *
   FROM customers c
   WHERE EXISTS (
       SELECT 1
       FROM orders o
       WHERE o.customer_id = c.customer_id
   );
   

   実行計画を IN クエリの実行計画と比較してください。アクセスされるテーブル、使用されるインデックス、またはアクセス方法に違いはありますか？

6. 観察結果：

   一般的に、サブクエリが大量の行を返す場合、EXISTS は IN よりもパフォーマンスが向上する傾向があります。これは、IN は外部クエリの値とサブクエリによって返されたすべての値を比較する必要があるのに対し、EXISTS は一致が見つかるとすぐに停止するためです。ただし、実際のパフォーマンスは、特定のクエリ、データ、およびデータベースシステムによって異なる場合があります。実行時間のより正確な測定値を取得するために BENCHMARK() 関数（元のドキュメントに示されているように）を使用できますが、この実験では EXPLAIN 出力の分析でクエリプランに関する十分な洞察が得られます。

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

   データベースとテーブルをクリーンアップしたい場合は、MySQL プロンプトで以下のコマンドを実行できます。

   DROP TABLE IF EXISTS orders;
   DROP TABLE IF EXISTS customers;
   DROP DATABASE IF EXISTS labdb;
   

   すべてのステップが完了したら、MySQL クライアントを終了するには次のように入力します。

   exit
   

まとめ

この実験では、SQL ステートメントの WHERE 句内でサブクエリを使用して、他のテーブルまたは同じテーブルから派生した条件に基づいてデータをフィルタリングする方法を学びました。MySQL サーバーへの接続、データベースとテーブルの作成、サンプルデータの挿入を実践しました。

orders テーブルの関連データに基づいて顧客を見つけるために、サブクエリと共に IN 演算子を使用することを検討しました。また、IN の代替として EXISTS 演算子についても学び、関連行の存在を確認するために使用することを実践しました。

さらに、外部クエリの列を参照する相関サブクエリを紹介し、平均注文額を超える注文を行った顧客を見つけるために使用しました。最後に、EXPLAIN コマンドを使用して実行計画を分析することにより、IN および EXISTS サブクエリのパフォーマンスを比較し、MySQL がこれらのクエリをどのように処理するかについての洞察を得ました。