SQL カーディナリティ — 1:1・1:N・N:Nテーブル関係の基礎

QUESTION 1

1対1 — ユーザーとプロフィールを INNER JOIN し、行数が変わらないことを確認する

INNER JOIN1:1行数維持カーディナリティ

前提知識 ▼

カーディナリティ（多重度）とは、テーブル間の「1行が相手テーブルの何行と対応するか」を表す関係性です。JOIN 前後の行数変化を予測するために不可欠な概念です。

最もシンプルな関係が 1対1（1:1）です。テーブルAの1行がテーブルBのちょうど1行と対応する関係で、結合しても行数は増減しません。

-- 1:1 の例: users 1行 ↔ user_profiles 1行
SELECT u.name, p.email
FROM users AS u
INNER JOIN user_profiles AS p
  ON u.user_id = p.user_id;
-- users: 3行 → JOIN後: 3行（行数変化なし）

1:1 の見分け方：結合キーが両方のテーブルで一意（PRIMARY KEY または UNIQUE 制約）であれば 1:1 関係です。1ユーザーに1プロフィール、1注文に1配送先のような構造が該当します。

問題 ▼

users テーブルと user_profiles テーブルを user_id で INNER JOIN し、ユーザーの名前とメールアドレスを取得してください。

使用テーブル

▸ users

user_id	name
1	田中
2	佐藤
3	山田

▸ user_profiles

profile_id	user_id	email
1	1	tanaka@example.com
2	2	sato@example.com
3	3	yamada@example.com

※ user_profiles.user_id には UNIQUE 制約があり、各ユーザーにプロフィールは1つだけです。

期待出力 ▼

name	email
田中	tanaka@example.com
佐藤	sato@example.com
山田	yamada@example.com

模範解答コード ▼

SELECT
  u.name,
  p.email
FROM users AS u
INNER JOIN user_profiles AS p
  ON u.user_id = p.user_id  -- 両方のテーブルで user_id は一意
ORDER BY u.user_id;

/*
  実行順序:
  1. FROM users AS u           → 行を読み込む
  2. INNER JOIN user_profiles  → 結合（一致行のみ）
  3. SELECT u.name, p.email    → 列を評価
  4. ORDER BY u.user_id        → 並び替えて出力
*/

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

SELECT
  u.name,
  p.email
FROM users AS u
INNER JOIN user_profiles AS p
  ON u.user_id = p.user_id
ORDER BY u.user_id;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM users AS uusers テーブル（3行）を読み込みます。このテーブルの user_id は主キーで、値は一意です。

1 / 3

user_id	name
1	田中
2	佐藤
3	山田

全 3行読込

学習ポイント

CARDINALITY

1対1（1:1）— 両テーブルの結合キーが一意

結合しても行数は増減しない
u = users p = user_profiles 3行 → 3行

1:1 の条件 — 両側のキーが一意：users.user_id は PRIMARY KEY（一意）、user_profiles.user_id も UNIQUE 制約で一意です。つまりどちらのテーブルも結合キーに重複値がないため、1行が必ず1行にマッチし、行の展開（増加）が起きません。これが 1:1 関係の本質です。

行数変化の公式 — 1:1 は最もシンプル：1:1 の INNER JOIN における行数は MIN(左テーブルのマッチ行数, 右テーブルのマッチ行数) です。全行がマッチする場合は行数が一切変わらないため、集計時に重複カウントのリスクがありません。次の問題（1:N）で行数が増える場面を学び、その違いを実感してください。

アンチパターン

UNIQUE 制約がないのに 1:1 と思い込む：user_profiles.user_id に UNIQUE 制約がなければ、同一ユーザーに複数のプロフィールが存在しえます。その場合 JOIN で行が増加し（1:N 関係）、集計値がずれる原因になります。結合前にカーディナリティを確認する習慣が重要です。

1:1 を常に別テーブルにする必要はない：name と email を同じ users テーブルに入れれば JOIN 自体が不要です。1:1 で分離するのは、プロフィール情報の遅延ロード、権限分離、変更頻度の違いなど明確な設計理由があるときだけにしましょう。

実務コラム

1:1 関係の実務例：users ↔ user_settings（通知ON/OFF等の設定テーブル）、orders ↔ shipping_addresses（注文ごとの配送先）、products ↔ product_details（商品詳細情報の分離）。1:1 JOIN は行数が変化しないため、SUM や COUNT を安全に使える「最も事故の起きにくい JOIN」です。

QUESTION 2

1対N — 部門テーブルと社員テーブルを JOIN し、行数が展開される仕組みを理解する

INNER JOIN1:N行数展開カーディナリティ

前提知識 ▼

1対N（1:N）は最も頻出の関係性です。テーブルAの1行がテーブルBの複数行と対応する関係で、JOIN すると「1」側の行が「N」側の行数分だけ展開（増加）します。

-- 1:N の例: 1部門 → N人の社員
SELECT d.dept_name, e.name
FROM departments AS d
INNER JOIN employees AS e
  ON d.dept_id = e.dept_id;
-- departments: 3行 → JOIN後: 5行（社員数に展開される）

行数展開が集計に与える影響：
1:N の JOIN では「1」側のデータ（例: dept_name）が N 行分コピーされます。この展開された状態で SUM や COUNT をすると二重・三重カウントの原因になります。展開を意識し、集計対象を正しく選ぶことが重要です。

問題 ▼

departments テーブルと employees テーブルを INNER JOIN し、各社員の名前（emp_name）と所属部門名（dept_name）を取得してください。

使用テーブル

▸ departments

dept_id	dept_name
1	営業部
2	開発部
3	人事部

▸ employees

emp_id	name	dept_id
1	田中	1
2	佐藤	1
3	山田	2
4	鈴木	2
5	高橋	2

期待出力 ▼

emp_name	dept_name
田中	営業部
佐藤	営業部
山田	開発部
鈴木	開発部
高橋	開発部

模範解答コード ▼

SELECT
  e.name AS emp_name,
  d.dept_name
FROM departments AS d
INNER JOIN employees AS e
  ON d.dept_id = e.dept_id  -- 1部門 → N社員（1:N で行が展開される）
ORDER BY d.dept_id, e.emp_id;

/*
  実行順序:
  1. FROM departments AS d      → 行を読み込む
  2. INNER JOIN employees AS e  → 結合（一致行のみ）
  3. SELECT e.name, d.dept_name → 列を評価
  4. ORDER BY                   → 並び替えて出力
*/

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

SELECT
  e.name AS emp_name,
  d.dept_name
FROM departments AS d
INNER JOIN employees AS e
  ON d.dept_id = e.dept_id
ORDER BY d.dept_id, e.emp_id;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM departments AS ddepartments テーブル（3行）を読み込みます。dept_id は主キーで一意です。この「1」側テーブルの行が、JOINでどう変化するかに注目してください。

1 / 4

dept_id	dept_name
1	営業部
2	開発部
3	人事部

全 3行読込（1側テーブル）

学習ポイント

CARDINALITY

1対N（1:N）— 「1」側の行が「N」側の件数分だけ展開される

結合キーの重複がある側に行数が合わせられる
d = departments(1側) e = employees(N側) 3行 → 5行

行数展開の仕組み：departments の「営業部」(dept_id=1)は employees で2行にマッチするため、JOIN 結果では「営業部」が2行にコピーされます。同様に「開発部」は3行に展開されます。JOIN 後の行数はマッチした「N」側の行数で決まり、この場合 2 + 3 = 5行です。

1:N の見分け方：結合キーが一方では一意（departments.dept_id = PK）、他方では重複あり（employees.dept_id は複数社員で共有）の場合が 1:N です。SQL ではPK側が「1」、FK側が「N」と覚えましょう。

アンチパターン

1:N の展開を忘れて「1」側の値を SUM する：もし departments に budget（予算）列があり、JOIN 後に SUM(d.budget) を全体で取ると、営業部の予算が2回、開発部の予算が3回カウントされます。集計は「N」側の列に対して行うか、展開前に「1」側を先に集計しておく必要があります。

マッチ0件の部門を見落とす：人事部は社員が0人のため INNER JOIN では結果に含まれません。全部門を表示したい場合は LEFT JOIN を使い、COUNT(e.emp_id) で 0 件を正しくカウントします。

実務コラム

1:N 関係は DB 設計の基本パターンで、実務のほとんどの JOIN がこれに該当します。users → orders（1ユーザーが複数注文）、categories → products（1カテゴリに複数商品）、posts → comments（1投稿に複数コメント）など。1:N JOIN は行数を展開するという事実を常に意識し、「JOIN したらこの行数になるはず」と予測する習慣が、JOIN事故を防ぐ最大の武器になります。

QUESTION 3

1対N + GROUP BY — JOIN で展開された行を集計し、元の行数に集約する

INNER JOIN1:N + GROUP BY展開→集約COUNT / SUM

前提知識 ▼

1:N JOIN による行数展開を GROUP BY で集約するパターンです。JOIN で N 行に展開された結果を、元の「1」側のキーでグループ化し、COUNT や SUM で集計します。

-- 行数変化の流れ: 展開 → 集約
customers(3行)
  → INNER JOIN orders → 6行 に展開（1:N）
  → GROUP BY customer_id → 3行 に集約

展開→集約パターンの行数変化を予測する：
① JOIN: 「1」側の行数 → 「N」側のマッチ行数 に展開
② GROUP BY: 展開された行数 → グループ数（= 「1」側の元の行数）に集約
この「展開→集約」の往復を理解することが、正しい集計クエリを書く基盤になります。

問題 ▼

customers と orders を INNER JOIN し、顧客ごとの注文件数（order_count）と合計金額（total_amount）を取得してください。注文件数の多い順で出力します。

使用テーブル

▸ customers

customer_id	name
1	田中
2	佐藤
3	山田

▸ orders

order_id	customer_id	amount
1	1	3000
2	1	1500
3	2	5000
4	2	2000
5	2	800
6	3	4000

期待出力 ▼

name	order_count	total_amount
佐藤	3	7800
田中	2	4500
山田	1	4000

模範解答コード ▼

SELECT
  c.name,
  COUNT(o.order_id)  AS order_count,
  SUM(o.amount)      AS total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name  -- 展開された行を元の顧客単位に集約
ORDER BY order_count DESC;

/*
  実行順序:
  1. FROM customers AS c     → 行を読み込む
  2. INNER JOIN orders AS o  → 結合（一致行のみ）
  3. GROUP BY c.customer_id  → グループ化
  4. 集計関数を評価          → COUNT, SUM
  5. SELECT                  → 列を評価
  6. ORDER BY order_count DESC    → 並び替えて出力
*/

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

SELECT
  c.name,
  COUNT(o.order_id)  AS order_count,
  SUM(o.amount)    AS total_amount
FROM customers AS c
INNER JOIN orders AS o
  ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name
ORDER BY order_count DESC;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM customers AS ccustomers テーブル（3行）を読み込みます。customer_id は主キー（1側）です。

1 / 5

customer_id	name
1	田中
2	佐藤
3	山田

全 3行読込（1側テーブル）

学習ポイント

ROW COUNT FLOW

展開→集約 — 1:N JOIN + GROUP BY の行数変化

JOIN で N 側の行数に展開 → GROUP BY で元の行数に戻る
3行(元) → 6行(JOIN展開) → 3行(GROUP BY集約)

展開→集約で正しい集計が得られる理由：JOIN で展開された各行は「N」側の異なるレコードを持っています（order_id が各行で一意）。この展開された行を GROUP BY で「1」側のキーでまとめ、COUNT・SUM で集計するため、重複なく正確な値が得られます。1:N + GROUP BY は SQL 集計の最も基本的なパターンです。

行数変化を事前に予測する習慣：SQL を書く前に「customers 3行 × orders 6行 → JOIN後 6行 → GROUP BY後 3行」と行数フローを頭で追うことで、結果の妥当性を検証できます。もし GROUP BY 後に想定と違う行数が返ってきたら、結合キーの重複（カーディナリティの誤り）を疑いましょう。

アンチパターン

GROUP BY を忘れると展開されたまま出力される：SELECT c.name, o.amount FROM customers c JOIN orders o ... のように GROUP BY なしで書くと、佐藤が3行出力されます。1:N JOIN 後に集計する場合は必ず GROUP BY を書きましょう。

GROUP BY に非集計列を書き忘れるとエラーになる：PostgreSQL では SELECT c.name ... GROUP BY c.customer_id とだけ書くと c.name がエラーになります。SELECT に書いた集計関数以外の列はすべて GROUP BY にも書く必要があります（主キーの関数従属性を除く）。

実務コラム

「顧客別の売上集計」「カテゴリ別の商品数」「月別の注文件数」——これらはすべて 1:N JOIN + GROUP BY の展開→集約パターンです。実務では「JOIN後に何行になるか」を常に予測することで、クエリ結果を目視確認する前にバグを防げます。まずは SELECT COUNT(*) で JOIN 後の行数を確認する習慣をつけましょう。

QUESTION 4

N対N — 中間テーブルを介して学生と講座を結合し、多対多の関係を理解する

INNER JOIN ×2N:N中間テーブルカーディナリティ

前提知識 ▼

N対N（多対多）は「1人の学生が複数の講座を受講」かつ「1つの講座に複数の学生が参加」する関係です。RDBMS では中間テーブル（Junction Table）を使って表現します。

-- N:N の構造: students ←(1:N)→ enrollments ←(N:1)→ courses
FROM students AS s
INNER JOIN enrollments AS e ON s.student_id = e.student_id
INNER JOIN courses AS c    ON e.course_id = c.course_id;

N:N = 2つの 1:N の組み合わせ：
中間テーブル（enrollments）は students と courses の両方の外部キーを持ちます。
students →(1:N)→ enrollments と courses →(1:N)→ enrollments という2つの 1:N関係で構成されます。JOIN 後の行数は中間テーブルの行数に一致します。

問題 ▼

students、enrollments（中間テーブル）、courses を2回の INNER JOIN で結合し、各学生が受講している講座名を一覧で取得してください。

使用テーブル

▸ students

student_id	name
1	田中
2	佐藤
3	山田

▸ enrollments（中間テーブル）

enrollment_id	student_id	course_id
1	1	1
2	1	2
3	2	1
4	2	3
5	3	2

▸ courses

course_id	course_name
1	SQL基礎
2	Python入門
3	データ分析

期待出力 ▼

student_name	course_name
田中	SQL基礎
田中	Python入門
佐藤	SQL基礎
佐藤	データ分析
山田	Python入門

模範解答コード ▼

SELECT
  s.name AS student_name,
  c.course_name
FROM students AS s
INNER JOIN enrollments AS e   -- 1回目: students →(1:N)→ enrollments
  ON s.student_id = e.student_id
INNER JOIN courses AS c  -- 2回目: enrollments →(N:1)→ courses
  ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_id;

/*
  実行順序:
  1. FROM students AS s            → students を読み込む
  2. INNER JOIN enrollments AS e   → 結合（1:N で展開）
  3. INNER JOIN courses AS c       → 結合（N:1）
  4. SELECT s.name, c.course_name  → 2列を射影
  5. ORDER BY                      → student_id・course_id でソート
  */

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

SELECT
  s.name AS student_name,
  c.course_name
FROM students AS s
INNER JOIN enrollments AS e
  ON s.student_id = e.student_id
INNER JOIN courses AS c
  ON e.course_id = c.course_id
ORDER BY s.student_id, c.course_id;

LEGEND ▼

データ取得・読込対象

① FROM ▼

FROM students AS sstudents テーブル（3行）を読み込みます。この後、中間テーブル enrollments を経由して courses に辿り着きます。

1 / 4

student_id	name
1	田中
2	佐藤
3	山田

全 3行読込

学習ポイント

CARDINALITY

N対N（N:N）— 中間テーブルで2つの 1:N を結ぶ

結果行数 = 中間テーブルの行数になる
s = students e = enrollments(中間) c = courses

N:N の結合行数は中間テーブルで決まる：1回目の JOIN（students → enrollments）で 1:N 展開により中間テーブルの行数（5行）になります。2回目の JOIN（enrollments → courses）は N:1 のため行数は変わりません。つまり最終的な行数 = 中間テーブルの行数です。中間テーブルの1行が「学生Xが講座Yを受講」という1つの関係を表しています。

中間テーブルの UNIQUE 制約：enrollments テーブルには通常 UNIQUE(student_id, course_id) の複合ユニーク制約を設定します。これにより「同じ学生が同じ講座に二重登録」することを防ぎ、意図しない行数膨張を防止できます。

アンチパターン

中間テーブルを省略して直接 JOIN しようとする：students と courses を中間テーブルなしに直接 JOIN する方法はありません（結合キーが存在しない）。無理に CROSS JOIN すると 3×3=9行（すべての組み合わせ）が生成され、実際の受講関係を表しません。N:N には必ず中間テーブルを使います。

カンマ区切りで N:N を表現する（非正規化）：students テーブルに course_ids = '1,2' のようにカンマ区切りで格納する設計は、JOIN・検索・集計がすべて困難になるアンチパターンです。中間テーブルによる正規化が RDB の正しい N:N 表現です。

実務コラム

N:N 関係の実務例: users ↔ roles（1ユーザーが複数ロール。中間テーブル user_roles）、products ↔ tags（1商品に複数タグ。中間テーブル product_tags）、actors ↔ movies（出演関係）。中間テーブルには関係の属性（例: enrolled_at や role_level）を追加できるため、単なる紐付け以上の情報も管理できます。

QUESTION 5

ファントラップ — 2つの 1:N を同時に JOIN すると発生する重複集計の罠と回避策

派生テーブルファントラップ重複集計JOIN事故

前提知識 ▼

ファントラップ（Fan Trap）は、1つのテーブルから2つの 1:N テーブルを同時に JOINしたときに発生する行の意図しない増殖（直積）です。最も頻出する JOIN 事故であり、カーディナリティ理解の集大成です。

-- [Warning] 間違い: 2つの 1:N テーブルを同時にJOIN → 直積が発生
FROM users AS u
INNER JOIN orders AS o  ON u.user_id = o.user_id  -- 1:N (1)
INNER JOIN reviews AS r ON u.user_id = r.user_id  -- 1:N (2)
-- 田中: orders 2行 × reviews 2行 = 4行に膨張！

なぜ直積が起きるのか：
orders と reviews に直接の結合キーがないため、ユーザー「田中」の orders 2行と reviews 2行のすべての組み合わせ（2×2=4行）が生成されます。
この状態で SUM(o.amount) すると各金額が reviews の行数分だけ重複カウントされ、誤った集計結果になります。

問題 ▼

各ユーザーの合計注文金額（total_amount）と平均レビュー評価（avg_rating）を取得してください。ファントラップを回避するため、各 1:N を個別に集計してから JOIN する正しいアプローチで解いてください。

使用テーブル

▸ users

user_id	name
1	田中
2	佐藤

▸ orders

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

▸ reviews

review_id	user_id	rating
1	1	5
2	1	3
3	2	4

期待出力 ▼

name	total_amount	avg_rating
田中	4500	4.0
佐藤	5000	4.0

模範解答コード ▼

-- ✓ 正しいアプローチ: 各 1:N を個別に集計 → 1:1 で安全に JOIN
SELECT
  u.name,
  o_agg.total_amount,
  r_agg.avg_rating
FROM users AS u
INNER JOIN (
  SELECT user_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
) AS o_agg ON u.user_id = o_agg.user_id
INNER JOIN (
  SELECT user_id, ROUND(AVG(rating), 1) AS avg_rating
  FROM reviews
  GROUP BY user_id
) AS r_agg ON u.user_id = r_agg.user_id
ORDER BY u.user_id;

/*
  実行順序:
  1. サブクエリ①            → orders を集計（o_agg）
  2. サブクエリ②            → reviews を集計（r_agg）
  3. FROM users AS u   → users を読み込む
  4. INNER JOIN o_agg  → user_id で結合
  5. INNER JOIN r_agg  → user_id で結合
  6. SELECT            → 結果を出力
  */

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

SELECT
  u.name,
  o_agg.total_amount,
  r_agg.avg_rating
FROM users AS u
INNER JOIN (
  SELECT user_id, SUM(amount) AS total_amount
  FROM orders GROUP BY user_id
) AS o_agg ON u.user_id = o_agg.user_id
INNER JOIN (
  SELECT user_id, ROUND(AVG(rating), 1) AS avg_rating
  FROM reviews GROUP BY user_id
) AS r_agg ON u.user_id = r_agg.user_id;

LEGEND ▼

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

グループ分類

① 派生テーブル①: orders 集計 ▼

SELECT user_id, SUM(amount) FROM orders GROUP BY user_idまず orders を user_id で集計し、派生テーブル o_agg を生成します。user_id が一意になるため、後の JOIN は 1:1 になります。

1 / 6

user_id	amount
1	3000
1	1500
2	5000

orders 3行 → 2グループに集約

学習ポイント

FAN TRAP SOLUTION

ファントラップ回避 — 先に集計して 1:1 に変換してから JOIN

2つの 1:N を個別に集計し、1:1 の派生テーブルとして結合
u = users o_agg = orders集計 r_agg = reviews集計

ファントラップの本質 — 直積による行の膨張：users を起点に orders（2行）と reviews（2行）を同時 JOIN すると、orders と reviews の間に結合条件がないため同一ユーザー内で直積が発生します。田中の場合 2×2=4行に膨張し、SUM(amount)が2倍の9000になります。Q2で学んだ「1:N は行を展開する」という知識を組み合わせると、この2つの展開が掛け算になる（N₁×N₂）ことが理解できます。

回避策の核心 — 「先に集計して 1:N を 1:1 に変換する」：各 1:N テーブルを GROUP BY で集計すると、user_id が一意な派生テーブルになります。これを users と JOIN すれば 1:1 × 1:1 のため行数が膨張しません。「JOINする前にカーディナリティを 1:1 に揃える」がファントラップ回避の鉄則です。

アンチパターン

DISTINCT で直積を「ごまかす」：SELECT DISTINCT で重複行を除去しても、集計関数（SUM・AVG）の値は直積の膨張を反映した間違った値のままです。DISTINCT は見た目の行を減らすだけで、集計の二重カウントは修正しません。

ファントラップの検出方法：JOIN 後に SELECT COUNT(*) を実行し、想定より行数が多い場合はファントラップを疑ってください。特に同じテーブルに対して2つ以上の 1:N テーブルを JOIN するときは必ず起きます。「合計額がやけに大きい」も典型的なサインです。

実務コラム

ファントラップは実務で最も遭遇する JOIN 事故です。典型例: 「ユーザー別の売上合計と問い合わせ件数を1つのダッシュボードで出したい」→ orders と tickets を同時 JOIN → 売上が膨張。解決策は3つあります。① 本問の派生テーブル方式、② WITH 句（CTE）で可読性を高める方式、③ ウィンドウ関数を使う方式。いずれも核心は「先に集計してカーディナリティを 1:1 に揃える」ことです。