演習：複数テーブルクエリ総合
演習：複数テーブルクエリ総合
📋 プロジェクト要件
このセクションでは、ECサイト注文管理システム のシナリオを使用して、これまでに学んだ複数テーブルクエリのスキルを総合的に応用します。
データベーステーブル構造
統一された4テーブルのデータベースを使用します：
SQL
-- 部門テーブル
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL,
city VARCHAR(50),
budget DECIMAL(12,2)
);
-- 社員テーブル
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100),
salary DECIMAL(10,2),
department_id INT,
hire_date DATE,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
-- 注文テーブル
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
employee_id INT,
order_date DATE,
total_amount DECIMAL(12,2),
status VARCHAR(20),
FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);
-- 商品テーブル
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10,2),
stock INT,
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
テストデータ
SQL
-- 部門データ
INSERT INTO departments VALUES (1, '技術部', '北京', 500000);
INSERT INTO departments VALUES (2, '営業部', '上海', 300000);
INSERT INTO departments VALUES (3, 'マーケティング部', '広州', 200000);
INSERT INTO departments VALUES (4, '経理部', '深セン', 150000);
-- 社員データ
INSERT INTO employees VALUES (1, '張', '偉', 'zhangwei@co.com', 12000, 1, '2023-03-15');
INSERT INTO employees VALUES (2, '李', '娜', 'lina@co.com', 9500, 1, '2023-06-20');
INSERT INTO employees VALUES (3, '王', '強', 'wangqiang@co.com', 8000, 2, '2022-11-10');
INSERT INTO employees VALUES (4, '趙', '敏', 'zhaomin@co.com', 11000, 2, '2024-01-05');
INSERT INTO employees VALUES (5, '劉', '洋', 'liuyang@co.com', 7500, 3, '2024-04-18');
INSERT INTO employees VALUES (6, '陳', '静', 'chenjing@co.com', 9000, NULL, '2025-02-28');
-- 注文データ
INSERT INTO orders VALUES (1001, 101, 3, '2026-05-10', 2500.00, 'completed');
INSERT INTO orders VALUES (1002, 102, 3, '2026-05-15', 1800.00, 'completed');
INSERT INTO orders VALUES (1003, 103, 4, '2026-06-01', 3200.00, 'shipped');
INSERT INTO orders VALUES (1004, 101, 1, '2026-06-05', 950.00, 'pending');
INSERT INTO orders VALUES (1005, 104, 4, '2026-06-10', 4100.00, 'completed');
INSERT INTO orders VALUES (1006, 105, 3, '2026-06-15', 2800.00, 'cancelled');
-- 商品データ
INSERT INTO products VALUES (1, 'ノートパソコン', 6999.00, 50, 1);
INSERT INTO products VALUES (2, 'ワイヤレスマウス', 129.00, 200, 1);
INSERT INTO products VALUES (3, 'オフィスデスク', 899.00, 30, 2);
INSERT INTO products VALUES (4, 'プロジェクター', 3500.00, 15, 3);
INSERT INTO products VALUES (5, 'プリンター', 2200.00, 25, 1);
🏗️ クエリ演習
クエリ1：社員の注文成績レポート
要件：各社員の注文数と売上合計を表示します。注文がない社員も含めてください。
SQL
SELECT
e.employee_id,
CONCAT(e.first_name, e.last_name) AS name,
d.department_name AS department,
COUNT(o.order_id) AS order_count,
COALESCE(SUM(o.total_amount), 0) AS total_sales
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY total_sales DESC;
結果：
TEXT
employee_id | name | department | order_count | total_sales
-----------+----------+------------+-------------+------------
4 | 趙敏 | 営業部 | 2 | 7300.00
3 | 王強| 営業部 | 3 | 7100.00
1 | 張偉 | 技術部 | 1 | 950.00
2 | 李娜 | 技術部 | 0 | 0.00
5 | 劉洋 | マーケティング部 | 0 | 0.00
6 | 陳静| NULL | 0 | 0.00
コード説明：
LEFT JOIN departments— 部門テーブルを結合して部門名を取得。部門に所属していない社員も保持LEFT JOIN orders— 注文テーブルを結合。注文がない社員も保持COALESCE(SUM(...), 0)— NULLを0に変換し、空白表示を回避GROUP BY— 社員ごとにグループ化し、注文数と合計金額を集計
クエリ2：部門売上ランキング
要件：部門ごとの売上合計を計算し、ランキング付けします。
SQL
SELECT
d.department_name AS department,
d.city AS city,
COUNT(DISTINCT e.employee_id) AS employee_count,
COUNT(o.order_id) AS total_orders,
COALESCE(SUM(o.total_amount), 0) AS total_sales,
RANK() OVER (ORDER BY COALESCE(SUM(o.total_amount), 0) DESC) AS ranking
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
GROUP BY d.department_id, d.department_name, d.city
ORDER BY total_sales DESC;
コード説明：
LEFT JOINチェーン：部門 → 社員 → 注文COUNT(DISTINCT e.employee_id)— 社員数を重複除去。複数の注文による重複カウントを回避RANK()— 売上合計でランキングするウィンドウ関数COALESCE— 注文がない部門を処理し、0を表示
クエリ3：高給与社員とその注文詳細
要件：会社の平均給与を超えている社員とその注文情報を検索します。
SQL
SELECT
e.employee_id,
CONCAT(e.first_name, e.last_name) AS name,
e.salary AS salary,
o.order_id,
o.order_date AS order_date,
o.total_amount AS order_amount,
o.status AS order_status
FROM employees e
INNER JOIN orders o ON e.employee_id = o.employee_id
WHERE e.salary > (
SELECT AVG(salary) FROM employees
)
ORDER BY e.salary DESC, o.order_date;
コード説明：
- サブクエリ
(SELECT AVG(salary) FROM employees)— 会社の平均給与を計算 INNER JOIN— 注文がある高給与社員のみ保持WHERE e.salary > (...)— サブクエリの結果をフィルタ条件として使用ORDER BYはまず給与の降順でソートし、次に日付の昇順
クエリ4：クロスクエリ — 対応する部門商品がない注文
要件：社員の部門に商品がない注文を検索します。
SQL
SELECT
o.order_id,
CONCAT(e.first_name, e.last_name) AS employee_name,
d.department_name AS department,
o.total_amount AS order_amount
FROM orders o
INNER JOIN employees e ON o.employee_id = e.employee_id
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE e.department_id IS NOT NULL
AND e.department_id NOT IN (
SELECT DISTINCT department_id FROM products WHERE department_id IS NOT NULL
);
コード説明：
INNER JOIN employees— 注文と社員を結合LEFT JOIN departments— 部門名を取得NOT IN (サブクエリ)— 商品がある部門を除外- JOINとサブクエリを組み合わせた混合フィルタリング
クエリ5：UNIONを使用した多次元データの結合
要件：「注文がある社員」と「すべての部門」を結合した総合的なディレクトリを生成します。
SQL
-- 完了注文がある社員
SELECT
CONCAT(first_name, last_name) AS name,
'社員' AS type,
CAST(salary AS VARCHAR) AS details
FROM employees
WHERE employee_id IN (
SELECT DISTINCT employee_id FROM orders WHERE status = 'completed'
)
UNION ALL
-- すべての部門
SELECT
department_name,
'部門',
CONCAT('予算: ', CAST(budget AS VARCHAR))
FROM departments
ORDER BY type, name;
コード説明：
- 最初のSELECT — サブクエリを使用して完了注文がある社員をフィルタリング
- 2番目のSELECT — すべての部門を取得
UNION ALL— 2つの結果セットを結合し、すべての行を保持- データソースを区別するために定数列「type」を追加
クエリ6：総合分析 — 注文ステータス分布と社員成績
要件：各社員の注文ステータス分布を計算し、「完了」注文を処理した社員を検索します。
SQL
SELECT
CONCAT(e.first_name, e.last_name) AS employee_name,
d.department_name AS department,
SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed,
SUM(CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS pending,
SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
COUNT(o.order_id) AS total_orders
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN orders o ON e.employee_id = o.employee_id
WHERE e.employee_id IN (
SELECT DISTINCT employee_id FROM orders
)
GROUP BY e.employee_id, e.first_name, e.last_name, d.department_name
ORDER BY total_orders DESC;
コード説明：
CASE WHEN条件付き集計 — 行データを列に変換（ピボット）LEFT JOINですべての関連データを保持- サブクエリ
IN (...)で注文がない社員をフィルタリング GROUP BYで社員ごとにグループ化・集計
❓ よくある質問
質問：INNER JOINとLEFT JOINのどちらをいつ使用すべきですか？ 回答： 左テーブルのすべてのレコードを保持する必要がある場合（右テーブルに一致がない場合でも）、
LEFT JOINを使用します。両方のテーブルで一致するレコードのみが必要な場合は、INNER JOINを使用します。実際には、レポートクエリは通常LEFT JOINを使用し、データ検証にはINNER JOINを使用します。
質問：サブクエリとJOINのどちらのパフォーマンスが良いですか？ 回答： 最新のデータベースオプティマイザーは通常、サブクエリをJOINに変換して実行します。簡単な関連付けには
JOINがより直感的です。存在チェック（「存在するか」）には、EXISTSやINサブクエリがより意味的に適しています。パフォーマンスの差異はEXPLAINで検証してください。
質問：複雑な複数テーブルクエリをどのようにデバッグしますか？ 回答： 段階的に構築します。最も内側のサブクエリから始め、結果が正しいことを確認してから、層ごとにラップします。CTE（
WITH句）を使用して、複雑なクエリを複数の読みやすい一時結果セットに分割することもできます。
質問：GROUP BYの後のSELECTには集計関数とグループ化されたカラムのみしか含められませんか？ 回答： はい。
SELECTの非集計カラムはすべてGROUP BYに出現する必要があります。そうしないとデータベースはエラーをスローします。これはSQL標準の要件です。
📖 まとめ
| スキル | このセクションでの応用 |
|---|---|
| INNER JOIN | 一致するリレーションシップのテーブルを関連付ける |
| LEFT JOIN | 左テーブルのすべてのレコードを保持 |
| サブクエリ（WHERE IN） | フィルタ条件として使用 |
| サブクエリ（スカラー） | 比較のために単一値を返す |
| UNION ALL | 異なる次元の結果セットを結合 |
| CASE WHEN | 条件付き集計（ピボット） |
| 集計関数 | COUNT、SUM、AVG、COALESCE |
| ウィンドウ関数 | RANK()によるランキング |
重要な演習ポイント：
- 複雑なクエリは段階的に、シンプルなものから複雑なものへと構築する
- 複数テーブルの結合を追加する前に、単一テーブルのクエリが正しいことを確認する
- NULL値を処理するために
COALESCEを使用する - クエリパフォーマンスを分析するために
EXPLAINを使用する
📝 演習
- クエリを作成してください：最も高い給与の社員の部門情報を検索します。部門名、都市、予算を含めてください。
- クエリを作成してください：都市ごとの部門数と社員総数を集計します。
LEFT JOINを使用して、社員がいない都市も表示されるようにしてください。 - クエリを作成してください：注文を処理したことがない社員を検索します。サブクエリ（
NOT IN）とLEFT JOIN ... IS NULLの両方で実装し、結果が一致するかどうかを比較してください。 - クエリを作成してください：社員を給与で3つのグループに分けます。「高（>10000）」「中（6000-10000）」「低（<6000）」。各グループの人数と平均給与を計算してください。
次のレッスン
次は 集計関数 を学びます。COUNT、SUM、AVG、MAX、MIN、そしてGROUP BYとHAVINGの高度な使い方をマスターします。
