JOINクエリ入門
JOINクエリ入門
銀行に行ったとき、窓口の担当者は「口座情報」と「個人情報」のテーブルを同時に確認する必要があります。別々に検索して手動で照合するのではなく、ID番号を使用して2つのテーブルを「結合」し、全体像を一度に把握します。JOIN は、複数のテーブル同士を「対話」させるSQLの魔法です。
1. コアコンセプト
JOINが必要な理由
データベースを設計する際、データの冗長性を避けるために複数のテーブルに分割します。例えば、employees テーブルには department_id のみを格納し、部門名や住所を重複させません。しかし、クエリでは完全な情報を「再構築」する必要があり、ここでJOINの出番です。
-- JOINなし：department_idのみ表示可能で、部門名は表示できない
SELECT name, department_id, salary FROM employees;
-- JOINあり：社員名と部門名が一緒に表示される
SELECT e.name, d.name AS department, e.salary
FROM employees e
JOIN departments d ON e.department_id = d.id;
テーブルのリレーションシップ型
| リレーションシップ型 | 説明 | 例 |
|---|---|---|
| 一対一 (1:1) | テーブルAの1行がテーブルBの1行に対応 | 1人のユーザーは1つのIDカードを持つ |
| 一対多 (1:N) | テーブルAの1行がテーブルBの複数行に対応 | 1つの部門には複数の社員が所属 |
| 多対多 (M:N) | テーブルAの複数行がテーブルBの複数行に対応 | 学生とコース（履修テーブルでリンク） |
サンプルデータベースでは：
departments→employees：一対多（1つの部門に複数の社員が所属）products→orders：一対多（1つの商品が複数の注文に出現可能）
外部キー
外部キーはテーブル間のリレーションシップを構築する橋渡しです。テーブルAの列がテーブルBの主キーを参照し、データの整合性を保証します。
-- employeesテーブルのdepartment_idはdepartmentsテーブルのidを指す外部キー
CREATE TABLE employees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
department_id INTEGER, -- 外部キー
salary DECIMAL(10,2),
hire_date DATE,
FOREIGN KEY (department_id) REFERENCES departments(id)
);
PRAGMA foreign_keys = ON; を実行する必要があります。
INNER JOINの構文
INNER JOIN は 両方のテーブルで一致する行のみ を返します。他方のテーブルに対応するデータがない行は除外されます。
SELECT カラム名
FROM テーブル_A
INNER JOIN テーブル_B ON テーブル_A.カラム = テーブル_B.カラム;
ON条件
ON 句は結合条件を指定します。つまり、2つのテーブル間で行を照合するためにどの列を使用するかを指定します。通常、外部キーと主キーを比較します。
-- ON条件：employees.department_id = departments.id
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
テーブルエイリアス (AS)
テーブル名が長い場合や、同名の列を区別する必要がある場合、AS を使用してテーブルにエイリアスを作成し、記述を簡潔にできます：
-- エイリアスなし：毎回フルネームで記述する必要がある
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
-- エイリアスあり：より簡潔
SELECT e.name, d.name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
2. 基本構文／使い方
INNER JOINの完全な構文
SELECT テーブル_A.カラム1, テーブル_A.カラム2, テーブル_B.カラム1
FROM テーブル_A
INNER JOIN テーブル_B ON テーブル_A.外部キー = テーブル_B.主キー
WHERE 条件
ORDER BY カラム名;
INNER キーワードは省略可能です。JOIN だけ書くと INNER JOIN と同等です。
A JOIN B ON ... JOIN C ON ... のようにチェーンするだけです。
例：社員と所属部門のクエリ（難易度⭐）
最も基本的なニーズである、各社員の所属部門名を確認します：
SELECT e.name AS employee, d.name AS department, e.salary
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
出力：
employee department salary
-------- ---------- --------
張三 技術部 15000.00
李四 技術部 18000.00
王五 営業部 12000.00
趙六 経理部 13000.00
銭七 技術部 20000.00
孫八 営業部 11000.00
呉十 経理部 14000.00
注意：周九 は表示されません。彼の department_id が NULL であるためです。INNER JOINは両方のテーブルで一致する行のみを保持します。
例：注文詳細のクエリ（3テーブル結合）（難易度⭐⭐）
実際のビジネスシナリオでは、複数のテーブルを結合することが一般的です。各注文の顧客名、商品名、単価、合計をクエリします：
SELECT o.customer_name, p.name AS product,
p.price, o.quantity,
(p.price * o.quantity) AS total
FROM orders o
INNER JOIN products p ON o.product_id = p.id
ORDER BY total DESC;
出力：
customer_name product price quantity total
------------- -------------- -------- -------- --------
小李 iPad Air 4799.00 3 14397.00
小剛 MacBook Pro 12999.00 1 12999.00
小王 iPhone 15 5999.00 2 11998.00
小明 iPhone 15 5999.00 1 5999.00
小剛 iPhone 14 4999.00 1 4999.00
小紅 Magic Keyboard 999.00 5 4995.00
小明 Apple Watch 2999.00 1 2999.00
小紅 AirPods Pro 1899.00 2 3798.00
3. よくある使用ケース
ケース1：技術部の全社員をクエリ
JOINで部門IDを見つけ、WHEREでフィルタリングします：
SELECT e.name, e.salary, e.hire_date
FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.name = '技術部';
出力：
name salary hire_date
------ -------- ----------
張三 15000.00 2023-01-15
李四 18000.00 2022-06-01
銭七 20000.00 2020-08-05
ケース2：部門ごとの社員数を集計
SELECT d.name AS department, COUNT(e.id) AS emp_count
FROM departments d
INNER JOIN employees e ON d.id = e.department_id
GROUP BY d.name;
出力：
department emp_count
---------- ---------
技術部 3
営業部 2
経理部 2
❓ よくある質問
質問：JOINとサブクエリ、どちらのパフォーマンスが良いですか？ 回答： 一般的に、JOINの方がパフォーマンスが良いです。データベースエンジンにはJOIN用の専用オプティマイザーが搭載されているためです。サブクエリは場合によっては一時テーブルを生成し、パフォーマンスがやや低下することがあります。ただし、データ量やインデックスに依存するため、
EXPLAINで実行計画を確認することをお勧めします。
質問：ONとWHEREの違いは何ですか？ 回答：
ONは結合条件で、2つのテーブルがどのようにペアリングされるかを決定します。WHEREはフィルタ条件で、どの行が表示されるかを決定します。INNER JOINでは似たような効果がありますが、LEFT JOINでは大きく異なります。ONは左テーブルの保持に影響しませんが、WHEREはNULL行をフィルタリングします。
質問：テーブルエイリアスは必須ですか？ 回答： 必須ではありませんが、強く推奨されます。テーブルエイリアスはSQLをより簡潔にし、特に多テーブル結合では可読性が大幅に向上します。
質問：外部キーがNULLの場合はどうなりますか？ 回答： INNER JOINはその行を除外します。一致する関連データが存在しないためです。これらの行を保持したい場合は、LEFT JOINを使用してください（次のレッスンで説明します）。
📖 まとめ
- データベースはデータを複数のテーブルに分割して冗長性を回避し、JOINでクエリ時に完全な情報を「再構築」します
- テーブルのリレーションシップには3種類あります：一対一、一対多、多対多。最も一般的なのは 一対多 です
- 外部キー はテーブル間の橋渡しであり、他のテーブルの主キーを参照します
- INNER JOIN は両方のテーブルで一致する行のみを返し、
ONで結合条件を指定します - テーブルエイリアス（
AS）はSQLをより簡潔にします。多テーブル結合では必ず使用しましょう
📝 演習
演習1（⭐）：すべての注文について、顧客名、商品名、商品カテゴリを表示するクエリを作成してください（orders テーブルと products テーブルの結合が必要です）。
演習2（⭐⭐）：部門ごとの平均給与を表示するクエリを作成してください。平均給与が12000を超える部門のみ表示してください（ヒント：JOIN + GROUP BY + HAVING）。
演習3（⭐⭐⭐）：各顧客の商品に対する支出合計を表示するクエリを作成してください。合計金額の降順で並べ替え、顧客名、注文数、合計金額を表示してください。
次のレッスン
👉 08-join-types - 結合タイプの詳細解説：LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN、SELF JOINを詳しく学び、適切な結合タイプを選択する戦略をマスターしましょう！
