SQL JOIN — 複数テーブル結合パターンの応用

QUESTION 1

SELF JOIN — 同一テーブルを2つの役割で結合し、階層関係を表現する

SELF JOIN自己結合組織階層エイリアス必須

前提知識 ▼

SELF JOIN（自己結合）は、同じテーブルを2つの異なるエイリアスで参照し、行同士を結合するテクニックです。
「社員と上司が同じ employees テーブルに存在し、manager_id が同テーブルの emp_id を参照する」組織階層は、その代表例です。

FROM   employees AS e   -- 「部下」側として参照
LEFT JOIN employees AS m  -- 「上司」側として参照
  ON e.manager_id = m.emp_id;

エイリアスが命： SELF JOINでは AS e（部下）と AS m（上司）のように必ず異なるエイリアスを付ける必要があります。エイリアスがないとDBはどちらのテーブルを指しているか判断できません。

問題 ▼

社員テーブル（employees）には、各社員の manager_id（直属上司の emp_id）が格納されています。

このテーブルを自己結合し、各社員名（employee）と、その直属上司の名前（manager）を一覧で取得してください。上司がいない社員（社長）も出力に含めてください。

使用テーブル

▸ employees

emp_id	name	manager_id
1	田中	NULL
2	佐藤	1
3	山田	1
4	鈴木	2

期待出力 ▼

employee	manager
田中	NULL
佐藤	田中
山田	田中
鈴木	佐藤

模範解答コード ▼

SELECT
  e.name  AS employee,  -- 部下側の名前
  m.name  AS manager    -- 上司側の名前（manager_idがNULLなら結果もNULL）

FROM employees AS e          -- 「部下」の視点でテーブルを読む
LEFT JOIN employees AS m    -- 同じテーブルを「上司」として再利用
  ON e.manager_id = m.emp_id  -- 部下のmanager_idが上司のemp_idと一致する行を結合
ORDER BY e.emp_id;

/*
  実行順序:
  1. FROM employees AS e       → 社員テーブルを「部下」として4行読み込む
  2. LEFT JOIN employees AS m  → 同テーブルを「上司」として再度読み込み、
                                  e.manager_id = m.emp_id で照合
                                  田中(manager_id=NULL)は一致する上司なし → NULLで補完
  3. SELECT e.name, m.name     → 部下名・上司名を射影して出力
  4. ORDER BY e.emp_id         → emp_id 昇順で並べ替え
*/

解説（テーブル変化・ポイント） ▼

SELECT
  e.name  AS employee,
  m.name  AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.emp_id
ORDER BY e.emp_id;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM employees AS e（部下として読み込む）employees テーブル（4行）を「部下」として読み込みます。各行の manager_id が、次の SELF JOIN で照合キーになります。

1 / 3

emp_id	name	manager_id
1	田中	NULL
2	佐藤	1
3	山田	1
4	鈴木	2

全 4行読込（e = 部下側）

学習ポイント

JOIN PATTERN

SELF JOIN（自己結合）

同一テーブルを2つの役割で同時参照
e = 部下視点 m = 上司視点

なぜ LEFT JOIN を使うか： INNER JOIN にすると、上司がいない（manager_id IS NULLの）トップ階層の社員が結果から消えてしまいます。「全社員を漏れなく出力しつつ、上司がいれば上司名も表示する」には、LEFT JOIN が必須です。

再帰的な階層への拡張： 今回は「1階層上の上司」だけを取得しましたが、PostgreSQL や MySQL 8.0 以降では WITH RECURSIVE（再帰CTE）を使うことで、何階層でも掘り下げた組織ツリー全体を1クエリで取得できます。SELF JOIN は再帰CTEの入門として理解が欠かせません。

アンチパターン

エイリアスを省略する： FROM employees JOIN employees ON ... と書くと、DBは「どちらの employees の列を参照しているのか」が曖昧になり正しく実行されません。SELF JOIN ではエイリアス（AS e / AS m）は必須構文です。

別テーブルに分離してしまう： 「上司テーブル」と「部下テーブル」を物理的に分けて管理する設計は冗長でデータ整合性も保てません。SELF JOIN を活用して1つのテーブルに正規化するのがRDBの基本思想です。

QUESTION 2

アンチJOIN — 「一度も購入していないユーザー」をLEFT JOIN + IS NULLで抽出する

LEFT JOINIS NULLアンチJOIN差集合

前提知識 ▼

「Aテーブルには存在するが、Bテーブルには存在しないレコード」を取得するパターンをアンチJOIN（Anti-JOIN）と呼びます。
LEFT JOIN で全件を残し、右テーブル側が NULL になった行だけをWHEREで絞り込む2段構えの手法です。

FROM   users AS u
LEFT JOIN purchases AS p ON u.user_id = p.user_id
WHERE  p.user_id IS NULL;   -- 右テーブルにマッチがない行だけを残す

NOT IN との使い分け： WHERE u.user_id NOT IN (SELECT user_id FROM purchases) でも同じ結果を得られますが、NOT IN は右リストに NULL が混入すると結果が空になるリスクがあります。アンチJOINパターンの方が安全で高速なため、実務では推奨されています。

問題 ▼

ユーザー一覧（users）と購入履歴（purchases）のテーブルがあります。

一度も購入をしたことがないユーザーの名前だけを取得してください。

使用テーブル

▸ users

user_id	name
1	田中
2	佐藤
3	山田

▸ purchases

purchase_id	user_id	item
1	1	商品A
2	1	商品B
3	2	商品C

期待出力 ▼

name
山田

模範解答コード ▼

SELECT
  u.name

FROM users AS u
LEFT JOIN purchases AS p
  ON u.user_id = p.user_id
WHERE p.purchase_id IS NULL;  -- 左結合の結果、右テーブル(p)がNULLになった行 = 購入実績のないユーザー

/*
  実行順序:
  1. FROM users AS u            → ユーザーテーブル（3行）を読み込む
  2. LEFT JOIN purchases AS p   → user_id で照合。田中・佐藤はマッチあり、山田はマッチなし
                                  山田の p 側列はすべて NULL で補完される
  3. WHERE p.purchase_id IS NULL→ NULLで補完された行（＝購入なしユーザー）だけを残す
                                  田中・佐藤は p.purchase_id が実値を持つためスキップされる
  4. SELECT u.name              → 残った1行（山田）の name を射影して出力
*/

解説（テーブル変化・ポイント） ▼

SELECT
  u.name
FROM users AS u
LEFT JOIN purchases AS p
  ON u.user_id = p.user_id
WHERE p.purchase_id IS NULL;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM users AS uusers テーブル（3行）を左テーブルとして読み込みます。この全行を保持しながら結合を行うのが LEFT JOIN の特性です。

1 / 4

user_id	name
1	田中
2	佐藤
3	山田

全 3行読込

学習ポイント

JOIN PATTERN

アンチJOIN（差集合の取得）

u にのみ存在する行 = 購入履歴のないユーザー
u = users p = purchases

IS NULL で絞る列の選び方： WHERE p.user_id IS NULL でも動作しますが、NOT NULL 制約のあるプライマリキー列（p.purchase_id）を使うのが最も確実です。NULL が入り得る一般カラムを使うと、「購入履歴はあるが、その列の値が本当に NULL」のケースと区別できなくなります。

実務での代表的なユースケース：「メルマガ登録済みだが、まだ1回も注文していない会員」「権限付与されていないユーザー」「レビューを書いていない購入者」など、マーケティングや運用の差分抽出で日常的に登場するパターンです。

アンチパターン

NOT IN にNULLが含まれる問題： WHERE u.user_id NOT IN (SELECT user_id FROM purchases) のサブクエリ結果に NULL が1件でも混在すると、NOT IN は常に UNKNOWN を返し、結果が1行も返らないという予期せぬ結果となります。アンチJOINパターンはこの問題を回避できる安全な書き方です。

実務コラム

PostgreSQL 9.5+ では EXCEPT 演算子（SELECT user_id FROM users EXCEPT SELECT user_id FROM purchases）でも差集合を取得できます。シンプルな差集合なら EXCEPT が読みやすい場合もありますが、他の列と一緒に取得したり追加の絞り込みが必要な場面では、アンチJOINパターンの方が柔軟性が高く汎用的です。

QUESTION 3

集計JOIN — サブクエリで部門平均を先に計算し、個人給与と比較する

INNER JOINGROUP BYサブクエリ集計との比較

前提知識 ▼

「各社員の給与が、自部門の平均より高いか低いか」を判定するには、一度集計してから個人データと結合するというアプローチが必要です。
サブクエリ（または後述のCTE）で GROUP BY 集計を先に行い、その結果テーブルをJOINすることで、個人行を残したまま集計値を横付けできます。

FROM employees AS e
INNER JOIN (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM   employees
  GROUP BY dept_id
) AS da ON e.dept_id = da.dept_id;

なぜ WHERE に AVG を書けないか： WHERE salary > AVG(salary) は構文として無効です。WHERE は集計前の各行を評価するため、集計関数を使えません。「先に集計 → 結果テーブルをJOIN」が正しい設計です。

問題 ▼

社員テーブル（employees）に給与（salary）と部門ID（dept_id）が格納されています。

各社員の名前・給与・所属部門の平均給与（avg_salary）を一覧で取得してください。

使用テーブル

▸ employees

emp_id	name	dept_id	salary
1	田中	10	500
2	佐藤	10	700
3	山田	20	600
4	鈴木	20	400

期待出力 ▼

name	salary	avg_salary
田中	500	600
佐藤	700	600
山田	600	500
鈴木	400	500

模範解答コード ▼

SELECT
  e.name,
  e.salary,
  da.avg_salary  -- サブクエリで計算した部門平均給与

FROM employees AS e
INNER JOIN (
  -- 先に部門ごとの平均給与を集計し、仮想テーブルを生成する
  SELECT
    dept_id,
    AVG(salary) AS avg_salary
  FROM   employees
  GROUP BY dept_id
) AS da                    -- "dept_avg" の略称 (派生テーブル)
  ON e.dept_id = da.dept_id
ORDER BY e.emp_id;

/*
  実行順序:
  1. サブクエリ内: FROM employees                  → GROUP BY dept_id
  2. FROM employees AS e                     → 社員テーブル
  3. INNER JOIN da ON dept_id 照合             → 各社員行に対応する部門平均を横付けする
  4. SELECT e.name, e.salary, da.avg_salary  → 必要な列を射影して出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  e.name,
  e.salary,
  da.avg_salary
FROM employees AS e
INNER JOIN (
  SELECT
    dept_id,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
) AS da
  ON e.dept_id = da.dept_id;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① サブクエリ GROUP BY ▼

SELECT dept_id, AVG(salary) AS avg_salary GROUP BY dept_idまず括弧内のサブクエリが実行されます。employees テーブルを dept_id でグループ化し、各部門の平均給与を計算します。この集計結果が「da（派生テーブル）」になります。

1 / 4

dept_id	name	salary	→ avg_salary
10	田中	500
10	佐藤	700	600.00
20	山田	600
20	鈴木	400	500.00

GROUP BY後: 2グループ → 仮想テーブル(da)が2行生成

学習ポイント

JOIN PATTERN

集計サブクエリとのINNER JOIN

先に GROUP BY で集計 → 個人行に横付け
e = 個人データ da = 部門集計

「行の粒度を揃える」という設計思想： employees（4行：個人粒度）と集計結果da（2行：部門粒度）は行数が異なります。JOIN後も「個人粒度（4行）」が維持されるのは、粒度の細かい側（e）が基準テーブルになるからです。このように異なる粒度のデータを JOIN で横付けする手法は実務SQLの基本です。

CASE式との組み合わせで「ランク付け」： 取得した avg_salary と salary を比較し、CASE WHEN e.salary > da.avg_salary THEN '平均以上' ELSE '平均以下' END と追記するだけで評価ラベルを付けられます。ダッシュボード系の集計SQLでは最頻出のパターンの一つです。

アンチパターン

WHERE句に集計関数を書く： WHERE e.salary > AVG(e.salary) は文法として無効です。WHERE は集計前の各行を評価するフェーズであり、集計関数（AVG/SUM等）はそのフェーズでは未計算です。集計値との比較が必要なら「先に集計テーブルを作ってJOIN」が正攻法です。

実務コラム

このクエリは後述のQ5で扱う CTE（WITH句）で書き換えることで、サブクエリのネストを解消してより読みやすくなります。WITH dept_avg AS (SELECT dept_id, AVG(salary) ... GROUP BY dept_id) SELECT ... FROM employees JOIN dept_avg ... という形です。サブクエリとCTEの違いは「書き方」だけで、実行計画はほぼ同等です（DBにより異なる場合あり）。

QUESTION 4

3テーブル連続LEFT JOIN — ECサイトの注文・ユーザー・商品を一気に結合する

LEFT JOIN x2連続結合EC設計NULL保持

前提知識 ▼

実務のWebアプリケーションでは、1つのクエリで3つ以上のテーブルを結合することが頻繁に発生します。
LEFT JOIN を連続して重ねる場合、常に直前までの結合結果が「左テーブル」になります。右側のテーブルにマッチがなくても行を落とさないことが、NULL保持のポイントです。

FROM         orders      AS o
LEFT JOIN    users       AS u  ON o.user_id    = u.user_id
LEFT JOIN    order_items AS oi ON o.order_id   = oi.order_id;

JOIN の順序と結果の関係： LEFT JOIN を連続する場合、FROM に指定したテーブルが「最も保護される側」です。どの後続テーブルにマッチしなくても、元の行は必ず結果に残ります。

問題 ▼

注文テーブル（orders）、ユーザーテーブル（users）、注文明細テーブル（order_items）の3つがあります。

3テーブルを結合して、注文ID・ユーザー名・商品名・数量を一覧で取得してください。商品が登録されていない注文（order_id=3）も出力に含めること。

使用テーブル

▸ orders

order_id	user_id
1	1
2	2
3	3

▸ users

user_id	name
1	田中
2	佐藤
3	山田

▸ order_items

item_id	order_id	product_name	qty
1	1	Tシャツ	2
2	1	ジーンズ	1
3	2	スニーカー	1

期待出力 ▼

order_id	name	product_name	qty
1	田中	Tシャツ	2
1	田中	ジーンズ	1
2	佐藤	スニーカー	1
3	山田	NULL	NULL

模範解答コード ▼

SELECT
  o.order_id,
  u.name,
  oi.product_name,
  oi.qty

FROM orders AS o
LEFT JOIN users AS u           -- ① 注文にユーザー情報を横付け（全注文を保持）
  ON o.user_id = u.user_id
LEFT JOIN order_items AS oi    -- ② 注文に明細を横付け（明細なし注文もNULL補完で保持）
  ON o.order_id = oi.order_id

ORDER BY o.order_id, oi.item_id;

/*
  実行順序:
  1. FROM orders AS o                 → 注文テーブル
  2. LEFT JOIN users AS u             → user_id で照合
  3. LEFT JOIN order_items AS oi      → order_id で照合
  4. SELECT ...                       → 必要な4列を射影
  5. ORDER BY o.order_id, oi.item_id  → 注文ID・明細ID順に並べ替え
  */

解説（テーブル変化・ポイント） ▼

SELECT
  o.order_id,
  u.name,
  oi.product_name,
  oi.qty
FROM orders AS o
LEFT JOIN users AS u
  ON o.user_id = u.user_id
LEFT JOIN order_items AS oi
  ON o.order_id = oi.order_id
ORDER BY o.order_id, oi.item_id;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM orders AS o注文テーブル（3行）を基点として読み込みます。この3行がすべての連続LEFT JOINの「左テーブル」の起点となります。

1 / 4

order_id	user_id
1	1
2	2
3	3

全 3行読込

学習ポイント

JOIN PATTERN

3テーブル連続LEFT JOIN

orders を基点に左結合を連鎖させる
行数は1対多の結合で変動する

「1対多」結合で行数が増える仕組み： order_id=1 の注文は order_items に2行存在するため、LEFT JOIN 後に2行に展開されます。これは正しい動作です。逆に言えば、1対多の関係を意識せずにJOINするとデータが水増しされるため、集計（SUM・COUNT等）と組み合わせる場合は特に注意が必要です。

INNER JOIN との使い分け： 「明細がない注文を対象外としてよい」ならすべて INNER JOIN にしてもOKです。ただし実務では「注文はあるが明細がまだない（カートに入れたが決済未了）」などのユースケースがあるため、LEFT JOIN で安全に全件保持しておく設計が多いです。

アンチパターン

1対多JOIN後に合計を取るとデータが膨らむ： 今回の結合結果に SUM(oi.qty) を付けると、order_id=1 の Tシャツ(2)+ジーンズ(1) が正しく3になりますが、ユーザー数など1対1の集計列があると行数が増えた分だけ値が重複加算されます。「結合してから集計」する場合は 粒度の変化 を必ず確認してください。

QUESTION 5

CTE + JOIN — WITH句で事前集計し、ユーザー情報と結合する現代的SQLパターン

WITH句(CTE)LEFT JOINCOALESCE可読性向上

前提知識 ▼

CTE（Common Table Expression / 共通テーブル式）は、WITH 名前 AS (サブクエリ) の形でSQLの冒頭に「名前付きの仮想テーブル」を定義する構文です。
複雑なサブクエリをネストさせる代わりに、上から下へ読める構造で書けるためコードの可読性が飛躍的に向上します。

WITH cte_name AS (
  SELECT ...  -- 事前集計や整形処理
)
SELECT ...
FROM   main_table
LEFT JOIN cte_name ON ...;

モダンSQLの推奨スタイル： PostgreSQL・MySQL 8.0・BigQuery・Snowflake など主要なRDBすべてでCTEが使えます。サブクエリ結合と同じ結果を、より読みやすく書けるのがCTEの最大の利点です。

問題 ▼

ユーザーテーブル（users）と注文テーブル（orders）があります。

WITH句を使ってユーザーごとの注文件数（order_count）と合計金額（total_amount）を集計し、ユーザー情報と結合して出力してください。
一度も注文していないユーザー（山田）も出力し、注文件数は 0、合計金額は NULL で表示すること。

使用テーブル

▸ users

user_id	name
1	田中
2	佐藤
3	山田

▸ orders

order_id	user_id	amount
1	1	3000
2	1	5000
3	2	2000

期待出力 ▼

name	order_count	total_amount
田中	2	8000
佐藤	1	2000
山田	0	NULL

模範解答コード ▼

-- ① CTE: ユーザーごとの注文集計テーブルを定義する
WITH order_stats AS (
  SELECT
    user_id,
    COUNT(*)      AS order_count,   -- 注文件数
    SUM(amount)    AS total_amount  -- 合計金額
  FROM   orders
  GROUP BY user_id
)

-- ② メインクエリ: users と CTE を LEFT JOIN で結合する
SELECT
  u.name,
  COALESCE(os.order_count, 0) AS order_count,  -- NULL を 0 に変換
  os.total_amount                              -- 注文なし(山田)はNULL

FROM users AS u
LEFT JOIN order_stats AS os
  ON u.user_id = os.user_id

ORDER BY u.user_id;

/*
  実行順序:
  1. CTE(order_stats)の評価
  2. FROM users AS u              → ユーザーテーブル
  3. LEFT JOIN order_stats AS os  → user_id で照合
  4. SELECT COALESCE(...)         → order_count の NULL を 0 に変換して射影
  5. ORDER BY u.user_id           → user_id 昇順で並べ替え
  */

解説（テーブル変化・ポイント） ▼

WITH order_stats AS (
  SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
)
SELECT
  u.name,
  COALESCE(os.order_count, 0) AS order_count,
  os.total_amount
FROM users AS u
LEFT JOIN order_stats AS os
  ON u.user_id = os.user_id
ORDER BY u.user_id;

LEGEND ▼

グループ化キー・集計対象

グループ分類

① WITH (CTE定義) ▼

order_stats AS (SELECT user_id, COUNT(*), SUM(amount) FROM orders GROUP BY user_id)CTE内のクエリが最初に実行されます。orders を user_id でグループ化し、注文件数と合計金額を集計した「order_stats」という名前の仮想テーブルを生成します。山田(user_id=3)はordersに行がないのでCTEには含まれません。

1 / 4

user_id	order_id	amount	→ count	→ sum
1	1	3000
1	2	5000	2	8000
2	3	2000	1	2000

CTE(order_stats)が生成: 2行（user_id=3は存在しない）

学習ポイント

JOIN PATTERN

CTE（WITH句）+ LEFT JOIN

事前集計を名前付きで定義し、上から読める構造に
WITH → FROM → JOIN → SELECT の流れ

CTEとサブクエリ（Q3）の違いは「可読性」： Q3ではサブクエリを括弧内にネストして書きましたが、同じ集計をCTEで書くとSQLの先頭に取り出せます。「まず何を準備するか」→「それをどう使うか」という処理の流れが上から下へ自然に読めるため、チームでのコードレビューや保守に圧倒的に優れています。実行結果は同等です。

COALESCE で NULL を意味のある値に変換： COALESCE(value, default) は、第1引数が NULL の場合に第2引数を返します。「注文ゼロ＝NULL」のままではアプリ側で不具合の原因になりやすいため、SQLの段階で0に変換しておくのが実務のベストプラクティスです。total_amount はNULLのまま返し、アプリ側で「-」表示するなど用途に合わせて使い分けます。

アンチパターン

CTEを過剰に定義する： CTE は1つのクエリ内に何個でも定義でき便利ですが、使わないCTEや、1行のサブクエリを全部CTEにするなど、過剰に分割すると逆に読みにくくなります。「複数箇所で参照する集計」「ネストが深くなる処理」に絞って使うのが効果的です。

LEFT JOINなのにCOALESCEを忘れる： LEFT JOIN によって NULL になった集計値をそのまま出力すると、アプリ側で「null + 1」のような意図しない演算結果となります。件数系は COALESCE(count, 0)、金額系はNULLのまま返すかアプリ判定にするかを意識的に設計してください。

実務コラム

BigQuery や Snowflake などのデータウェアハウスでは、CTEを複数チェーンさせてデータ変換パイプラインを1ファイルに記述するスタイルが主流です。WITH step1 AS (...), step2 AS (...SELECT FROM step1...), step3 AS (...) SELECT ... FROM step3 のように、変換ステップを順に名前付きで積み上げる書き方はdbt（data build tool）でも標準的に使われており、アナリティクスエンジニアリングの現場では必須スキルです。