SQLite テーブル結合：データ抽出と効率的なクエリ

はじめに

この実験（Lab）では、テーブル結合のテクニックを使って、SQLite の複数のテーブルからデータを結合する方法を学びます。INNER JOIN、LEFT JOIN、複数のテーブルの結合、および結合された結果のフィルタリングについて説明します。この実験（Lab）を終える頃には、SQLite を使用して効率的にデータを取得および結合できるようになります。

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

このステップでは、employees と departments の 2 つのテーブルを作成し、サンプルデータを投入します。これらのテーブルは、テーブル結合のテクニックを示すために、以降のステップで使用されます。

まず、ターミナルで次のコマンドを実行して、SQLite シェルを開きます。

sqlite3 /home/labex/project/company.db

このコマンドは SQLite シェルを開き、company.db という名前のデータベースファイルに接続します。ファイルが存在しない場合、SQLite はファイルを作成します。

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

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department_id INTEGER
);

このコマンドは、employees という名前のテーブルを、id、name、および department_id の 3 つの列で作成します。id 列は主キーであり、各従業員を一意に識別します。

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

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    name TEXT
);

このコマンドは、departments という名前のテーブルを、id と name の 2 つの列で作成します。id 列は主キーであり、各部署を一意に識別します。

次に、サンプルデータを employees テーブルに挿入します。

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

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

最後に、サンプルデータを departments テーブルに挿入します。

INSERT INTO departments (name) VALUES
('Sales'),
('Marketing'),
('Engineering');

このコマンドは、departments テーブルに 3 つの行を挿入します。各行は、部署とその名前を表します。

テーブルが正しく作成され、データが投入されたことを確認するには、次の SQL コマンドを実行します。

SELECT * FROM employees;

Expected Output:

1|Alice|1
2|Bob|2
3|Charlie|1
4|David|3
5|Eve|

そして：

SELECT * FROM departments;

Expected Output:

1|Sales
2|Marketing
3|Engineering

INNER JOIN クエリの実行

このステップでは、SQLite で INNER JOIN 句を使用する方法を学びます。INNER JOIN は、指定された条件に基づいて両方のテーブルに一致がある場合に、行を返します。

INNER JOIN の基本的な構文は次のとおりです。

SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

この例では、従業員名と部署名を一緒に取得します。これを行うには、次の SQL コマンドを実行します。

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

このコマンドは、employees テーブルの department_id 列が departments テーブルの id 列と一致することに基づいて、employees テーブルと departments テーブルを結合します。次に、employees テーブルから従業員の名前を選択し、departments テーブルから部署の名前を選択します。

Expected Output:

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

この出力は、従業員の名前と対応する部署名を示しています。Eve の department_id が NULL であり、一致する部署がないため、結果に含まれていないことに注意してください。INNER JOIN は、一致する行のみを返します。

オプションデータのための LEFT JOIN の使用

このステップでは、SQLite で LEFT JOIN を使用する方法を学びます。LEFT JOIN (または LEFT OUTER JOIN) は、左側のテーブルからすべての行を返し、右側のテーブルから一致する行を返します。右側のテーブルに一致するものがない場合、右側のテーブルの列には NULL 値が返されます。これは、あるテーブルからすべてのレコードを取得し、存在する場合は別のテーブルから関連データを含める場合に役立ちます。

LEFT JOIN の基本的な構文は次のとおりです。

SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;

従業員が部署に所属していない場合でも、すべての従業員とその部署名を取得するには、次の SQL コマンドを実行します。

SELECT employees.name, departments.name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

このコマンドは、employees テーブルの department_id 列が departments テーブルの id 列と一致することに基づいて、employees テーブルと departments テーブルを結合します。employees テーブルから従業員の名前を選択し、departments テーブルから部署の名前を選択します。LEFT JOIN であるため、すべての従業員がリストされます。

Expected Output:

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

Eve がリストされていますが、department_id が NULL であり、一致する部署がないため、部署名は空 (NULL) であることに注意してください。これは、LEFT JOIN が右側のテーブル (departments) に一致するものがない場合でも、左側のテーブル (employees) からすべての行を含める方法を示しています。

複数テーブルの結合

このステップでは、SQLite で複数のテーブルを結合する方法を学びます。3 つ以上のテーブルを結合するには、複数の JOIN 句を使用して、いくつかの関連テーブルからデータを結合する必要があります。

まず、部署の所在地を格納するための locations という新しいテーブルを追加しましょう。次の SQL コマンドを実行して、locations テーブルを作成します。

CREATE TABLE locations (
    id INTEGER PRIMARY KEY,
    department_id INTEGER,
    city TEXT
);

このコマンドは、id、department_id、および city の 3 つの列を持つ locations という名前のテーブルを作成します。

次に、サンプルデータを locations テーブルに挿入します。

INSERT INTO locations (department_id, city) VALUES
(1, 'New York'),
(2, 'Los Angeles'),
(3, 'Chicago');

次に、3 つのテーブルすべてを結合して、従業員名、部署名、および部署の所在地を取得しましょう。次の SQL コマンドを実行します。

SELECT employees.name, departments.name, locations.city
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id;

このクエリは、最初に employees.department_id = departments.id に基づいて employees と departments を結合します。次に、departments.id = locations.department_id に基づいて、その結果を locations テーブルと結合します。これにより、従業員を部署に、次にそれらの部署の所在地にリンクします。

Expected Output:

Alice|Sales|New York
Bob|Marketing|Los Angeles
Charlie|Sales|New York
David|Engineering|Chicago

これは、各従業員の名前、部署の名前、および部署が所在する都市を示しています。Eve は部署が割り当てられていないため、含まれていません。

結合結果のフィルタリング

このステップでは、SQLite で WHERE 句を使用して、結合されたテーブルの結果をフィルタリングする方法を学びます。フィルタリングを使用すると、結合操作後に特定の条件を満たす行のみを取得できます。

営業 (Sales) 部門で働く従業員の名前を取得するには、次の SQL コマンドを実行します。

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
WHERE departments.name = 'Sales';

このクエリは、employees テーブルと departments テーブルを結合し、部署名が 'Sales' である従業員のみを含むように結果をフィルタリングします。

Expected Output:

Alice
Charlie

これは、Alice と Charlie が営業 (Sales) 部門で働いていることを示しています。

次に、ニューヨーク (New York) にある部署で働く従業員の名前を取得しましょう。次の SQL コマンドを実行します。

SELECT employees.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id
INNER JOIN locations ON departments.id = locations.department_id
WHERE locations.city = 'New York';

このクエリは、3 つのテーブルすべてを結合し、部署の所在地がニューヨーク (New York) である従業員のみを含むように結果をフィルタリングします。

Expected Output:

Alice
Charlie

やはり、Alice と Charlie はニューヨーク (New York) にある部署にいる唯一の従業員です。

まとめ

この実験（Lab）では、テーブル結合のテクニックを使用して、SQLite で複数のテーブルからデータを結合する方法を学びました。両方のテーブルに一致がある場合にレコードを返す INNER JOIN と、左側のテーブルからすべてのレコードを返し、右側のテーブルから一致するレコードを返す LEFT JOIN について説明しました。また、複数のテーブルを結合し、WHERE 句を使用して結合された結果をフィルタリングする方法も学びました。これらのスキルにより、SQLite を使用してデータを効率的に取得および結合できるようになります。