【SQL実践ドリル 第3回】テーブル結合 (JOIN) ― 複数テーブルからデータを取得する

はじめに

【SQL実践ドリル】 シリーズ第3回のテーマは テーブル結合 (JOIN) です。

リレーショナルデータベースでは、データを複数のテーブルに分割して格納します。「社員の名前と部署名を一覧で見たい」「注文ごとの合計金額を知りたい」といった要件を満たすには、テーブルどうしを結合する必要があります。

JOIN はSQLで最も重要かつ実務で頻出する操作です。しっかりマスターしましょう。

対象読者

• SELECT の基本（第1回）と集計関数（第2回）を理解している方
• 複数テーブルのデータを組み合わせて取得する方法を学びたい方
• MySQL を使っている方（PostgreSQL との差異は都度補足します）

難易度の目安

マーク	レベル	説明
⭐	基本	2テーブルの基本的な結合
⭐⭐	応用	LEFT JOIN、自己結合、3テーブル結合
⭐⭐⭐	チャレンジ	JOIN + 集計を組み合わせた実践問題

---

サンプルデータベース

第1回・第2回と同じデータベースを使います。まだ作成していない方は、以下のSQL文を実行してください。

-- 部署テーブル
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(50)
);

INSERT INTO departments VALUES
(1, '営業部', '東京'),
(2, '開発部', '大阪'),
(3, '人事部', '東京'),
(4, '経理部', '名古屋'),
(5, 'マーケティング部', '福岡');

-- 社員テーブル
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    hire_date DATE NOT NULL,
    salary INT NOT NULL,
    manager_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES
(1, '田中太郎', 1, '2020-04-01', 350000, NULL),
(2, '佐藤花子', 2, '2019-07-15', 420000, 1),
(3, '鈴木一郎', 1, '2021-01-10', 300000, 1),
(4, '高橋美咲', 3, '2018-09-01', 380000, NULL),
(5, '伊藤健太', 2, '2022-03-20', 450000, 2),
(6, '渡辺さくら', 2, '2023-06-01', 280000, 2),
(7, '山本大輔', 4, '2020-11-15', 330000, 4),
(8, '中村真理', 1, '2021-08-01', 310000, 1),
(9, '小林誠', 3, '2024-01-15', 270000, 4),
(10, '加藤優子', NULL, '2023-10-01', 290000, NULL);

-- 顧客テーブル
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    prefecture VARCHAR(20),
    created_at DATE NOT NULL
);

INSERT INTO customers VALUES
(1, '株式会社ABC', '[e-mail]', '東京都', '2023-01-15'),
(2, 'DEFコーポレーション', '[e-mail]', '大阪府', '2023-03-20'),
(3, 'GHI商事', '[e-mail]', '東京都', '2023-06-10'),
(4, 'JKLテクノロジー', NULL, '愛知県', '2024-01-05'),
(5, 'MNOサービス', '[e-mail]', '福岡県', '2024-04-20');

-- 商品テーブル
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price INT NOT NULL,
    stock INT NOT NULL DEFAULT 0
);

INSERT INTO products VALUES
(1, 'ノートPC Pro', 'パソコン', 198000, 50),
(2, 'ワイヤレスマウス', '周辺機器', 3500, 200),
(3, 'USBハブ 7ポート', '周辺機器', 4800, 150),
(4, '4Kモニター 27インチ', 'モニター', 45000, 30),
(5, 'メカニカルキーボード', '周辺機器', 12000, 80),
(6, 'ノートPC Light', 'パソコン', 89000, 100),
(7, 'Webカメラ HD', '周辺機器', 6500, 120),
(8, 'ゲーミングモニター', 'モニター', 65000, 20);

-- 注文テーブル
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    employee_id INT NOT NULL,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

INSERT INTO orders VALUES
(1, 1, 1, '2024-07-01'),
(2, 2, 3, '2024-07-05'),
(3, 1, 1, '2024-07-10'),
(4, 3, 8, '2024-08-01'),
(5, 4, 3, '2024-08-15'),
(6, 2, 1, '2024-09-01'),
(7, 5, 8, '2024-09-10'),
(8, 1, 3, '2024-10-01'),
(9, 3, 1, '2024-10-15'),
(10, 4, 8, '2024-11-01');

-- 注文明細テーブル
CREATE TABLE order_details (
    detail_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO order_details VALUES
(1, 1, 1, 2),
(2, 1, 2, 5),
(3, 2, 4, 1),
(4, 2, 5, 3),
(5, 3, 6, 1),
(6, 3, 3, 2),
(7, 4, 1, 1),
(8, 4, 7, 4),
(9, 5, 2, 10),
(10, 5, 5, 2),
(11, 6, 8, 1),
(12, 6, 2, 3),
(13, 7, 6, 2),
(14, 7, 3, 5),
(15, 8, 1, 1),
(16, 8, 4, 2),
(17, 9, 5, 1),
(18, 9, 7, 3),
(19, 10, 6, 3),
(20, 10, 2, 8);

---

JOIN の基本概念

JOIN とは

JOIN は、2つ以上のテーブルを共通の列（通常は外部キーと主キー）を使って結合する操作です。

JOIN の種類

テーブルA          テーブルB
+----+------+     +----+--------+
| id | name |     | id | dept   |
+----+------+     +----+--------+
|  1 | 太郎 |     |  1 | 営業部 |
|  2 | 花子 |     |  3 | 人事部 |
|  3 | 一郎 |     |  4 | 経理部 |
+----+------+     +----+--------+

INNER JOIN（内部結合）

両方のテーブルに一致するデータがある行だけを返します。

INNER JOIN の結果:
+----+------+--------+
| id | name | dept   |
+----+------+--------+
|  1 | 太郎 | 営業部 |  ← 両方に id=1 がある
|  3 | 一郎 | 人事部 |  ← 両方に id=3 がある
+----+------+--------+
※ id=2 の花子（Bに無い）と id=4 の経理部（Aに無い）は除外される

SELECT A.id, A.name, B.dept
FROM テーブルA A
    INNER JOIN テーブルB B ON A.id = B.id;

LEFT JOIN（左外部結合）

左のテーブルの全行を保持し、右のテーブルに一致がない場合は NULL を入れます。

LEFT JOIN の結果:
+----+------+--------+
| id | name | dept   |
+----+------+--------+
|  1 | 太郎 | 営業部 |
|  2 | 花子 | NULL   |  ← テーブルBに一致なし → NULLで埋められる
|  3 | 一郎 | 人事部 |
+----+------+--------+
※ 左のテーブルAの全行が保持される

SELECT A.id, A.name, B.dept
FROM テーブルA A
    LEFT JOIN テーブルB B ON A.id = B.id;

RIGHT JOIN（右外部結合）

右のテーブルの全行を保持し、左のテーブルに一致がない場合は NULL を入れます。

RIGHT JOIN の結果:
+------+------+--------+
| id   | name | dept   |
+------+------+--------+
|    1 | 太郎 | 営業部 |
|    3 | 一郎 | 人事部 |
| NULL | NULL | 経理部 |  ← テーブルAに一致なし → NULLで埋められる
+------+------+--------+
※ 右のテーブルBの全行が保持される

SELECT A.id, A.name, B.dept
FROM テーブルA A
    RIGHT JOIN テーブルB B ON A.id = B.id;

実務上の注意: RIGHT JOIN は LEFT JOIN のテーブル順を入れ替えれば同じ結果が得られるため、実務では LEFT JOIN が好まれます。可読性の観点からも LEFT JOIN に統一するチームが多いです。

CROSS JOIN（直積・交差結合）

両テーブルの全ての組み合わせを返します。結果の行数は「左の行数 x 右の行数」です。

SELECT A.name, B.dept
FROM テーブルA A
    CROSS JOIN テーブルB B;
-- 結果: 3 x 3 = 9行

CROSS JOIN は ON 句を指定しません。通常の業務ではあまり使いませんが、カレンダーテーブルの生成などで活用されることがあります。

自己結合（Self Join）

同じテーブルどうしを結合します。社員とマネージャーの関係のように、同一テーブル内で参照関係がある場合に使います。

-- 社員テーブルを2回参照し、manager_id で結合
SELECT
    e.name AS 社員名,
    m.name AS マネージャー名
FROM employees e
    LEFT JOIN employees m ON e.manager_id = m.employee_id;

複数テーブルの結合

3つ以上のテーブルを連鎖的に結合できます。

-- orders → order_details → products を連鎖結合
SELECT o.order_id, p.product_name, od.quantity
FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id;

JOIN の ON 句と WHERE 句の違い

• ON 句: テーブル結合の条件（どの行とどの行をつなぐか）
• WHERE 句: 結合後の結果に対する絞り込み条件

INNER JOIN では ON と WHERE の結果は同じですが、LEFT JOIN では違いがあります。

-- LEFT JOIN + WHERE: 結合した後に絞り込む（左テーブルの行が消える可能性あり）
SELECT e.name, d.department_name
FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.location = '東京';

-- LEFT JOIN + ON に条件追加: 結合条件として使う（左テーブルの全行は保持される）
SELECT e.name, d.department_name
FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id
        AND d.location = '東京';

---

問題

問題 1 ⭐（基本）：社員名と部署名を一覧表示（INNER JOIN）

社員テーブル (employees) と部署テーブル (departments) を結合し、各社員の name（社員名）と department_name（部署名）を一覧で表示してください。部署に所属していない社員は除外してかまいません。

期待結果:

+----------------+-------------------+
| name           | department_name   |
+----------------+-------------------+
| 田中太郎       | 営業部            |
| 佐藤花子       | 開発部            |
| 鈴木一郎       | 営業部            |
| 高橋美咲       | 人事部            |
| 伊藤健太       | 開発部            |
| 渡辺さくら     | 開発部            |
| 山本大輔       | 経理部            |
| 中村真理       | 営業部            |
| 小林誠         | 人事部            |
+----------------+-------------------+
9 rows in set

検算: 加藤優子は department_id = NULL のため INNER JOIN では除外されます。残り9名が各部署に対応付けられます。

模範解答

SELECT e.name, d.department_name
FROM employees e
    INNER JOIN departments d ON e.department_id = d.department_id;

解説: INNER JOIN は両方のテーブルに一致するデータがある行だけを返します。加藤優子さんは department_id が NULL のため、departments テーブルとの結合条件 e.department_id = d.department_id が成立せず、結果から除外されます。

テーブル別名（e, d）を使うことで、クエリを簡潔に書けます。INNER JOIN の INNER は省略して JOIN と書くこともできます。

---

問題 2 ⭐（基本）：注文一覧に顧客名を付けて表示

注文テーブル (orders) と顧客テーブル (customers) を結合し、order_id（注文ID）、customer_name（顧客名）、order_date（注文日）を注文日の昇順で表示してください。

期待結果:

+----------+-------------------------+------------+
| order_id | customer_name           | order_date |
+----------+-------------------------+------------+
|        1 | 株式会社ABC             | 2024-07-01 |
|        2 | DEFコーポレーション     | 2024-07-05 |
|        3 | 株式会社ABC             | 2024-07-10 |
|        4 | GHI商事                 | 2024-08-01 |
|        5 | JKLテクノロジー         | 2024-08-15 |
|        6 | DEFコーポレーション     | 2024-09-01 |
|        7 | MNOサービス             | 2024-09-10 |
|        8 | 株式会社ABC             | 2024-10-01 |
|        9 | GHI商事                 | 2024-10-15 |
|       10 | JKLテクノロジー         | 2024-11-01 |
+----------+-------------------------+------------+
10 rows in set

模範解答

SELECT o.order_id, c.customer_name, o.order_date
FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date ASC;

解説: orders テーブルの customer_id と customers テーブルの customer_id を結合条件にします。全ての注文に対応する顧客が存在するため、INNER JOIN で全10件が返されます。

---

問題 3 ⭐⭐（応用）：部署に所属していない社員も含めた一覧（LEFT JOIN）

社員テーブルと部署テーブルを LEFT JOIN で結合し、全社員の employee_id（社員ID）、name（社員名）、department_name（部署名）を表示してください。部署に所属していない社員の department_name は NULL で表示されます。

期待結果:

+-------------+----------------+-------------------+
| employee_id | name           | department_name   |
+-------------+----------------+-------------------+
|           1 | 田中太郎       | 営業部            |
|           2 | 佐藤花子       | 開発部            |
|           3 | 鈴木一郎       | 営業部            |
|           4 | 高橋美咲       | 人事部            |
|           5 | 伊藤健太       | 開発部            |
|           6 | 渡辺さくら     | 開発部            |
|           7 | 山本大輔       | 経理部            |
|           8 | 中村真理       | 営業部            |
|           9 | 小林誠         | 人事部            |
|          10 | 加藤優子       | NULL              |
+-------------+----------------+-------------------+
10 rows in set

検算: 加藤優子（employee_id=10）は department_id = NULL のため、departments テーブルに一致する行がありません。LEFT JOIN により社員の行は保持され、department_name は NULL になります。

模範解答

SELECT e.employee_id, e.name, d.department_name
FROM employees e
    LEFT JOIN departments d ON e.department_id = d.department_id;

解説: LEFT JOIN は左のテーブル（employees）の全行を保持します。右のテーブル（departments）に一致がない行は、右テーブルの列が NULL で埋められます。

問題1の INNER JOIN との違い:

• INNER JOIN: 加藤優子が除外される（9行）
• LEFT JOIN: 加藤優子も含まれる（10行、department_name は NULL）

「全データを漏れなく表示したい」場合は LEFT JOIN を使います。

---

問題 4 ⭐⭐（応用）：注文がない顧客を見つける（LEFT JOIN + IS NULL）

顧客テーブルの全顧客について、注文がない顧客を見つけてください。customer_name（顧客名）を表示してください。LEFT JOIN と IS NULL を使って解いてください。

ヒント: customers を左に、orders を右にして LEFT JOIN し、orders 側の列が NULL の行を探します。

期待結果:

Empty set (0.00 sec)

検算:

• customer_id=1（株式会社ABC）: 注文1, 3, 8 がある
• customer_id=2（DEFコーポレーション）: 注文2, 6 がある
• customer_id=3（GHI商事）: 注文4, 9 がある
• customer_id=4（JKLテクノロジー）: 注文5, 10 がある
• customer_id=5（MNOサービス）: 注文7 がある

全ての顧客に注文が存在するため、結果は0件です。

模範解答

SELECT c.customer_name
FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

解説: このパターンは anti-join（反結合） と呼ばれ、「結合相手がいない行」を見つける頻出テクニックです。

仕組み:

1. LEFT JOIN で全顧客を保持する
2. 注文がある顧客は orders の列に値が入る
3. 注文がない顧客は orders の列が全て NULL になる
4. WHERE o.order_id IS NULL で注文がない顧客だけを抽出する

今回のデータでは全顧客に注文があるため結果は0件ですが、実務では「まだ注文がない新規顧客」を見つけるために非常によく使うパターンです。

同じ結果は NOT EXISTS サブクエリでも取得できます。

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

---

問題 5 ⭐⭐（応用）：注文明細に商品名と単価を付けて表示（3テーブル結合）

注文テーブル (orders)、注文明細テーブル (order_details)、商品テーブル (products) を結合し、以下を表示してください。order_id（注文ID）、product_name（商品名）、price（単価）、quantity（数量）。注文ID 1 と 2 の明細だけに絞り込み、order_id の昇順、detail_id の昇順で並べてください。

期待結果:

+----------+--------------------------+--------+----------+
| order_id | product_name             | price  | quantity |
+----------+--------------------------+--------+----------+
|        1 | ノートPC Pro             | 198000 |        2 |
|        1 | ワイヤレスマウス         |   3500 |        5 |
|        2 | 4Kモニター 27インチ      |  45000 |        1 |
|        2 | メカニカルキーボード     |  12000 |        3 |
+----------+--------------------------+--------+----------+
4 rows in set

検算:

• 注文1の明細: detail_id=1 (product_id=1, qty=2), detail_id=2 (product_id=2, qty=5)
  • product_id=1: ノートPC Pro, 198000
  • product_id=2: ワイヤレスマウス, 3500
• 注文2の明細: detail_id=3 (product_id=4, qty=1), detail_id=4 (product_id=5, qty=3)
  • product_id=4: 4Kモニター 27インチ, 45000
  • product_id=5: メカニカルキーボード, 12000

模範解答

SELECT o.order_id, p.product_name, p.price, od.quantity
FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
WHERE o.order_id IN (1, 2)
ORDER BY o.order_id ASC, od.detail_id ASC;

解説: 3つのテーブルを連鎖的に結合しています。

orders ─── order_details ─── products
  │  order_id で結合  │  product_id で結合

結合は左から右へ順に処理されます。

1. orders と order_details を order_id で結合
2. その結果と products を product_id で結合

WHERE o.order_id IN (1, 2) で注文1と2に絞り込んでいます。

---

問題 6 ⭐⭐（応用）：社員とそのマネージャー名を表示（自己結合）

社員テーブルを自己結合して、各社員の name（社員名）とその manager_id に対応するマネージャーの名前を表示してください。マネージャーがいない社員も含め、全社員を表示してください。列名はそれぞれ 社員名 と マネージャー名 としてください。

期待結果:

+----------------+------------------+
| 社員名         | マネージャー名   |
+----------------+------------------+
| 田中太郎       | NULL             |
| 佐藤花子       | 田中太郎         |
| 鈴木一郎       | 田中太郎         |
| 高橋美咲       | NULL             |
| 伊藤健太       | 佐藤花子         |
| 渡辺さくら     | 佐藤花子         |
| 山本大輔       | 高橋美咲         |
| 中村真理       | 田中太郎         |
| 小林誠         | 高橋美咲         |
| 加藤優子       | NULL             |
+----------------+------------------+
10 rows in set

検算:

• 田中太郎 (manager_id=NULL) → マネージャーなし
• 佐藤花子 (manager_id=1) → 田中太郎
• 鈴木一郎 (manager_id=1) → 田中太郎
• 高橋美咲 (manager_id=NULL) → マネージャーなし
• 伊藤健太 (manager_id=2) → 佐藤花子
• 渡辺さくら (manager_id=2) → 佐藤花子
• 山本大輔 (manager_id=4) → 高橋美咲
• 中村真理 (manager_id=1) → 田中太郎
• 小林誠 (manager_id=4) → 高橋美咲
• 加藤優子 (manager_id=NULL) → マネージャーなし

模範解答

SELECT
    e.name AS 社員名,
    m.name AS マネージャー名
FROM employees e
    LEFT JOIN employees m ON e.manager_id = m.employee_id;

解説: 自己結合（Self Join） は、同じテーブルを異なる別名で2回参照して結合する手法です。

employees e（社員として参照）
    │
    │ e.manager_id = m.employee_id
    │
employees m（マネージャーとして参照）

• e は「社員」としての employees テーブル
• m は「マネージャー」としての employees テーブル
• LEFT JOIN を使うことで、マネージャーがいない社員（manager_id = NULL）も結果に含まれます

INNER JOIN にすると、マネージャーがいない田中太郎、高橋美咲、加藤優子の3名が除外されてしまいます。

---

問題 7 ⭐⭐⭐（チャレンジ）：注文ごとの合計金額

注文テーブル (orders)、注文明細テーブル (order_details)、商品テーブル (products) を結合し、注文ごとの合計金額を求めてください。order_id（注文ID）、order_date（注文日）、合計金額を表示し、合計金額の降順で並べてください。列名は 合計金額 としてください。

期待結果:

+----------+------------+----------+
| order_id | order_date | 合計金額 |
+----------+------------+----------+
|        1 | 2024-07-01 |   413500 |
|       10 | 2024-11-01 |   295000 |
|        8 | 2024-10-01 |   288000 |
|        4 | 2024-08-01 |   224000 |
|        7 | 2024-09-10 |   202000 |
|        3 | 2024-07-10 |    98600 |
|        2 | 2024-07-05 |    81000 |
|        6 | 2024-09-01 |    75500 |
|        5 | 2024-08-15 |    59000 |
|        9 | 2024-10-15 |    31500 |
+----------+------------+----------+
10 rows in set

検算:

• 注文1: 2 x 198000 + 5 x 3500 = 396000 + 17500 = 413500
• 注文2: 1 x 45000 + 3 x 12000 = 45000 + 36000 = 81000
• 注文3: 1 x 89000 + 2 x 4800 = 89000 + 9600 = 98600
• 注文4: 1 x 198000 + 4 x 6500 = 198000 + 26000 = 224000
• 注文5: 10 x 3500 + 2 x 12000 = 35000 + 24000 = 59000
• 注文6: 1 x 65000 + 3 x 3500 = 65000 + 10500 = 75500
• 注文7: 2 x 89000 + 5 x 4800 = 178000 + 24000 = 202000
• 注文8: 1 x 198000 + 2 x 45000 = 198000 + 90000 = 288000
• 注文9: 1 x 12000 + 3 x 6500 = 12000 + 19500 = 31500
• 注文10: 3 x 89000 + 8 x 3500 = 267000 + 28000 = 295000

模範解答

SELECT
    o.order_id,
    o.order_date,
    SUM(od.quantity * p.price) AS 合計金額
FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
GROUP BY o.order_id, o.order_date
ORDER BY 合計金額 DESC;

解説: JOIN と集計関数の組み合わせは実務で非常に頻出します。

処理の流れ:

1. orders → order_details → products を結合（注文明細の各行に商品の単価を付ける）
2. 結合後、各行で quantity * price（数量 x 単価）が計算可能になる
3. GROUP BY o.order_id で注文ごとにグループ化
4. SUM(od.quantity * p.price) で注文ごとの合計金額を算出

GROUP BY には order_id と order_date の両方を含めています。order_id が主キーであるため order_date は関数従属しますが、ONLY_FULL_GROUP_BY モードでは明示的に含める必要があります。

---

問題 8 ⭐⭐⭐（チャレンジ）：担当者別の売上合計ランキング

注文の担当社員ごとの売上合計金額を求めてください。社員テーブル (employees)、注文テーブル (orders)、注文明細テーブル (order_details)、商品テーブル (products) を結合し、name（担当者名）と 売上合計 を表示してください。売上合計の降順で並べてください。

期待結果:

+------------+----------+
| name       | 売上合計 |
+------------+----------+
| 中村真理   |   721000 |
| 田中太郎   |   619100 |
| 鈴木一郎   |   428000 |
+------------+----------+
3 rows in set

検算:

• 田中太郎 (employee_id=1) の担当注文: 注文1, 3, 6, 9
  • 413500 + 98600 + 75500 + 31500 = 619100
• 鈴木一郎 (employee_id=3) の担当注文: 注文2, 5, 8
  • 81000 + 59000 + 288000 = 428000
• 中村真理 (employee_id=8) の担当注文: 注文4, 7, 10
  • 224000 + 202000 + 295000 = 721000

模範解答

SELECT
    e.name,
    SUM(od.quantity * p.price) AS 売上合計
FROM employees e
    JOIN orders o ON e.employee_id = o.employee_id
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
GROUP BY e.employee_id, e.name
ORDER BY 売上合計 DESC;

解説: 4つのテーブルを連鎖結合しています。

employees → orders → order_details → products

結合の流れ:

1. employees と orders を employee_id で結合（どの社員がどの注文を担当したか）
2. orders と order_details を order_id で結合（各注文にどの明細があるか）
3. order_details と products を product_id で結合（各明細の商品の単価を取得）

結果は3名だけです。注文を担当していない社員は INNER JOIN により結果から除外されます。全社員を表示したい場合は最初の結合を LEFT JOIN に変更します。

---

問題 9 ⭐⭐⭐（チャレンジ）：まだ一度も注文されていない商品を見つける

商品テーブル (products) の中で、注文明細テーブル (order_details) に一度も登場しない商品（まだ一度も注文されていない商品）を見つけてください。product_id（商品ID）と product_name（商品名）を表示してください。

期待結果:

Empty set (0.00 sec)

検算:

注文明細に登場する product_id を確認します。

• product_id=1: detail_id 1, 7, 15 に登場
• product_id=2: detail_id 2, 9, 12, 20 に登場
• product_id=3: detail_id 6, 14 に登場
• product_id=4: detail_id 3, 16 に登場
• product_id=5: detail_id 4, 10, 17 に登場
• product_id=6: detail_id 5, 13, 19 に登場
• product_id=7: detail_id 8, 18 に登場
• product_id=8: detail_id 11 に登場

全8商品が少なくとも1回は注文されているため、結果は0件です。

模範解答

SELECT p.product_id, p.product_name
FROM products p
    LEFT JOIN order_details od ON p.product_id = od.product_id
WHERE od.detail_id IS NULL;

解説: 問題4と同じ anti-join パターンです。

1. products を左に、order_details を右にして LEFT JOIN
2. 注文実績がある商品は order_details の列に値が入る
3. 注文実績がない商品は order_details の列が全て NULL になる
4. WHERE od.detail_id IS NULL で注文実績がない商品だけを抽出

今回のデータでは全商品に注文実績があるため結果は0件ですが、このパターンは実務で頻出します。例えば「在庫はあるが注文が来ていない商品」を見つけてマーケティング施策に活かすなどのユースケースがあります。

NOT EXISTS を使った別解:

SELECT p.product_id, p.product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_details od
    WHERE od.product_id = p.product_id
);

パフォーマンスの注意: 大量データの場合、LEFT JOIN + IS NULL と NOT EXISTS のどちらが速いかはDBMSやインデックスの状態によります。MySQLでは一般的にどちらも同等に最適化されますが、実行計画（EXPLAIN）で確認することをお勧めします。

---

問題 10 ⭐⭐⭐（チャレンジ）：全部署と所属社員数の一覧（社員0人の部署も表示）

部署テーブルの全部署について、所属する社員の数を表示してください。社員が0人の部署も含めてください。department_name（部署名）と 社員数 を表示し、社員数の降順で並べてください。

期待結果:

+-------------------+--------+
| department_name   | 社員数 |
+-------------------+--------+
| 営業部            |      3 |
| 開発部            |      3 |
| 人事部            |      2 |
| 経理部            |      1 |
| マーケティング部  |      0 |
+-------------------+--------+
5 rows in set

検算:

• 営業部 (department_id=1): 田中太郎, 鈴木一郎, 中村真理 = 3名
• 開発部 (department_id=2): 佐藤花子, 伊藤健太, 渡辺さくら = 3名
• 人事部 (department_id=3): 高橋美咲, 小林誠 = 2名
• 経理部 (department_id=4): 山本大輔 = 1名
• マーケティング部 (department_id=5): 該当社員なし = 0名

模範解答

SELECT
    d.department_name,
    COUNT(e.employee_id) AS 社員数
FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY 社員数 DESC;

解説: この問題のポイントは2つあります。

1. LEFT JOIN で全部署を保持する

departments を左テーブルにして LEFT JOIN することで、社員がいないマーケティング部も結果に含めます。INNER JOIN にすると、マーケティング部は除外されてしまいます。

2. COUNT(e.employee_id) で正しく0を数える

COUNT(*) ではなく COUNT(e.employee_id) を使うことが重要です。

• COUNT(*): 行数を数える。マーケティング部にも LEFT JOIN の結果として1行（NULLの行）が存在するため、1 になってしまう
• COUNT(e.employee_id): employee_id が NULL でない行数を数える。マーケティング部は employee_id = NULL なので 0 になる

-- NG: マーケティング部が1になってしまう
SELECT d.department_name, COUNT(*) AS 社員数
FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
-- マーケティング部 | 1  ← 不正！

-- OK: employee_id の非NULL行だけを数える
SELECT d.department_name, COUNT(e.employee_id) AS 社員数
FROM departments d
    LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;
-- マーケティング部 | 0  ← 正しい！

この「LEFT JOIN + COUNT(右テーブルの列)」は実務で非常に重要なパターンです。

---

まとめ

第3回では、テーブル結合（JOIN）の各種パターンを学びました。

結合の種類	用途
INNER JOIN	両テーブルに一致がある行のみ取得
LEFT JOIN	左テーブルの全行を保持（右テーブルに一致がなければNULL）
RIGHT JOIN	右テーブルの全行を保持（実務では LEFT JOIN で代用が多い）
CROSS JOIN	全ての組み合わせ（直積）
自己結合	同じテーブルどうしの結合（階層構造の表現など）

重要なパターン:

• anti-join: LEFT JOIN + WHERE 右テーブルの列 IS NULL で「相手がいない行」を見つける
• 複数テーブル結合: JOIN を連鎖させて3つ以上のテーブルを結合する
• JOIN + 集計: JOIN で必要なデータを揃えてから GROUP BY で集計する
• LEFT JOIN + COUNT(列名): 0件のグループも正しく数える

全3回のシリーズを通じて、SELECT の基本、集計関数、テーブル結合を学びました。これらを組み合わせることで、実務のほとんどのデータ取得・分析クエリに対応できます。

参考

• MySQL 8.0 リファレンスマニュアル - JOIN 構文
• MySQL 8.0 リファレンスマニュアル - 外部結合の簡略化
• PostgreSQL 16 ドキュメント - テーブル式 (JOIN)
• Use The Index, Luke - JOIN

---

@kotaro_ai_lab
AI活用や開発効率化について発信しています。フォローお気軽にどうぞ！