SQL Quiz Book

SQL JOIN — 結合種類とテーブル変化の基礎

基礎JOINテーブル結合Web開発PostgreSQL対応全5問
1 / 5 · 学習中 0 / 5 問完了
QUESTION 6

SELF JOIN — 同一テーブルに2つのエイリアスを付けて「部下と上司」を1クエリで取得する

SELF JOINエイリアス階層構造組織図
前提知識 ▼

SELF JOIN（自己結合）とは、同じテーブルに異なるエイリアスを付けて2回 JOIN するテクニックです。1つのテーブルを「部下側（e）」と「上司側（m）」など2つの役割に分けて、同一テーブル内の行同士の関係を取得できます。

FROM   employees AS e         -- 部下として読み込む
INNER JOIN employees AS m   -- 同じテーブルを上司として再度 JOIN
  ON e.manager_id = m.emp_id;  -- 部下の manager_id と 上司の emp_id を照合
エイリアスが必須： SELF JOIN では同じテーブル名が2回登場するため、必ず異なるエイリアスを付けてどちらの行を参照しているかを明示します。エイリアスを省略するとSQL構文エラーになります。
問題 ▼

employees テーブルには manager_id 列があり、自分の上司の emp_id を指しています（社長は NULL）。SELF JOIN を使って全従業員の名前と上司の名前を取得してください（社長は除外）。

使用テーブル
▸ employees
emp_idnamemanager_id
1田中（社長）NULL
2佐藤1
3山田1
4鈴木2
期待出力 ▼
employeemanager
佐藤田中（社長）
山田田中（社長）
鈴木佐藤
模範解答コード ▼ 
SELECT
  e.name  AS employee,  -- 部下側 (e) の名前
  m.name  AS manager    -- 上司側 (m) の名前

FROM employees AS e         -- e: 部下として読み込む（4行）
INNER JOIN employees AS m   -- m: 上司として同じテーブルをもう一度 JOIN
  ON e.manager_id = m.emp_id -- 部下の manager_id と 上司の emp_id が一致する行のみ
ORDER BY e.emp_id;

/*
  実行順序:
  1. FROM employees AS e        → 部下として読み込む
  2. INNER JOIN employees AS m  → 上司として自己結合
  3. SELECT e.name, m.name      → 2列を射影
  */
解説（テーブル変化・ポイント） ▼
SELECT e.name AS employee, m.name AS manager FROM employees AS e INNER JOIN employees AS m ON e.manager_id = m.emp_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM employees AS eemployees テーブル（4行）を「部下（e）」として読み込みます。manager_id 列が SELF JOIN のキーになります。田中（emp_id=1）は manager_id = NULL のため、INNER JOIN 条件を満たさず次のステップで除外されます。
1 / 3
emp_idnamemanager_id
1田中（社長）NULL
2佐藤1
3山田1
4鈴木2
全 4行 読込（部下としての employees）
学習ポイント
A B
INNER JOIN (SELF JOIN)
同じテーブルの別エイリアス間で、条件に一致する行同士を結合する。
A: employees(e) / B: employees(m)
SELF JOIN の本質 — 「1つのテーブルを2役で使う」：通常の JOIN は異なるテーブル同士を結合しますが、SELF JOIN は同じテーブルに異なるエイリアス（e / m）を付けて2度登場させます。データベースエンジンは内部的に2つの異なるテーブルとして扱います。階層構造（親子・上司部下）や同一テーブル内の行同士を比較したいときに使います。
INNER JOIN なら社長（NULL）は自動除外される：ON e.manager_id = m.emp_id で照合するとき、田中の manager_id = NULL はどの値とも一致しない（NULL = 1 は UNKNOWN）ため、INNER JOIN の結果から除外されます。「社長も含めて全員を取得したい」場合は LEFT JOIN に変えると、田中の manager 列が NULL で出力されます。
アンチパターン
エイリアスを付けないとエラー：FROM employees INNER JOIN employees ON ... のようにエイリアスを省略すると、name などの列が「どちらの employees の name か」が曖昧でエラーになります。SELF JOIN では必ず両方にエイリアスを付けることが必須です。
エイリアス名に役割を示さないと混乱する：AS a, AS b のような無意味なエイリアスより、AS e（employee）, AS m（manager） のように役割を示す名前を使いましょう。コードレビュー時に「どちらが部下でどちらが上司か」が即座に分かります。
実務コラム
SELF JOIN が登場する典型的な実務シナリオ：① 組織図API（全従業員と上司名を一覧表示）、② カテゴリの親子関係（categories テーブルで parent_id を持つ木構造）、③ SNSのフォロー/フォロワー（同じ users テーブルの user_id 同士を follows テーブルで結ぶ）。「1つのテーブルの中に関係がある」データを扱うときは SELF JOIN を思い出してください。
1 / 5
問題を選択
QUESTION 7

LEFT JOIN の ON vs WHERE — フィルタをON句に書くかWHERE句に書くかで結果が変わる

LEFT JOINON句フィルタWHERE句の罠NULL保持
前提知識 ▼

LEFT JOIN と組み合わせるとき、フィルタ条件をどこに書くかで結果が変わります。これは実務で最もよく起きるバグの1つです。

-- ✓ ON句フィルタ: 全ユーザー保持 + shipped 注文のみ結合
LEFT JOIN orders AS o
  ON  u.user_id = o.user_id
  AND o.status  = 'shipped'  ← 結合の条件として機能。左テーブル全行を保持

-- ✗ WHERE句フィルタ: NULL行が除外されINNER JOINと同等になる
LEFT JOIN orders AS o ON u.user_id = o.user_id
WHERE o.status = 'shipped'  ← NULLは比較でFALSEになり、注文なし行が消える
ON句 vs WHERE句の違い： ON句のフィルタは「結合の条件」として右テーブルにのみ作用し、左テーブルの全行保持は維持されます。WHERE句のフィルタは JOIN後の仮想テーブル全体に適用されるため、NULL補完された行も除外されてしまいます。
問題 ▼

users テーブルの全ユーザーと、各ユーザーの status = 'shipped' の注文情報を取得してください。shipped 注文がないユーザーや注文自体がないユーザーは NULL で表示します。ON句に AND o.status = 'shipped' を追加して実装してください。

※ 田中（user_id=1）には shipped 注文（id=1）と pending 注文（id=2）の両方があります。pending 注文は ON条件を満たさないため NULL扱いになります。

使用テーブル
▸ users
user_idname
1田中
2佐藤
3山田
▸ orders
order_iduser_idstatus
11shipped
21pending
32shipped
期待出力 ▼
nameorder_idstatus
田中1shipped
佐藤3shipped
山田NULLNULL
模範解答コード ▼ 
SELECT
  u.name,
  o.order_id,
  o.status

FROM users AS u
LEFT JOIN orders AS o
  ON  u.user_id = o.user_id   -- 結合キー
  AND o.status  = 'shipped'  -- ON句フィルタ: shipped のみ結合対象（左行保持は維持）
ORDER BY u.user_id, o.order_id NULLS LAST;

-- ↑ AND を WHERE に書くと間違い:
--   WHERE o.status = 'shipped' にすると NULL行（山田）も除外され
--   INNER JOIN と同じ結果になってしまう。

/*
  実行順序:
  1. FROM users AS u                      → 左テーブルを読み込む
  2. LEFT JOIN orders AS o                → ON（user_id 一致 AND status='shipped'）で結合
  3. SELECT u.name, o.order_id, o.status  → 列を射影
  */
解説（テーブル変化・ポイント） ▼
SELECT u.name, o.order_id, o.status FROM users AS u LEFT JOIN orders AS o ON u.user_id = o.user_id AND o.status = 'shipped';
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM users AS uusers テーブル（3行）を左テーブルとして読み込みます。LEFT JOIN のため、この3行は結果に必ず含まれます。
1 / 3
user_idname
1田中
2佐藤
3山田
全 3行 読込（全行が保持される）
学習ポイント
A B
LEFT JOIN
左テーブルの全行を保持し、ON条件（複数可）に一致する右テーブルの行を結合する。
A: users(u) / B: orders(o)
ON句フィルタ vs WHERE句フィルタの本質的な違い：ON句の条件は「結合の定義」であり、マッチしない行は NULL補完されて左テーブルの全行保持が維持されます。WHERE句の条件は「結合後の仮想テーブルへのフィルタ」であり、NULL補完された行（o.status = NULL）はどんな条件とも一致しないため除外されます。LEFT JOIN の「全行保持」を活かしたいなら、右テーブルへの条件は ON句に書くのが原則です。
LEFT JOIN のあとの WHERE は意図的に使う：Part A の Q3（差集合パターン）で学んだように WHERE o.order_id IS NULL は意図的に NULL行だけを残す使い方でした。本問のように「右テーブルの値で絞り込みたいが左テーブルは全件欲しい」場合は ON句に書きます。「全ユーザー保持しつつ条件付きで結合」→ ON句、「結合後に絞り込む（差集合含む）」→ WHERE句と使い分けます。
アンチパターン
WHERE に書くと LEFT JOIN が INNER JOIN と同等になる：... LEFT JOIN orders AS o ON u.user_id = o.user_id WHERE o.status = 'shipped' と書くと、山田（NULL）と田中のpending行はどちらも NULL = 'shipped' が FALSE になり除外されます。結果は田中(shipped)・佐藤(shipped)の2行のみ、つまり INNER JOIN と同じ結果です。LEFT JOIN を使う意味がなくなります。
実務コラム
このパターンの典型的な実務例：「全ユーザーと直近30日の注文を一覧表示」→ ON u.user_id = o.user_id AND o.created_at >= NOW() - INTERVAL '30 days'。30日以内に注文がないユーザーも NULL で表示できます。WHERE に書いてしまうと「30日以内に注文したユーザーのみ」になってしまいます。ダッシュボードで「全会員のアクティビティ」を表示するAPIでは特に重要な知識です。
2 / 5
問題を選択
QUESTION 8

COALESCE + LEFT JOIN — LEFT JOINのNULLをCOALESCEでデフォルト値に変換する

LEFT JOINCOALESCENULL変換デフォルト値
前提知識 ▼

LEFT JOIN で NULL になった列をそのままAPIレスポンスに含めると、クライアント側で都度 null チェックが必要になります。COALESCE 関数を使うと、SQL側で NULL をデフォルト値に変換できます。

COALESCE(expr, default_value)
-- expr が NULL なら default_value を返す。NULL でなければ expr をそのまま返す。
-- 複数引数も可: COALESCE(a, b, c) → 左から最初の非NULL値を返す
SELECT
  u.name,
  COALESCE(pr.bio, '未設定')           AS bio,     -- NULL → '未設定'
  COALESCE(pr.avatar_url, '/default.png') AS avatar  -- NULL → '/default.png'
FROM users AS u
LEFT JOIN profiles AS pr ON u.user_id = pr.user_id;
COALESCE vs IFNULL / ISNULL： MySQL には IFNULL(expr, default) がありますが、COALESCE はSQL標準でPostgreSQL・MySQL・SQLiteなど全対応です。実務ではCOALESCE を使う方がポータブルです。
問題 ▼

users テーブルの全ユーザーと、各ユーザーのプロフィール情報（bio・avatar_url）を取得してください。プロフィールが未登録のユーザーは、bio を '未設定'、avatar_url を '/img/default.png' で表示してください。

使用テーブル
▸ users
user_idname
1田中
2佐藤
3山田
▸ profiles
user_idbioavatar_url
1SQLが得意/img/tanaka.png
2デザイナー/img/sato.png
期待出力 ▼
namebioavatar_url
田中SQLが得意/img/tanaka.png
佐藤デザイナー/img/sato.png
山田未設定/img/default.png
模範解答コード ▼ 
SELECT
  u.name,
  COALESCE(pr.bio,        '未設定')           AS bio,           -- pr.bio が NULL（プロフィール未登録）なら '未設定' を返す
  COALESCE(pr.avatar_url, '/img/default.png') AS avatar_url  -- pr.avatar_url が NULL なら '/img/default.png' を返す

FROM users AS u
LEFT JOIN profiles AS pr   -- プロフィールが未登録でも users 全行を保持
  ON u.user_id = pr.user_id
ORDER BY u.user_id;

/*
  実行順序:
  1. FROM users AS u           → 全ユーザー3行を読み込む
  2. LEFT JOIN profiles AS pr  → ON条件で照合
  3. SELECT + COALESCE(...)    → 各行に COALESCE を適用
  */
解説（テーブル変化・ポイント） ▼
SELECT u.name, COALESCE(pr.bio, '未設定') AS bio, COALESCE(pr.avatar_url, '/img/default.png') AS avatar_url FROM users AS u LEFT JOIN profiles AS pr ON u.user_id = pr.user_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM users AS uusers テーブル（3行）を左テーブルとして読み込みます。LEFT JOIN のため全ユーザーが結果に含まれます。
1 / 4
user_idname
1田中
2佐藤
3山田
全 3行 読込
学習ポイント
A B
LEFT JOIN (+ COALESCE)
左テーブルを全行保持し、右側に合致がない場合はNULL補完（COALESCEで変換）される。
A: users(u) / B: profiles(pr)
COALESCE(expr, default) の動作：COALESCE は引数を左から順に評価し、最初に NULL でない値を返します。COALESCE(pr.bio, '未設定') は「pr.bio が NULL でなければ pr.bio を、NULL ならば '未設定' を返す」という意味です。複数のフォールバックも可能で、COALESCE(pr.bio, u.name, '未設定') のように書けます。
型の一致に注意：COALESCE の引数は同じデータ型でなければなりません。COALESCE(count_col, '0') のように整数列に文字列を渡すと型エラーになります（PostgreSQL）。数値列なら COALESCE(count_col, 0)、文字列列なら COALESCE(str_col, '') と型を合わせましょう。
アンチパターン
NOT NULL 列に不要な COALESCE は冗長：スキーマで NOT NULL 制約がある列に COALESCE を付けても意味がありません。COALESCE はNULL になりうる列（LEFT JOINの結果や NULLABLEな列）に使うものです。不要な COALESCE はクエリを読みにくくするだけです。
WHERE 句で COALESCE を使うとインデックスが効かない：WHERE COALESCE(col, 0) = 0 のように WHERE 句でインデックス列を COALESCE で包むと、インデックスが使われなくなります（関数を適用するとインデックス列が評価できない）。フィルタ条件では WHERE col IS NULL OR col = 0 のように書くのが適切です。
実務コラム
COALESCE + LEFT JOIN はバックエンドAPIで頻繁に登場します。例：① プロフィール未設定のデフォルト表示（アバター画像URL、自己紹介文）、② 集計クエリのゼロ補完（COALESCE(SUM(amount), 0) AS total）、③ 多言語対応（COALESCE(t.ja, t.en, 'N/A') AS label で日本語なければ英語を返す）。SQL側でデフォルト値を処理することで、アプリコードの null チェックを大幅に削減できます。
3 / 5
問題を選択
QUESTION 9

JOIN + HAVING — GROUP BY で集計した後に条件で絞り込む（WHERE との実行順序の違い）

INNER JOINHAVINGGROUP BY集計後フィルタ
前提知識 ▼

HAVING は GROUP BY の後で集計結果に対して条件を指定するフィルタです。WHERE は集計前（行レベル）のフィルタなので、集計関数（COUNT・SUM など）を WHERE に書くとエラーになります。

SELECT   u.name, COUNT(o.order_id) AS order_count
FROM     users AS u
INNER JOIN orders AS o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name
HAVING   COUNT(o.order_id) >= 2;  -- 集計後の絞り込み
SQL の実行順序（重要）： FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。HAVING は GROUP BY の後なので集計関数が使えます。WHERE は GROUP BY 前なので集計関数は使えません（WHERE COUNT(...) >= 2 は構文エラー）。
問題 ▼

users テーブルと orders テーブルを結合して、注文件数が2件以上のユーザーの名前と注文件数を取得してください。出力は注文件数の降順で並べてください。

使用テーブル
▸ users
user_idname
1田中
2佐藤
3山田
▸ orders
order_iduser_idamount
114200
211800
323500
432000
53900
期待出力 ▼
nameorder_count
田中2
山田2
模範解答コード ▼ 
SELECT
  u.name,
  COUNT(o.order_id)  AS order_count  -- 各グループの注文件数を集計

FROM users AS u
INNER JOIN orders AS o
  ON u.user_id = o.user_id

GROUP BY u.user_id, u.name  -- SELECT の非集計列を全列指定

HAVING COUNT(o.order_id) >= 2  -- GROUP BY 後の集計値に条件をかける（WHERE は不可）

ORDER BY order_count DESC, u.user_id;  -- 同件数はuser_id昇順で確定

/*
  実行順序:
  1. FROM users AS u           → users の3行を読み込む
  2. INNER JOIN orders AS o    → ON条件で結合 → 仮想テーブル5行
  3. WHERE                     → （なし）
  4. GROUP BY u.user_id        → 5行を3グループに分類（田中2行・佐藤1行・山田2行）
  5. HAVING COUNT >= 2         → 各グループの COUNT を評価: 佐藤（COUNT=1）を除外
  6. SELECT u.name, COUNT(...) → 残り2グループを射影
  7. ORDER BY order_count DESC, u.user_id → 件数降順、同件数はuser_id昇順
*/
解説（テーブル変化・ポイント） ▼
SELECT u.name, COUNT(o.order_id) AS order_count FROM users AS u INNER JOIN orders AS o ON u.user_id = o.user_id GROUP BY u.user_id, u.name HAVING COUNT(o.order_id) >= 2 ORDER BY order_count DESC, u.user_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM users AS uusers テーブル（3行）を左テーブルとして読み込みます。
1 / 5
user_idname
1田中
2佐藤
3山田
全 3行 読込
学習ポイント
A B
INNER JOIN
両テーブルに存在するキーが一致する行のみを結合し、その後GROUP BYで集計する。
A: users(u) / B: orders(o)
HAVING は「GROUP BY 後のフィルタ」：SQL の実行順序は FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY です。HAVING は GROUP BY の後に実行されるため、集計関数（COUNT・SUM・AVG など）を条件に使えます。WHERE は GROUP BY 前なので集計関数は使えません。
非集計条件は HAVING でなく WHERE に書く：「特定カテゴリの注文のみ集計したい」場合の category = 'food' のような行レベルの条件は HAVING ではなく WHERE に書くのが正しく、効率的です。WHERE で先に絞り込んでから GROUP BY すると集計対象の行数が減り、パフォーマンスが向上します。
アンチパターン
WHERE に集計関数を書くとエラー：WHERE COUNT(o.order_id) >= 2 は GROUP BY の前に評価されるため、集計が未完了の状態で集計関数を呼ぶことになり構文エラーになります。集計後の絞り込みは必ず HAVINGを使います。
SELECT エイリアスを HAVING で使えないDBがある：SELECT COUNT(...) AS order_count ... HAVING order_count >= 2 の order_count エイリアスは PostgreSQL では HAVING で使えません（GROUP BY では一部使えます）。安全のため HAVING では集計式をそのまま再記述するか、サブクエリで包みます。
実務コラム
HAVING の典型的な実務ユースケース：① ヘビーユーザー抽出（月間購入3回以上のユーザー）、② 売上閾値フィルタ（月間売上が100万円以上の担当者一覧）、③ 品質チェック（同じ email アドレスが2件以上登録されている重複レコードの検出: HAVING COUNT(*) >= 2）。特にデータ品質チェックやセグメント分析では HAVING は必須の構文です。
4 / 5
問題を選択
QUESTION 10

CROSS JOIN — ON句なしで全行×全行の組み合わせ（直積）を生成する

CROSS JOIN直積ON句なしバリアント生成
前提知識 ▼

CROSS JOIN（交差結合）は ON句を指定せず、左テーブルの全行と右テーブルの全行のすべての組み合わせを生成します。結果の行数は「左テーブルの行数 × 右テーブルの行数」です。

FROM   colors AS c
CROSS JOIN sizes AS s;
-- ON句を書かない。colors 3行 × sizes 3行 = 9行を生成する
データ爆発に注意： CROSS JOIN は行数が掛け算で増えます。1万行 × 1万行 = 1億行になるため、大きいテーブルには絶対に使わないこと。また、JOIN の ON句を書き忘れると意図せず CROSS JOIN（直積）が実行されるバグが起きます。
問題 ▼

商品バリアント（色とサイズの全組み合わせ）を生成してください。colors テーブル（3色）と sizes テーブル（3サイズ）を CROSS JOIN して、color_name と size_name の全組み合わせ（合計9行）を取得してください。

使用テーブル
▸ colors
color_idcolor_name
1レッド
2ブルー
3グリーン
▸ sizes
size_idsize_name
1S
2M
3L
期待出力 ▼
color_namesize_name
レッドS
レッドM
レッドL
ブルーS
ブルーM
ブルーL
グリーンS
グリーンM
グリーンL
模範解答コード ▼ 
SELECT
  c.color_name,
  s.size_name

FROM colors AS c
CROSS JOIN sizes AS s  -- ON句なし: c の全3行 × s の全3行 = 9通りを生成

ORDER BY c.color_id, s.size_id; -- 色・サイズの順に並べる

/*
  実行順序:
  1. FROM colors AS c         → colors の3行を読み込む
  2. CROSS JOIN sizes AS s    → ON条件なし。colors の各行に対して sizes の全3行を結合。
                                 レッド × (S, M, L) → 3行
                                 ブルー × (S, M, L) → 3行
                                 グリーン × (S, M, L) → 3行
                                 合計 3 × 3 = 9行の仮想テーブルを生成
  3. SELECT c.color_name, s.size_name → 2列を射影
  4. ORDER BY c.color_id, s.size_id   → 色・サイズ順に並べ替え
*/
解説（テーブル変化・ポイント） ▼
SELECT c.color_name, s.size_name FROM colors AS c CROSS JOIN sizes AS s ORDER BY c.color_id, s.size_id;
LEGEND ▼
データ取得・読込対象
① FROM ▼
FROM colors AS ccolors テーブル（3行）を左テーブルとして読み込みます。CROSS JOIN では各行が右テーブルの全行と組み合わさります。
1 / 3
color_idcolor_name
1レッド
2ブルー
3グリーン
全 3行 読込
学習ポイント
× A B
CROSS JOIN
ON句を持たず、左テーブルと右テーブルの全行同士の組み合わせ（直積）を生成する。
A: colors(c) / B: sizes(s)
CROSS JOIN は「全組み合わせの生成器」：ON句を指定しないため、左テーブルの各行が右テーブルの全行と結合されます。結果行数は 左行数 × 右行数。本問では 3色 × 3サイズ = 9通りです。「あらかじめ存在するデータ行を結合する」ではなく、「テーブルの積を使って新しい組み合わせ行を生成する」という発想の転換が重要です。
古い構文の CROSS JOIN：FROM colors, sizes（カンマ区切り）は暗黙の CROSS JOIN です。SQL-89 の古い記法で、現代では CROSS JOIN キーワードを明示的に書く方が意図が明確です。また、古い記法の FROM カンマ区切りで WHERE を書き忘れると意図せず CROSS JOIN になるバグの原因にもなります。
アンチパターン
大テーブルへの CROSS JOIN は絶対禁止：CROSS JOIN の行数は掛け算で増加します。1万行 × 1万行 = 1億行になり、DBサーバーのメモリを枯渇させクラッシュを引き起こします。CROSS JOIN は行数が少ない小さなマスタデータ同士（色・サイズ・曜日など）にのみ使います。
ON句の書き忘れで意図しない CROSS JOIN が発生する：INNER JOIN table2 と書いて ON句を省略すると、DBによっては CROSS JOIN として実行されます（PostgreSQL ではエラー）。多テーブルJOINで行数が急増したときは「ON句を書き忘れていないか」を最初に確認しましょう。
実務コラム
CROSS JOIN の典型的な実務例：① 商品バリアント生成（本問のように色×サイズ→SKU一覧）、② カレンダー生成（generate_series(1,12) CROSS JOIN generate_series(1,31) で月×日のカレンダーマトリクス）、③ A/Bテストの組み合わせ生成（パターンA×条件Bの全テストケース）。「何も持っていないゼロから組み合わせを作りたい」とき、CROSS JOIN は強力な武器になります。
5 / 5
問題を選択
次のセット →