SQLでのネストされたクエリの使用方法

チュートリアル
MySQL

紹介

構造化クエリ言語（SQL）は、関係データベース管理システム（RDBMS）でデータを管理するために使用されます。SQLで便利な機能の1つは、クエリ内にクエリを作成することであり、これはサブクエリまたはネストされたクエリとしても知られています。ネストされたクエリは、通常、括弧で囲まれた SELECT ステートメントであり、主要な SELECT 、 INSERT 、または DELETE 操作内に埋め込まれています。

このチュートリアルでは、 SELECT 、 INSERT 、および DELETE ステートメントでネストされたクエリを使用します。また、ネストされたクエリ内で集計関数を使用して、 WHERE および LIKE 句で指定したソートされたデータ値とデータ値を比較します。

前提条件

このガイドに従うには、SQLを使用する関係データベース管理システム（RDBMS）が実行されているコンピュータが必要です。このチュートリアルの手順と例は、次の環境を使用して検証されました:

  • A server running Ubuntu 20.04, with a non-root user with sudo administrative privileges and firewall enabled. Follow our Initial Server Setup with Ubuntu 20.04 to get started.
  • サーバーにMySQLをインストールしてセキュリティを確保しました。これを設定するには、Ubuntu 20.04にMySQLをインストールする方法ガイドに従ってください。このガイドでは、このガイドのステップ3で概説されているように、非ルートのMySQLユーザーも設定されていると想定しています。

注意:多くの関係データベース管理システムは、独自のSQL実装を使用しています。このチュートリアルで概説されているコマンドは、ほとんどのRDBMSで機能しますが、MySQL以外のシステムでテストすると、正確な構文や出力が異なる場合があります。

このチュートリアルでネストされたクエリを使用するためには、サンプルデータがロードされたデータベースとテーブルが必要です。準備ができていない場合は、次のMySQLへの接続とサンプルデータベースの設定セクションを読んで、データベースとテーブルの作成方法を学んでください。このチュートリアルでは、後でこのサンプルデータベースとテーブルを参照します。

MySQLへの接続とサンプルデータベースの設定

SQLデータベースがリモートサーバーで実行されている場合は、ローカルマシンからサーバーにSSHで接続します:

  1. ssh sammy@your_server_ip

次に、MySQLプロンプトを開き、sammyを自分のMySQLユーザーアカウント情報に置き換えます:

  1. mysql -u sammy -p

zooDBという名前のデータベースを作成します:

  1. CREATE DATABASE zooDB;

データベースが正常に作成された場合、次の出力を受け取ります:

Output
Query OK, 1 row affected (0.01 sec)

次のUSEステートメントを実行して、zooDBデータベースを選択します:

  1. USE zooDB;



Output
Database changed

データベースを選択した後、その内部にテーブルを作成します。このチュートリアルの例では、動物園を訪れるゲストに関する情報を保存するテーブルを作成します。このテーブルには、次の7つの列が含まれます:

  • guest_id: 動物園を訪れるゲストの値を保存し、intデータ型を使用します。また、この列はテーブルの主キーとして機能し、この列の各値がそれぞれの行の一意の識別子として機能します。
  • first_name: 各ゲストの名前を保存します。文字列の最大長は30文字で、varcharデータ型を使用します。
  • last_name: 各ゲストの姓を保存します。文字列の最大長は30文字で、varcharデータ型を再度使用します。
  • guest_type: 各ゲストの種類（大人または子供）を含みます。文字列の最大長は15文字で、varcharデータ型を使用します。
  • membership_type: 各ゲストが保持している会員タイプを表します。文字列の最大長は30文字で、varcharデータ型を使用します。
  • membership_cost: さまざまな会員タイプの費用を保存します。この列は、decimalデータ型を使用し、精度は5でスケールは2です。つまり、この列の値は5桁を持ち、小数点の右側に2桁を持つことができます。
  • total_visits: 各ゲストからの訪問総数を記録するためにintデータ型を使用します。

以下のCREATE TABLEコマンドを実行して、guestsという名前のテーブルを作成して、それぞれの列を含めます:

  1. CREATE TABLE guests (

  2. guest_id int, 

  3. first_name varchar(30),

  4. last_name varchar(30),

  5. guest_type varchar(15),

  6. membership_type varchar(30),

  7. membership_cost decimal(5,2), 

  8. total_visits int,

  9. PRIMARY KEY (guest_id)

  10. );

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

  1. INSERT INTO guests

  2. (guest_id, first_name, last_name, guest_type, membership_type, membership_cost, total_visits)

  3. VALUES

  4. (1, 'Judy', 'Hopps', 'Adult', 'Resident Premium Pass', 110.0, 168),

  5. (2, 'Nick', 'Wilde', 'Adult', 'Day Pass', 62.0, 1),

  6. (3, 'Duke', 'Weaselton', 'Adult', 'Resident Pass', 85.0, 4),

  7. (4, 'Tommy', 'Yax', 'Child', 'Youth Pass', 67.0, 30),

  8. (5, 'Lizzie', 'Yax', 'Adult', 'Guardian Pass', 209.0, 30),

  9. (6, 'Jenny', 'Bellwether', 'Adult', 'Resident Premium Pass', 110.0, 20),

  10. (7, 'Idris', 'Bogo', 'Child', 'Youth Pass', 67.0, 79),

  11. (8, 'Gideon', 'Grey', 'Child', 'Youth Pass', 67.0, 100),

  12. (9, 'Nangi', 'Reddy', 'Adult', 'Guardian Champion', 400.0, 241),

  13. (10, 'Octavia', 'Otterton', 'Adult', 'Resident Pass', 85.0, 11),

  14. (11, 'Calvin', 'Roo', 'Adult', 'Resident Premium Pass', 110.0, 173), 

  15. (12, 'Maurice', 'Big', 'Adult', 'Guardian Champion', 400.0, 2), 

  16. (13, 'J.K.', 'Lionheart', 'Child', 'Day Pass', 52.0, 1), 

  17. (14, 'Priscilla', 'Bell', 'Child', 'Day Pass', 104.0, 2),

  18. (15, 'Tommy', 'Finnick', 'Adult', 'Day Pass', 62.0, 1);



Output
Query OK, 15 rows affected (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

データを挿入したら、SQLでネストされたクエリを使用する準備が整いました。

SELECTを使用したネストされたクエリの使用

SQLでは、クエリはデータベース内のテーブルからデータを取得する操作であり、常にSELECTステートメントを含みます。ネストされたクエリは、別の操作内に埋め込まれた完全なクエリです。ネストされたクエリには通常のクエリで使用されるすべての要素が含まれ、任意の有効なクエリを別の操作内に埋め込んでネストされたクエリにすることができます。たとえば、ネストされたクエリはINSERTおよびDELETE操作内に埋め込むことができます。操作によっては、特定の操作順序に従うためにステートメントを適切な数の括弧で囲んで埋め込む必要があります。また、ネストされたクエリは、複数のコマンドを1つのクエリステートメントで実行し、複数のクエリを記述して希望の結果を返す代わりに使用されます。

ネストされたクエリをよりよく理解するために、前のステップのサンプルデータを使用して、それらがどのように役立つかを説明しましょう。例えば、guests テーブルの中で、平均よりも高い頻度で動物園を訪れたゲストをすべて見つけたいとします。次のようなクエリを使用してこの情報を見つけることができると仮定するかもしれません：

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > AVG(total_visits);

しかし、この構文を使用したクエリはエラーを返します：

Output
ERROR 1111 (HY000): Invalid use of group function

このエラーの理由は、AVG() のような集計関数が SELECT 句内で実行されない限り機能しないからです。

この情報を取得するための1つのオプションは、まずゲスト訪問の平均数を見つけるクエリを実行し、次にその値に基づいて結果を見つけるために別のクエリを実行することです。次の2つの例のように：

  1. SELECT AVG(total_visits) FROM guests;



Output
+-----------------+
| avg(total_visits) |
+-----------------+
| 57.5333 |
+-----------------+
1 row in set (0.00 sec)

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > 57.5333;



Output
+----------+---------+------------+
| first_name | last_name | total_visits |
+----------+---------+------------+
| Judy | Hopps | 168 |
| Idris | Bogo | 79 |
| Gideon | Grey | 100 |
| Nangi | Reddy | 241 |
| Calvin | Roo | 173 |
+----------+---------+------------+
5 rows in set (0.00 sec)

ただし、最初のクエリ (SELECT AVG(total_visits) FROM guests;) を第二のクエリの中にネストすることで、この同じ結果セットを単一のクエリで取得することができます。ネストされたクエリでは、必要なだけの括弧を使用して、実行したい操作を完了する必要があります。これは、ネストされたクエリが最初に実行される操作であるためです。

  1. SELECT first_name, last_name, total_visits 

  2. FROM guests 

  3. WHERE total_visits > 

  4. (SELECT AVG(total_visits) FROM guests);



Output
+------------+-----------+--------------+
| first_name | last_name | total_visits |
+------------+-----------+--------------+
| Judy       | Hopps     |          168 |
| Idris      | Bogo      |           79 |
| Gideon     | Grey      |          100 |
| Nangi      | Reddy     |          241 |
| Calvin     | Roo       |          173 |
+------------+-----------+--------------+
5 rows in set (0.00 sec)

この出力によれば、5人のゲストが平均よりも多くの訪問をしていました。この情報は、現在のメンバーが動物園を頻繁に訪れ、毎年メンバーシップパスを更新する創造的な方法を考える上で有益な洞察を提供する可能性があります。さらに、この例は、2つの別々のクエリを実行する代わりに、目的の結果を1つの完全なステートメントで取得するためにネストされたクエリを使用する価値を示しています。

INSERT

ネストされたクエリを使用すると、他のSELECTステートメントに埋め込むことに限定されるわけではありません。実際、ネストされたクエリを使用して、既存のテーブルにデータを挿入することもできます。これは、ネストされたクエリをINSERT操作内に埋め込むことで行われます。

以下に、新しいテーブルupgrade_guestsを作成するCREATE TABLEを使用して説明します。このテーブルには、6つの列が含まれます。データ型（intやvarcharなど）やそれらが保持できる最大文字数などのデータ型に注意してください。元のguestsテーブルのデータ型と一致しない場合、ネストされたクエリを使用してguestsテーブルからデータを挿入しようとするとエラーが発生し、データが正しく転送されません。次の情報を使用してテーブルを作成します：

  1. CREATE TABLE upgrade_guests (

  2. guest_id int, 

  3. first_name varchar(30),

  4. last_name varchar(30),

  5. membership_type varchar(30),

  6. membership_cost decimal(5,2),

  7. total_visits int, 

  8. PRIMARY KEY (guest_id)

  9. );

一貫性と正確性のために、このテーブルのデータ型情報のほとんどをguestsテーブルと同じに保持しています。また、新しいテーブルに不要な余分な列は削除しました。この空のテーブルが準備できたら、次のステップはテーブルに所望のデータ値を挿入することです。

この操作では、データが挿入される場所を明確にするために、新しいupgrade_guestsテーブルにINSERT INTOを書きます。次に、関連するデータ値を取得するためのSELECTステートメントと、それらがguestsテーブルから取得されていることを確認するためのFROMを書きます。

さらに、「Resident」メンバーのいずれかに15％の割引を適用するために、ネストされたクエリステートメント（membership_cost * 0.85を乗算する）に、WHERE句を使用してmembership_type列の値をソートします。そして、LIKE句を使用して、”Resident”メンバーシップのみの結果をさらに絞り込み、パーセンテージ%シンボルを単一引用符の前後に配置して、同じパターン、またはこの場合、同じ用語に続くメンバーシップを選択します。次に、次のようにクエリを記述します：

  1. INSERT INTO upgrade_guests

  2. SELECT guest_id, first_name, last_name, membership_type, 

  3. (membership_cost * 0.85), total_visits

  4. FROM guests

  5. WHERE membership_type LIKE '%resident%';



Output
Query OK, 5 rows affected, 5 warnings (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 5

新しいupgrade_guestsテーブルに5つのレコードが追加されたことを示す出力があります。作成した空のupgrade_guestsテーブルに正常にguestsテーブルから要求されたデータが転送され、ネストされたクエリとWHERE句で指定された条件であることを確認するには、次を実行します：

  1. SELECT * FROM upgrade_guests;



Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        1 | Judy       | Hopps      | Resident Premium Pass |           93.50 |          168 |
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|       11 | Calvin     | Roo        | Resident Premium Pass |           93.50 |          173 |
+----------+------------+------------+-----------------------+-----------------+--------------+
5 rows in set (0.01 sec)

この新しいupgrade_guestsテーブルからの出力によると、guestテーブルからの「Resident」関連のゲストメンバーシップ情報が正しく挿入されました。さらに、新しいmembership_costが15％の割引が適用されて再計算されました。その結果、この操作により、適切な対象顧客をセグメント化し、ターゲット化するのに役立ち、割引価格がすでにこれらの見込み顧客と共有できるようになりました。

ネストされたクエリを使用したDELETE

ネストされたクエリをDELETEステートメントと組み合わせる練習として、頻繁に訪れるゲストを削除したいとします。現在、動物園をよく訪れているメンバーにはアップグレードされたプレミアムパスの割引を促進したいだけなので、アップグレードされたプレミアムパスの割引をメンバーに焦点を当てたいとします。

DELETE FROMステートメントを使用して、この操作を開始します。この場合、upgrade_guestsテーブルからデータが削除されることが明確になります。その後、WHERE句を使用して、ネストされたクエリで指定された量よりも多いtotal_visitsをソートします。埋め込まれたネストされたクエリでは、SELECTを使用してtotal_visitsの平均、AVGを見つけます。これにより、前述のWHERE句に適切なデータ値が比較されます。最後に、その情報をguestsテーブルから取得するためにFROMを使用します。完全なクエリステートメントは次のようになります：

  1. DELETE FROM upgrade_guests

  2. WHERE total_visits >

  3. (SELECT AVG(total_visits) FROM guests);



Output
Query OK, 2 rows affected (0.00 sec)

upgrade_guestsテーブルからこれらのレコードが正常に削除されたことを確認し、total_visitsを数字順に昇順で結果を整理するためにORDER BYを使用します。

注意： 新しいテーブルからレコードを削除するためにDELETEステートメントを使用しても、元のテーブルからそれらを削除しません。すべての元のレコードが考慮されていることを確認するためにSELECT * FROM original_tableを実行できます。たとえそれらが新しいテーブルから削除されていてもです。

  1. SELECT * FROM upgrade_guests ORDER BY total_visits;



Output
+----------+------------+------------+-----------------------+-----------------+--------------+
| guest_id | first_name | last_name  | membership_type       | membership_cost | total_visits |
+----------+------------+------------+-----------------------+-----------------+--------------+
|        3 | Duke       | Weaselton  | Resident Pass         |           72.25 |            4 |
|       10 | Octavia    | Otterton   | Resident Pass         |           72.25 |           11 |
|        6 | Jenny      | Bellwether | Resident Premium Pass |           93.50 |           20 |
+----------+------------+------------+-----------------------+-----------------+--------------+
3 rows in set (0.00 sec)

この出力からわかるように、DELETEステートメントとネストされたクエリは、指定されたデータ値を削除するために正常に機能しました。このテーブルは今や、平均訪問回数よりも少ない3人のゲストの情報を保持しており、これは動物園の担当者が彼らにプレミアムパスへのアップグレードについて割引価格で連絡を取り、彼らが動物園にもっと頻繁に行くことを促進するための素晴らしい出発点です。

結論

ネストされたクエリは、別々のクエリを実行することでしか得られない非常に詳細な結果を得ることができるため、便利です。さらに、ネストされたクエリを使用してINSERTやDELETEステートメントを実行することで、データを一括で挿入または削除する別の方法が提供されます。データの整理方法について詳しく学びたい場合は、SQLの使い方シリーズをチェックしてください。

Source:
https://www.digitalocean.com/community/tutorials/how-to-use-nested-queries