SQL JOIN — 結合パターンと行数変化の応用

SELECT
  u.name,
  COUNT(o.order_id)          AS order_count,  -- COUNT(列名)はNULLをスキップ → 0件ユーザーは自動で0になる
  COALESCE(SUM(o.amount), 0) AS total_amount  -- SUM(NULL) = NULL のため COALESCE で 0 に変換する
FROM users AS u
LEFT JOIN orders AS o                         -- 全ユーザーを保持（山田は NULL 補完）
  ON u.user_id = o.user_id
GROUP BY u.user_id, u.name                    -- NULL 行も含めてグループ化する
ORDER BY total_amount DESC;

/*
  実行順序:
  1. FROM users AS u             → users を読み込む
  2. LEFT JOIN orders AS o       → ON で照合・NULL 補完
  3. GROUP BY u.user_id, u.name  → ユーザーでグループ化
  4. COUNT / COALESCE(SUM,0)     → 件数・合計（NULLは0補正）
  5. SELECT / ORDER BY           → total_amount DESC で出力
  */

SELECT
  e.name AS employee,             -- 部下側エイリアス e の name
  m.name AS manager               -- 上司側エイリアス m の name
FROM employees AS e
INNER JOIN employees AS m         -- 同じテーブルを別ロールで結合: e.manager_id = m.emp_id で上司を特定
  ON e.manager_id = m.emp_id      -- manager_id=NULL（田中）は ON条件不成立 → 除外
ORDER BY e.manager_id, e.emp_id;

/*
  実行順序:
  1. FROM employees AS e       → 全5行を「部下ロール」として読み込む
  2. INNER JOIN employees AS m → 各行の manager_id を emp_id として上司を照合
                                 manager_id=NULL（田中）はON条件を満たさないため除外
  3. SELECT e.name, m.name     → 部下名・上司名の2列を射影
  4. ORDER BY                  → manager_id → emp_id の順でソート
*/

SELECT
  o.order_id,
  u.name AS user_name,
  o.amount,
  CASE o.status                    -- 単純CASE式: o.status の値で分岐して日本語ラベルに変換する
    WHEN 'pending'   THEN '処理中'
    WHEN 'shipped'   THEN '発送済み'
    WHEN 'delivered' THEN '配達完了'
    WHEN 'cancelled' THEN 'キャンセル'
    ELSE             '不明'          -- 未定義ステータスへのフォールバック
  END AS status_label
FROM orders AS o
INNER JOIN users AS u
  ON o.user_id = u.user_id
ORDER BY o.order_id;

/*
  実行順序:
  1. FROM orders AS o          → 注文5行を読み込む
  2. INNER JOIN users AS u     → user_id で照合し user_name を付与
  3. CASE o.status ...         → 各行の status 値を評価し日本語ラベルに変換
  4. SELECT / ORDER BY         → 4列を order_id 昇順で出力
*/

SELECT
  u.name,
  s.latest_order_date,
  s.total_amount
FROM users AS u
INNER JOIN (                        -- FROM句のサブクエリ（派生テーブル）
  SELECT
    user_id,
    MAX(ordered_at) AS latest_order_date,  -- 最新注文日
    SUM(amount)     AS total_amount        -- 合計金額
  FROM orders
  GROUP BY user_id
) AS s                             -- 派生テーブルに別名 s を付ける
  ON u.user_id = s.user_id
ORDER BY s.latest_order_date DESC; -- 最新注文日の降順

/*
  実行順序:
  1. サブクエリ実行             → orders を GROUP BY user_id で集計
                                 派生テーブル s（3行）が生成される
  2. FROM users AS u           → users の3行を読み込む
  3. INNER JOIN ... AS s       → u.user_id と s.user_id を照合・結合（3行保持）
  4. SELECT u.name, s.*        → 必要な3列を射影
  5. ORDER BY latest_order_date DESC → 最新注文日の降順でソート
*/