SQLite サブクエリのテクニック

はじめに

この実験（Lab）では、SQLite のサブクエリのテクニックを探求し、データの取得とフィルタリングの能力を高めます。WHERE句内でサブクエリを使用する方法、SELECTステートメントに埋め込む方法、および相関サブクエリを構築する方法を学びます。

テーブルの作成とデータの挿入

この最初のステップでは、departmentsテーブルとemployeesテーブルの 2 つのテーブルを作成し、いくつかのサンプルデータを挿入します。これにより、次のステップでサブクエリを使用する練習に必要なデータが提供されます。

まず、LabEx VM でターミナルを開きます。デフォルトのパスは/home/labex/projectです。

開始するには、company.dbという名前の SQLite データベースに接続します。データベースが存在しない場合、SQLite が自動的に作成します。次のコマンドを実行します。

sqlite3 company.db

このコマンドは、SQLite コマンドラインツールを開き、company.dbデータベースに接続します。sqlite>プロンプトが表示されます。

次に、次の SQL コマンドを使用してdepartmentsテーブルを作成します。

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT,
    location TEXT
);

このコマンドは、departmentsという名前のテーブルを、department_id、department_name、およびlocationの 3 つのカラムで作成します。department_idカラムは、このテーブルの主キー（primary key）です。

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

INSERT INTO departments (department_name, location) VALUES
('Sales', 'New York'),
('Marketing', 'Los Angeles'),
('Engineering', 'San Francisco');

このコマンドは、departmentsテーブルに 3 つの行を挿入し、3 つの異なる部署とその場所を表します。

次に、次の SQL コマンドを使用してemployeesテーブルを作成します。

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name TEXT,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

このコマンドは、employeesという名前のテーブルを、employee_id、employee_name、およびdepartment_idの 3 つのカラムで作成します。department_idカラムは、departmentsテーブルのdepartment_idカラムを参照する外部キー（foreign key）です。

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

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3);

このコマンドは、employeesテーブルに 4 つの行を挿入し、4 つの異なる従業員とその部署 ID を表します。

WHERE 句でサブクエリを使用する

このステップでは、WHERE句でサブクエリを使用して、別のクエリの出力に基づいて結果をフィルタリングする方法を学びます。

サブクエリとは、別の SQL ステートメント内にネストされたSELECTステートメントのことです。この場合、サブクエリを使用してdepartmentsテーブルからdepartment_idの値を選択し、それらの値を使用してemployeesテーブルに対するクエリの結果をフィルタリングします。

'New York'に所在する部署で働くすべての従業員を見つけましょう。これを行うには、まず'New York'にある部署のdepartment_idを見つけ、次にそのdepartment_idを持つすべての従業員を見つける必要があります。

sqlite>プロンプトで、次の SQL コマンドを入力します。

SELECT employee_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');

このコマンドは、サブクエリによって返されるdepartment_idの値のリストにdepartment_idが含まれるemployeesテーブルからemployee_nameを選択します。サブクエリは、locationが'New York'であるdepartmentsテーブルからdepartment_idを選択します。

コマンドを実行すると、次の出力が表示されるはずです。

Alice
Charlie

この出力は、ニューヨークにある Sales 部門で働く従業員の名前を示しています。

SELECT 句にサブクエリを埋め込む

このステップでは、SQL ステートメントのSELECT句内にサブクエリを埋め込んで、関連するデータを取得する方法を学びます。

SELECT句にサブクエリを埋め込むと、外側のクエリの各行に対して単一の値を取得できます。この値は、多くの場合、計算された値または別のテーブルからの関連する値です。

各従業員の名前と、その従業員の部署名を取得しましょう。sqlite>プロンプトで、次の SQL コマンドを入力します。

SELECT employee_name, (SELECT department_name FROM departments WHERE department_id = employees.department_id) AS department_name FROM employees;

このコマンドは、employeesテーブルからemployee_nameを選択し、departmentsテーブルからdepartment_nameを取得するサブクエリも含まれています。サブクエリは、employeesテーブルのdepartment_idを使用して、正しい部署を照合します。サブクエリの結果は、department_nameとしてエイリアス（alias）されます。

コマンドを実行すると、次の出力が表示されるはずです。

Alice|Sales
Bob|Marketing
Charlie|Sales
David|Engineering

この出力は、各従業員の名前と、対応する部署の名前を示しています。

相関サブクエリを構築する

このステップでは、相関サブクエリを構築する方法を学びます。相関サブクエリとは、外側のクエリのカラムを参照するサブクエリのことです。これは、サブクエリが外側のクエリの各行に対して一度実行されることを意味します。

一度実行され、その結果が外側のクエリで使用される単純なサブクエリとは異なり、相関サブクエリは、その値について外側のクエリに依存します。これらは、サブクエリ内の値を外側のクエリの現在の行の値と比較する必要がある場合に使用されます。

従業員の名前と同じ都市にある部署で働くすべての従業員を見つけましょう。これを機能させるために、まず従業員名を都市名に更新します。

UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Alice';
UPDATE employees SET employee_name = 'Los Angeles' WHERE employee_name = 'Bob';
UPDATE employees SET employee_name = 'New York' WHERE employee_name = 'Charlie';
UPDATE employees SET employee_name = 'San Francisco' WHERE employee_name = 'David';

これらのコマンドは、employeesテーブルのemployee_nameカラムを都市名に更新します。

次に、相関サブクエリを作成しましょう。

SELECT e.employee_name FROM employees e WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location = e.employee_name);

このコマンドは、サブクエリによって返されるdepartment_idの値のリストにdepartment_idが含まれるemployeesテーブル（エイリアス（alias）e）からemployee_nameを選択します。サブクエリは、departmentsテーブル（エイリアス（alias）d）から、locationが外側のクエリのemployee_nameと一致するdepartment_idを選択します。

コマンドを実行すると、次の出力が表示されるはずです。

New York
Los Angeles
San Francisco

この出力は、同じ都市にある部署で働く従業員（現在は都市名）の名前を示しています。

JOIN を使用してサブクエリの効率を評価する

このステップでは、サブクエリの効率を評価し、最適化のためにJOIN操作を使用した代替アプローチを検討する方法を学びます。

サブクエリは強力ですが、特に大規模なデータセットでは、パフォーマンスのボトルネックにつながる場合があります。多くの場合、JOIN操作を使用してサブクエリを書き換えることができ、その方が効率的な場合があります。

前のステップの相関サブクエリをJOINを使用して書き換えてみましょう。

SELECT e.employee_name FROM employees e JOIN departments d ON e.department_id = d.department_id WHERE d.location = e.employee_name;

このコマンドは、employeesテーブル（エイリアス（alias）e）からemployee_nameを選択し、department_idカラムでdepartmentsテーブル（エイリアス（alias）d）と結合（JOIN）します。次に、WHERE句は、departmentsテーブルのlocationがemployeesテーブルのemployee_nameと一致する行のみを含めるように結果をフィルタリングします。

結果を確認するには、コマンドを実行します。前のステップと同じ出力が表示されるはずです。

New York
Los Angeles
San Francisco

効率を評価するには、通常、変更の前後にEXPLAIN QUERY PLANを使用します。ただし、LabEx 環境の制限により、EXPLAIN QUERY PLANコマンドを完全に実証することはできません。重要な点は、JOIN操作は、特に大規模なデータセットの場合、相関サブクエリよりも効率的なことが多いということです。

最後に、sqlite3シェルを終了します。

.exit

これにより、bash プロンプトに戻ります。

まとめ

この実験（Lab）では、SQLite サブクエリを使用して、データの取得およびフィルタリング機能を強化する方法を学びました。WHERE句内でサブクエリを使用し、SELECTステートメントに埋め込み、相関サブクエリを構築する練習をしました。また、効率を向上させるために、JOIN操作を使用してサブクエリを書き換える方法も学びました。これらのテクニックは、SQLite でデータを操作するための強力なツールを提供します。