「顧客リストと注文データを結合したいけど、注文していない顧客も表示したい」 「社員と部署のデータを繋げたいけど、部署未配属の新入社員も含めたい」 「商品マスタと在庫データを結合したいけど、在庫切れ商品も一覧に出したい」
こんな場面で活躍するのが**外部結合（OUTER JOIN）**です。
データベースを扱っていると、 「片方のテーブルにしかないデータも残したい」という場面がよくありますよね。
通常の結合（INNER JOIN）では、両方のテーブルに存在するデータしか取得できません。 でも、外部結合を使えば、存在しないデータも NULL として表示できるんです。
この記事を読めば、LEFT JOIN、RIGHT JOIN、FULL OUTER JOINの違いが完璧に理解でき、 実務で迷わず使い分けられるようになります。
図解と実例たっぷりで、一緒に学んでいきましょう！
1. 外部結合の基本概念を図で理解しよう
🔄 内部結合と外部結合の違い
まず、結合（JOIN）の基本を整理しましょう。
内部結合（INNER JOIN）：
- 両方のテーブルに存在するデータだけを取得
- マッチしないデータは結果に含まれない
- 一番厳密な結合方法
外部結合（OUTER JOIN）：
- 片方または両方のテーブルの全データを保持
- マッチしないデータも NULL として表示
- データの欠損を許容する柔軟な結合
🔄 外部結合の3つの種類
PostgreSQLの外部結合には3種類あります。
1. LEFT JOIN（左外部結合）
最も使用頻度が高い！
左側のテーブルの全レコード ＋ 右側のマッチするレコード
右側にデータがない場合は NULL
2. RIGHT JOIN（右外部結合）
LEFT JOINの逆バージョン
右側のテーブルの全レコード ＋ 左側のマッチするレコード
左側にデータがない場合は NULL
3. FULL OUTER JOIN（完全外部結合）
両方のテーブルの全レコードを保持
どちらかにしかないデータも全て表示
最も包括的な結合
🔄 ベン図で理解する結合の違い
INNER JOIN: [A ∩ B] 両方に存在するデータのみ
LEFT JOIN: [A ∪ (A ∩ B)] 左側全部 ＋ 共通部分
RIGHT JOIN: [(A ∩ B) ∪ B] 共通部分 ＋ 右側全部
FULL OUTER: [A ∪ B] 両方全部
2. LEFT JOIN（左外部結合）の使い方と実例
📌 LEFT JOINの基本構文
SELECT
カラム名
FROM
テーブル1（左側）
LEFT JOIN
テーブル2（右側）
ON
テーブル1.結合キー = テーブル2.結合キー;
📌 実例1：顧客と注文データの結合
準備：サンプルテーブルの作成
-- 顧客テーブル
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
registration_date DATE
);
-- 注文テーブル
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total_amount DECIMAL(10,2)
);
-- サンプルデータ投入
INSERT INTO customers VALUES
(1, '田中太郎', '2024-01-15'),
(2, '鈴木花子', '2024-02-20'),
(3, '佐藤次郎', '2024-03-10'),
(4, '山田美咲', '2024-04-05');
INSERT INTO orders VALUES
(101, 1, '2024-06-01', 5000),
(102, 1, '2024-06-15', 3000),
(103, 2, '2024-06-20', 7500);
-- 注意: customer_id = 3, 4 の注文はない
LEFT JOINの実行例：
-- 全顧客と注文情報を取得（注文していない顧客も含む）
SELECT
c.customer_id,
c.customer_name,
c.registration_date,
o.order_id,
o.order_date,
o.total_amount
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
ORDER BY
c.customer_id;
結果：
customer_id | customer_name | registration_date | order_id | order_date | total_amount
------------|---------------|-------------------|----------|------------|-------------
1 | 田中太郎 | 2024-01-15 | 101 | 2024-06-01 | 5000.00
1 | 田中太郎 | 2024-01-15 | 102 | 2024-06-15 | 3000.00
2 | 鈴木花子 | 2024-02-20 | 103 | 2024-06-20 | 7500.00
3 | 佐藤次郎 | 2024-03-10 | NULL | NULL | NULL
4 | 山田美咲 | 2024-04-05 | NULL | NULL | NULL
ポイント：
- 佐藤次郎と山田美咲は注文がないが、結果に含まれる
- 注文関連のカラムは NULL になる
📌 実例2：注文していない顧客だけを抽出
-- WHERE句でNULLをフィルタリング
SELECT
c.customer_id,
c.customer_name,
c.registration_date
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
WHERE
o.order_id IS NULL;
結果：
customer_id | customer_name | registration_date
------------|---------------|------------------
3 | 佐藤次郎 | 2024-03-10
4 | 山田美咲 | 2024-04-05
これで、まだ注文していない顧客をピンポイントで見つけられます！
3. RIGHT JOIN（右外部結合）の使い方と実例
📌 RIGHT JOINの基本構文
SELECT
カラム名
FROM
テーブル1（左側）
RIGHT JOIN
テーブル2（右側）
ON
テーブル1.結合キー = テーブル2.結合キー;
📌 実例：部署と社員データの結合
-- 部署テーブル
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(100),
location VARCHAR(100)
);
-- 社員テーブル
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
dept_id INT,
salary DECIMAL(10,2)
);
-- サンプルデータ
INSERT INTO departments VALUES
(10, '営業部', '東京'),
(20, '開発部', '大阪'),
(30, '人事部', '東京');
INSERT INTO employees VALUES
(1, '高橋一郎', 10, 500000),
(2, '伊藤二郎', 10, 450000),
(3, '渡辺三郎', 20, 600000),
(4, '中村四郎', NULL, 400000); -- 部署未配属
RIGHT JOINの実行：
-- 全社員と部署情報を取得（部署未配属の社員も含む）
SELECT
d.dept_name,
d.location,
e.emp_id,
e.emp_name,
e.salary
FROM
departments d
RIGHT JOIN
employees e ON d.dept_id = e.dept_id
ORDER BY
e.emp_id;
結果：
dept_name | location | emp_id | emp_name | salary
----------|----------|--------|-----------|----------
営業部 | 東京 | 1 | 高橋一郎 | 500000.00
営業部 | 東京 | 2 | 伊藤二郎 | 450000.00
開発部 | 大阪 | 3 | 渡辺三郎 | 600000.00
NULL | NULL | 4 | 中村四郎 | 400000.00
📌 LEFT JOINで書き換える方法
実は、RIGHT JOINは LEFT JOINで書き換え可能です。
-- 上記のRIGHT JOINと同じ結果
SELECT
d.dept_name,
d.location,
e.emp_id,
e.emp_name,
e.salary
FROM
employees e -- テーブルの順番を入れ替え
LEFT JOIN
departments d ON e.dept_id = d.dept_id
ORDER BY
e.emp_id;
どちらを使うべき？
- 一般的には LEFT JOIN の方が読みやすい
- チーム内で統一することが重要
4. FULL OUTER JOIN（完全外部結合）の使い方
📌 FULL OUTER JOINの基本構文
SELECT
カラム名
FROM
テーブル1
FULL OUTER JOIN
テーブル2
ON
テーブル1.結合キー = テーブル2.結合キー;
📌 実例：在庫と注文の突き合わせ
-- 商品在庫テーブル
CREATE TABLE inventory (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
stock_quantity INT
);
-- 受注テーブル
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
product_id INT,
order_quantity INT,
order_date DATE
);
-- サンプルデータ
INSERT INTO inventory VALUES
(1, 'ノートPC', 10),
(2, 'マウス', 50),
(3, 'キーボード', 30),
(4, 'モニター', 5); -- 注文なし
INSERT INTO order_items VALUES
(101, 1, 2, '2024-06-01'),
(102, 2, 5, '2024-06-02'),
(103, 3, 3, '2024-06-03'),
(104, 5, 1, '2024-06-04'); -- 商品ID:5は在庫に存在しない
FULL OUTER JOINの実行：
-- 在庫と注文の完全な突き合わせ
SELECT
COALESCE(i.product_id, oi.product_id) AS product_id,
i.product_name,
i.stock_quantity,
oi.order_item_id,
oi.order_quantity,
oi.order_date
FROM
inventory i
FULL OUTER JOIN
order_items oi ON i.product_id = oi.product_id
ORDER BY
product_id;
結果：
product_id | product_name | stock_quantity | order_item_id | order_quantity | order_date
-----------|--------------|----------------|---------------|----------------|------------
1 | ノートPC | 10 | 101 | 2 | 2024-06-01
2 | マウス | 50 | 102 | 5 | 2024-06-02
3 | キーボード | 30 | 103 | 3 | 2024-06-03
4 | モニター | 5 | NULL | NULL | NULL
5 | NULL | NULL | 104 | 1 | 2024-06-04
重要ポイント：
COALESCE関数で、NULLでない方の値を取得- 在庫にない商品の注文も検出できる（データ不整合の発見）
5. 複数テーブルの外部結合
🔗 3つ以上のテーブルを結合する
実務では、3つ以上のテーブルを結合することがよくあります。
-- 顧客、注文、商品の3テーブル結合
SELECT
c.customer_name,
o.order_id,
o.order_date,
p.product_name,
oi.quantity,
oi.unit_price
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
LEFT JOIN
order_items oi ON o.order_id = oi.order_id
LEFT JOIN
products p ON oi.product_id = p.product_id
WHERE
c.registration_date >= '2024-01-01'
ORDER BY
c.customer_name, o.order_date;
🔗 結合順序の重要性
-- パターン1: すべてLEFT JOIN
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
-- パターン2: 混在させる場合（注意が必要）
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
-- この場合、注文がない顧客は結果から消える！
ベストプラクティス：
- 基準となるテーブルから順番に結合
- LEFT JOINの連鎖を意識する
- INNER JOINを混ぜる時は要注意
6. 外部結合のパフォーマンス最適化
⚡ インデックスの活用
-- 結合キーには必ずインデックスを作成
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
-- 複合インデックスも検討
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
⚡ EXPLAINで実行計画を確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT
c.customer_name,
COUNT(o.order_id) as order_count,
SUM(o.total_amount) as total_spent
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
GROUP BY
c.customer_id, c.customer_name;
チェックポイント：
- Hash Join vs Nested Loop vs Merge Join
- インデックススキャンが使われているか
- 実行時間とバッファ使用量
⚡ 大量データでの最適化テクニック
-- 1. WHERE句は結合前に適用されるように書く
SELECT * FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
AND o.order_date >= '2024-01-01' -- ONに条件を書く
-- WHERE o.order_date >= '2024-01-01' だとLEFT JOINの意味がなくなる
-- 2. 不要なカラムは取得しない
SELECT
c.customer_id,
c.customer_name,
o.order_id -- 必要なカラムだけ
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- 3. サブクエリで事前集計
SELECT
c.*,
o_summary.order_count,
o_summary.total_amount
FROM
customers c
LEFT JOIN (
SELECT
customer_id,
COUNT(*) as order_count,
SUM(total_amount) as total_amount
FROM orders
GROUP BY customer_id
) o_summary ON c.customer_id = o_summary.customer_id;
7. よくあるミスと注意点
⚠️ WHERE句での落とし穴
-- 間違い：LEFT JOINなのに結果がINNER JOINになってしまう
SELECT * FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01'; -- NULLが除外される！
-- 正解：ONに条件を含める
SELECT * FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
AND o.order_date >= '2024-01-01';
-- または、NULLを考慮する
SELECT * FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2024-01-01' OR o.order_date IS NULL;
⚠️ 集計関数とNULLの扱い
-- COUNT(*)とCOUNT(column)の違い
SELECT
c.customer_name,
COUNT(*) as row_count, -- 顧客の行数をカウント
COUNT(o.order_id) as order_count -- 実際の注文数をカウント
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
-- SUM、AVGでNULLを0として扱う
SELECT
c.customer_name,
COALESCE(SUM(o.total_amount), 0) as total_spent,
COALESCE(AVG(o.total_amount), 0) as avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
⚠️ 重複レコードの対処
-- 1対多の結合で重複が発生する場合
-- 問題：顧客ごとの注文数と商品数が正しくカウントされない
SELECT
c.customer_name,
COUNT(DISTINCT o.order_id) as order_count, -- DISTINCTが必要
COUNT(DISTINCT p.product_id) as product_variety
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name;
8. 実践的な使用例とベストプラクティス
💼 ケース1：月次売上レポート（売上ゼロの月も表示）
-- 日付マスタを生成して完全な月次レポートを作成
WITH month_series AS (
SELECT generate_series(
'2024-01-01'::date,
'2024-12-31'::date,
'1 month'::interval
)::date AS month_start
)
SELECT
TO_CHAR(ms.month_start, 'YYYY-MM') as month,
COUNT(o.order_id) as order_count,
COALESCE(SUM(o.total_amount), 0) as monthly_sales
FROM
month_series ms
LEFT JOIN
orders o ON DATE_TRUNC('month', o.order_date) = ms.month_start
GROUP BY
ms.month_start
ORDER BY
ms.month_start;
💼 ケース2：在庫切れ商品の検出
-- 在庫と直近の売上を比較
WITH recent_sales AS (
SELECT
product_id,
SUM(quantity) as total_sold,
MAX(order_date) as last_order_date
FROM order_items
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY product_id
)
SELECT
p.product_id,
p.product_name,
COALESCE(i.stock_quantity, 0) as current_stock,
COALESCE(rs.total_sold, 0) as recent_sales_30d,
rs.last_order_date,
CASE
WHEN i.stock_quantity IS NULL OR i.stock_quantity = 0 THEN '在庫切れ'
WHEN i.stock_quantity < COALESCE(rs.total_sold, 0) * 0.5 THEN '要発注'
ELSE '在庫十分'
END as stock_status
FROM
products p
LEFT JOIN
inventory i ON p.product_id = i.product_id
LEFT JOIN
recent_sales rs ON p.product_id = rs.product_id
ORDER BY
stock_status, p.product_name;
💼 ケース3：顧客セグメンテーション
-- 顧客を購買行動で分類
WITH customer_metrics AS (
SELECT
c.customer_id,
c.customer_name,
c.registration_date,
COUNT(o.order_id) as order_count,
SUM(o.total_amount) as total_spent,
MAX(o.order_date) as last_order_date,
CURRENT_DATE - MAX(o.order_date) as days_since_last_order
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
GROUP BY
c.customer_id, c.customer_name, c.registration_date
)
SELECT
customer_name,
registration_date,
COALESCE(order_count, 0) as order_count,
COALESCE(total_spent, 0) as total_spent,
last_order_date,
CASE
WHEN order_count = 0 THEN '未購入顧客'
WHEN days_since_last_order <= 30 THEN 'アクティブ顧客'
WHEN days_since_last_order <= 90 THEN '休眠予備軍'
ELSE '休眠顧客'
END as customer_segment
FROM
customer_metrics
ORDER BY
customer_segment, total_spent DESC;
9. トラブルシューティング
❌ エラー1：「column reference is ambiguous」
-- エラーが出るクエリ
SELECT customer_id, customer_name -- どのテーブルのcustomer_id？
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- 解決：テーブルエイリアスを付ける
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
❌ エラー2：想定より多くのレコードが返される
-- 診断クエリ：重複の原因を特定
SELECT
c.customer_id,
c.customer_name,
COUNT(*) as duplicate_count
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
GROUP BY
c.customer_id, c.customer_name
HAVING
COUNT(*) > 1
ORDER BY
duplicate_count DESC;
-- 解決：DISTINCTまたは集約を使用
SELECT DISTINCT
c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
❌ パフォーマンスが遅い
-- 実行計画を確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... FROM ... LEFT JOIN ...;
-- 統計情報を更新
ANALYZE customers;
ANALYZE orders;
-- 必要に応じてVACUUM
VACUUM ANALYZE customers;
VACUUM ANALYZE orders;
10. PostgreSQL特有の便利な機能
🎯 LATERAL JOIN（相関サブクエリ結合）
-- 各顧客の最新3件の注文を取得
SELECT
c.customer_name,
latest_orders.order_id,
latest_orders.order_date,
latest_orders.total_amount
FROM
customers c
LEFT JOIN LATERAL (
SELECT
order_id,
order_date,
total_amount
FROM orders o
WHERE o.customer_id = c.customer_id
ORDER BY order_date DESC
LIMIT 3
) latest_orders ON true
ORDER BY
c.customer_name, latest_orders.order_date DESC;
🎯 配列を使った集約
-- 顧客ごとの注文IDを配列で取得
SELECT
c.customer_name,
ARRAY_AGG(o.order_id ORDER BY o.order_date) as order_ids,
ARRAY_AGG(o.total_amount ORDER BY o.order_date) as amounts
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
GROUP BY
c.customer_id, c.customer_name;
🎯 JSON形式での結果取得
-- 顧客と注文を階層的なJSONで取得
SELECT
c.customer_id,
c.customer_name,
COALESCE(
JSON_AGG(
JSON_BUILD_OBJECT(
'order_id', o.order_id,
'order_date', o.order_date,
'total_amount', o.total_amount
) ORDER BY o.order_date
) FILTER (WHERE o.order_id IS NOT NULL),
'[]'::json
) as orders
FROM
customers c
LEFT JOIN
orders o ON c.customer_id = o.customer_id
GROUP BY
c.customer_id, c.customer_name;
まとめ：外部結合をマスターして、データ分析の幅を広げよう！
PostgreSQLの外部結合、思っていたより奥が深いですよね。 でも、基本をしっかり理解すれば、複雑なデータ分析も怖くありません。
重要ポイントのおさらい：
✅ LEFT JOINが最も使用頻度が高い
- 基準テーブルの全データを保持
- 「すべての〜を表示」という要件に最適
✅ WHERE句とON句の使い分けが重要
- ONに条件：外部結合を維持
- WHEREに条件：NULLが除外される
✅ NULLの扱いに注意
- COALESCEで初期値を設定
- IS NULLで存在チェック
- 集計関数での考慮
✅ パフォーマンスは計測して改善
- インデックスは必須
- EXPLAINで実行計画確認
- 必要なカラムだけ取得
実務での使い分け：
| 用途 | 使うJOIN | 理由 |
|---|---|---|
| 顧客分析 | LEFT JOIN | 未購入顧客も含めたい |
| 在庫管理 | FULL OUTER | 両方の不整合を検出 |
| 売上レポート | LEFT JOIN | ゼロ売上も表示必要 |
| マスタ結合 | INNER JOIN | 正確なデータのみ |
外部結合を使いこなせるようになると、 データの「見えない部分」が見えてきます。
存在しないデータこそが、重要な発見につながることも多いんです。
🚀 次のステップ
今すぐ試すべきこと：
- 自分のデータベースでLEFT JOINを実行
- NULLレコードの確認
- EXPLAINで実行計画をチェック
スキルアップのために：
- LATERAL JOINにチャレンジ
- ウィンドウ関数と組み合わせる
- CTEで複雑なクエリを整理
この記事が、あなたのPostgreSQLスキル向上に役立つことを願っています！ 外部結合をマスターして、より深いデータ分析を実現しましょう！
コメント