SQL JOIN — 結合パターンとテーブル変化の基礎

SELECT
  u.name,
  t.tag_name

FROM users AS u
INNER JOIN user_tags AS ut   -- ① まず中間テーブルと結合し、ユーザーが持つ tag_id を引き当てる
  ON u.user_id = ut.user_id
INNER JOIN tags AS t         -- ② 中間テーブルの tag_id を使って、tags テーブルからタグ名を引き当てる
  ON ut.tag_id = t.tag_id
ORDER BY u.user_id, t.tag_name;

/*
  実行順序:
  1. FROM users AS u           → users (2行) を読み込む
  2. INNER JOIN user_tags AS ut→ user_id で照合。田中(1)は2行、佐藤(2)は1行に展開される (計3行の中間結果)
  3. INNER JOIN tags AS t      → 展開された3行の tag_id をもとに tags を照合し結合
  4. SELECT u.name, t.tag_name → 必要な文字列の列だけを射影して出力
*/

SELECT
  ts.student,
  ts.score,
  gs.grade

FROM test_scores AS ts
INNER JOIN grading_scales AS gs                       -- イコールではなく、スコアが範囲内に収まるマスタ行と結合する
  ON ts.score BETWEEN gs.min_score AND gs.max_score
ORDER BY CASE ts.student
  WHEN '田中' THEN 1
  WHEN '佐藤' THEN 2
  ELSE 3 END;

/*
  実行順序:
  1. FROM test_scores AS ts       → 点数テーブル(3行)を読み込む
  2. INNER JOIN grading_scales    → ts.score の値が、gs の min 〜 max の範囲に含まれるか判定
                                  田中の 85 は 80〜100 にマッチ (A)
                                  佐藤の 55 は 0〜59 にマッチ (C)
                                  山田の 72 は 60〜79 にマッチ (B)
  3. SELECT ts.student, ...       → 結合された仮想テーブルから3列を射影
  4. ORDER BY CASE ts.student WHEN '田中' THEN 1 WHEN '佐藤' THEN 2 ELSE 3 END → 入力表の生徒順（田中→佐藤→山田）で出力
*/

SELECT
  d.dept_name,
  e.name

FROM departments AS d
FULL OUTER JOIN employees AS e  -- LEFT JOIN(人事部を残す) と RIGHT JOIN(鈴木を残す) を同時に行う
  ON d.dept_id = e.dept_id
ORDER BY COALESCE(d.dept_id, e.dept_id);

/*
  実行順序:
  1. FROM departments AS d        → 部署テーブル（3行）を読み込む
  2. FULL OUTER JOIN employees e  → 両者の dept_id を照合
       ・マッチあり: 営業(田中)、開発(佐藤)
       ・d 側のみ存在: 人事（e 側はNULL補完）
       ・e 側のみ存在: 鈴木（d 側はNULL補完）
  3. SELECT d.dept_name, e.name   → 全ての行（合計4行）を射影して出力
*/

SELECT
  u.name,
  lo.last_order

FROM users AS u
LEFT JOIN (
  -- 仮想テーブル: orders テーブルをユーザー単位で集計し、最大日付を取得
  SELECT
    user_id,
    MAX(order_date) AS last_order
  FROM orders
  GROUP BY user_id
) AS lo
  ON u.user_id = lo.user_id       -- users と 仮想テーブル(lo) を user_id で結合
ORDER BY u.user_id;

/*
  実行順序:
  1. サブクエリ FROM orders    → orders を読み込む
  2. サブクエリ集計              → ユーザーごとに MAX 日付を算出（lo）
  3. FROM users AS u      → users を読み込む
  4. LEFT JOIN ... AS lo  → lo と結合（未注文はNULL）
  5. SELECT u.name, ...   → 列を射影
  */