【SQL】CASE WHEN完全ガイド｜SELECT・集計ピボット・ORDER BY・UPDATE・WHERE/HAVING・NULL処理・RDBMS別関数まで解説

-- employees テーブル（社員）
-- id | name   | dept  | score | salary | status
--  1 | 田中   | sales |    85 |  30000 | active
--  2 | 鈴木   | tech  |    92 |  45000 | active
--  3 | 高橋   | sales |    60 |  38000 | inactive
--  4 | 田中B  | hr    |    75 |  32000 | active
--  5 | 伊藤   | tech  |    45 |  50000 | active
--  6 | 渡辺   | hr    |  NULL |  42000 | active

-- orders テーブル（注文）
-- id | customer_id | amount | status   | region
--  1 |           1 |  12000 | shipped  | east
--  2 |           2 |   3500 | pending  | west
--  3 |           3 |  85000 | shipped  | east
--  4 |           1 |   2800 | canceled | north
--  5 |           4 |  45000 | pending  | west
--  6 |           2 |   9500 | shipped  | south

-- ===== シンプル CASE（等値比較）=====
-- status を日本語ラベルに変換
SELECT
    id,
    name,
    CASE status
        WHEN 'active'   THEN '在籍中'
        WHEN 'inactive' THEN '休職中'
        ELSE '不明'
    END AS status_label
FROM employees;

-- ===== サーチド CASE（任意条件式）=====
-- score を範囲で評価ランクに変換
SELECT
    id,
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'S'
        WHEN score >= 80 THEN 'A'
        WHEN score >= 70 THEN 'B'
        WHEN score >= 60 THEN 'C'
        WHEN score IS NULL THEN '未評価'
        ELSE 'D'
    END AS rank
FROM employees;

-- ポイント: WHEN 条件は上から順に評価される
-- score=92 は最初の WHEN score >= 90 に一致したら残りの WHEN は評価しない
-- → 最初に一致する条件を上に書く

-- 給与帯のラベルを付ける（サーチド CASE + AND）
SELECT
    id,
    name,
    salary,
    CASE
        WHEN dept = 'tech' AND salary >= 40000 THEN '上位技術職'
        WHEN dept = 'tech' AND salary <  40000 THEN '技術職'
        WHEN dept = 'sales'                     THEN '営業職'
        WHEN dept = 'hr'                        THEN '管理部門'
        ELSE 'その他'
    END AS job_category
FROM employees;

-- 数値の区分変換（IN・BETWEEN・比較を組み合わせる）
SELECT
    id,
    amount,
    CASE
        WHEN status = 'canceled'         THEN 'キャンセル'
        WHEN amount >= 50000              THEN '大口'
        WHEN amount BETWEEN 10000 AND 49999 THEN '中口'
        ELSE '小口'
    END AS order_type
FROM orders;

-- CASE WHEN で 0/1 フラグを生成（EXISTS 的な使い方）
SELECT
    id,
    name,
    CASE WHEN score IS NOT NULL AND score >= 60 THEN 1 ELSE 0 END AS is_passed,
    CASE WHEN status = 'active'                THEN 1 ELSE 0 END AS is_active
FROM employees;

-- 部署ごと・ランク別の人数をクロス集計
SELECT
    dept,
    SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END) AS rank_A_or_S,
    SUM(CASE WHEN score >= 60 AND score < 80 THEN 1 ELSE 0 END) AS rank_B_or_C,
    SUM(CASE WHEN score <  60 THEN 1 ELSE 0 END) AS rank_D,
    SUM(CASE WHEN score IS NULL THEN 1 ELSE 0 END) AS not_evaluated,
    COUNT(*) AS total
FROM employees
GROUP BY dept
ORDER BY dept;

-- ステータス別・地域別の売上をクロス集計
SELECT
    region,
    SUM(CASE WHEN status = 'shipped'  THEN amount ELSE 0 END) AS shipped_total,
    SUM(CASE WHEN status = 'pending'  THEN amount ELSE 0 END) AS pending_total,
    SUM(CASE WHEN status = 'canceled' THEN amount ELSE 0 END) AS canceled_total,
    SUM(amount) AS grand_total
FROM orders
GROUP BY region
ORDER BY grand_total DESC;

-- 各部署のアクティブ率
SELECT
    dept,
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
    -- ELSE を省略すると NULL が COUNT に含まれない
    ROUND(
        COUNT(CASE WHEN status = 'active' THEN 1 END) * 100.0 / COUNT(*),
        1
    ) AS active_rate_pct
FROM employees
GROUP BY dept;

-- 月別・カテゴリ別に件数と金額を横展開
SELECT
    DATE_FORMAT(created_at, '%Y-%m') AS year_month,
    COUNT(CASE WHEN region = 'east'  THEN 1 END) AS east_orders,
    COUNT(CASE WHEN region = 'west'  THEN 1 END) AS west_orders,
    COUNT(CASE WHEN region = 'north' THEN 1 END) AS north_orders,
    COUNT(CASE WHEN region = 'south' THEN 1 END) AS south_orders,
    SUM(CASE WHEN region = 'east'  THEN amount ELSE 0 END) AS east_amount,
    SUM(CASE WHEN region = 'west'  THEN amount ELSE 0 END) AS west_amount
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY year_month;

-- status の独自順序で並べる（active → inactive → その他）
SELECT id, name, status
FROM employees
ORDER BY
    CASE status
        WHEN 'active'   THEN 1
        WHEN 'inactive' THEN 2
        ELSE 3
    END,
    name ASC;

-- NULL を最後に並べる（NULL は通常 ASC で先頭になる RDBMS が多い）
SELECT id, name, score
FROM employees
ORDER BY
    CASE WHEN score IS NULL THEN 1 ELSE 0 END,  -- NULL を後ろに
    score DESC;

-- 緊急性の高いステータスを先頭に、その中で金額降順
SELECT id, customer_id, amount, status
FROM orders
ORDER BY
    CASE status
        WHEN 'pending'  THEN 1  -- 対応中を最優先
        WHEN 'shipped'  THEN 2
        WHEN 'canceled' THEN 3
    END,
    amount DESC;

-- 複数の条件を組み合わせた並び替え
SELECT id, name, dept, salary
FROM employees
ORDER BY
    CASE WHEN dept = 'tech' THEN 0 ELSE 1 END,  -- tech を先に
    salary DESC;

-- スコアに基づいてランクを一括更新
-- （employees テーブルに rank カラムがあると仮定）
UPDATE employees
SET rank = CASE
    WHEN score >= 90 THEN 'S'
    WHEN score >= 80 THEN 'A'
    WHEN score >= 70 THEN 'B'
    WHEN score >= 60 THEN 'C'
    WHEN score IS NULL THEN '未評価'
    ELSE 'D'
END;
-- 全行を条件に応じて 1 回の UPDATE で更新

-- 部署ごとに異なる給与調整率を適用
UPDATE employees
SET salary = CASE
    WHEN dept = 'tech'  AND score >= 80 THEN salary * 1.10  -- 技術部高評価: +10%
    WHEN dept = 'tech'  AND score <  80 THEN salary * 1.05  -- 技術部: +5%
    WHEN dept = 'sales' AND score >= 80 THEN salary * 1.08  -- 営業部高評価: +8%
    WHEN dept = 'sales'               THEN salary * 1.03  -- 営業部: +3%
    ELSE salary * 1.02                                       -- その他: +2%
END;

-- アクティブ社員のスコア上位者のみステータス更新
UPDATE employees
SET status = CASE
    WHEN score >= 90 THEN 'excellent'
    WHEN score >= 75 THEN 'good'
    ELSE 'standard'
END
WHERE status = 'active'    -- アクティブ社員のみ対象
  AND score IS NOT NULL;    -- スコアが入力済みのもの

-- 注文の再計算（金額閾値でステータスを更新）
UPDATE orders
SET status = CASE
    WHEN amount >= 50000 THEN 'vip'
    WHEN amount >= 10000 THEN 'regular'
    ELSE 'small'
END
WHERE status != 'canceled';  -- キャンセル済みは除外

-- 複数の列を同時に条件分岐で更新
UPDATE employees
SET
    rank   = CASE
                 WHEN score >= 90 THEN 'S'
                 WHEN score >= 80 THEN 'A'
                 WHEN score >= 70 THEN 'B'
                 WHEN score >= 60 THEN 'C'
                 ELSE 'D'
             END,
    salary = CASE
                 WHEN score >= 90 THEN salary * 1.15
                 WHEN score >= 80 THEN salary * 1.10
                 WHEN score >= 70 THEN salary * 1.05
                 ELSE salary
             END,
    updated_at = NOW()  -- 更新日時も同時に設定
WHERE status = 'active'
  AND score IS NOT NULL;

-- ★ 重要: UPDATE 前に SELECT で確認する習慣を
-- SELECT id, name, score, rank, salary,
--        CASE WHEN score >= 90 THEN 'S' ... END AS new_rank,
--        CASE WHEN score >= 90 THEN salary * 1.15 ... END AS new_salary
-- FROM employees WHERE status = 'active' AND score IS NOT NULL;

-- パラメータで絞り込み条件を切り替える例
-- @filter = 'high' → score >= 80、それ以外 → 全件
SET @filter = 'high';

SELECT id, name, score
FROM employees
WHERE
    CASE @filter
        WHEN 'high' THEN score >= 80
        WHEN 'low'  THEN score < 60
        ELSE 1 = 1  -- 全件
    END;

-- ※ MySQL では CASE が評価値を返すため CASE WHEN ... THEN 1 ELSE 0 END = 1 の形が確実
SELECT id, name, score
FROM employees
WHERE CASE
    WHEN @filter = 'high' THEN CASE WHEN score >= 80 THEN 1 ELSE 0 END
    WHEN @filter = 'low'  THEN CASE WHEN score <  60 THEN 1 ELSE 0 END
    ELSE 1
END = 1;

-- 実務では WHERE col IN (...) や OR を使う方が一般的
-- WHERE 句の複数条件については
-- https://codingls.com/sql/1850/ を参照

-- 部署別の集計後、条件によって閾値を変えてフィルタ
SELECT
    dept,
    AVG(score) AS avg_score,
    COUNT(*) AS member_count
FROM employees
WHERE score IS NOT NULL
GROUP BY dept
HAVING
    AVG(score) >= CASE dept
        WHEN 'tech'  THEN 75  -- 技術部は 75 以上
        WHEN 'sales' THEN 70  -- 営業部は 70 以上
        ELSE 65
    END;
-- 部署ごとに異なる閾値を設定してフィルタリング

-- NULL を CASE WHEN で処理する
SELECT
    id,
    name,
    score,
    CASE
        WHEN score IS NULL THEN '未評価'  -- IS NULL で NULL チェック
        WHEN score >= 80   THEN '優秀'
        ELSE '標準'
    END AS evaluation
FROM employees;

-- NG: NULL との等値比較（WHEN score = NULL は常に FALSE）
CASE
    WHEN score = NULL THEN '未評価'  -- これは動かない！
    ...

-- ELSE なし → 全条件に一致しない場合は NULL が返る
SELECT
    id,
    CASE WHEN score >= 80 THEN '優秀' END AS grade
    -- score が NULL または 80 未満 → NULL が返る
FROM employees;

-- CASE WHEN と COALESCE の使い分け
-- 単純な NULL→代替値ならCOALESCEの方が簡潔
SELECT COALESCE(score::VARCHAR, '未評価') AS score_display FROM employees;

-- 複数条件（NULL 含む）が絡む場合は CASE WHEN を使う
SELECT
    CASE
        WHEN score IS NULL             THEN '未入力'
        WHEN score >= 80               THEN '合格'
        WHEN status = 'inactive'       THEN '休職中'
        ELSE '不合格'
    END AS result
FROM employees;

-- 2 段階の条件分岐（部署 → スコア）
SELECT
    id,
    name,
    dept,
    score,
    CASE dept
        WHEN 'tech' THEN
            CASE
                WHEN score >= 85 THEN 'テックリード候補'
                WHEN score >= 70 THEN '技術職標準'
                ELSE '要スキルアップ'
            END
        WHEN 'sales' THEN
            CASE
                WHEN score >= 80 THEN '営業エース'
                ELSE '営業標準'
            END
        ELSE 'その他部門'
    END AS career_path
FROM employees;

-- ネストが深い場合の代替: AND 条件でフラット化
-- 上記と同等（ネストなし・より可読性が高い）
SELECT
    id,
    name,
    CASE
        WHEN dept = 'tech'  AND score >= 85 THEN 'テックリード候補'
        WHEN dept = 'tech'  AND score >= 70 THEN '技術職標準'
        WHEN dept = 'tech'                  THEN '要スキルアップ'
        WHEN dept = 'sales' AND score >= 80 THEN '営業エース'
        WHEN dept = 'sales'                 THEN '営業標準'
        ELSE 'その他部門'
    END AS career_path
FROM employees;
-- ↑ こちらの方が読みやすい（フラットな CASE WHEN が推奨）

-- ===== MySQL: IF() =====
SELECT
    id,
    IF(score >= 70, '合格', '不合格') AS result,
    IF(status = 'active', salary * 1.05, salary) AS adjusted_salary
FROM employees;

-- 3条件以上は CASE WHEN の方が読みやすい
-- IF(score>=90, 'S', IF(score>=80, 'A', IF(score>=70, 'B', 'C')))  -- 読みにくい
-- CASE WHEN score>=90 THEN 'S' WHEN score>=80 THEN 'A' ...  -- 推奨

-- ===== SQL Server: IIF() =====
SELECT
    id,
    IIF(score >= 70, '合格', '不合格') AS result
FROM employees;

-- ===== Oracle: DECODE() =====
SELECT
    id,
    DECODE(status, 'active', '在籍中', 'inactive', '休職中', '不明') AS status_label,
    DECODE(dept, 'tech', salary * 1.1, 'sales', salary * 1.08, salary) AS adjusted_salary
FROM employees;

-- ===== 移植性を考えると CASE WHEN 推奨 =====
-- CASE WHEN は MySQL / PostgreSQL / SQL Server / Oracle すべてで動作する
SELECT
    CASE status
        WHEN 'active'   THEN '在籍中'
        WHEN 'inactive' THEN '休職中'
        ELSE '不明'
    END AS status_label
FROM employees;

-- ===== NG: WHERE 句で CASE WHEN を列に適用（インデックス不使用）=====
SELECT * FROM employees
WHERE CASE WHEN dept = 'tech' THEN score ELSE 0 END >= 80;
-- 列に関数的に CASE WHEN を適用しているためインデックス非使用になりやすい

-- OK: 通常の条件式に書き直す
SELECT * FROM employees
WHERE dept = 'tech' AND score >= 80;

-- ===== UPDATE + CASE WHEN は全行スキャンが発生しやすい =====
-- WHERE 句でできるだけ対象行を絞り込む
UPDATE employees
SET rank = CASE WHEN score >= 80 THEN 'A' ELSE 'B' END;
-- 全行を更新（WHERE なし）→ テーブルフルスキャン

-- WHERE で対象行を絞る
UPDATE employees
SET rank = CASE WHEN score >= 80 THEN 'A' ELSE 'B' END
WHERE score IS NOT NULL  -- score がある行のみ（インデックスを活用できる）
  AND status = 'active';

-- ===== SUM(CASE WHEN ...) のピボット集計は GROUP BY と組み合わせる =====
-- フィルターが必要な場合は WHERE で集計前に絞る
SELECT
    dept,
    SUM(CASE WHEN score >= 80 THEN 1 ELSE 0 END) AS high_score
FROM employees
WHERE status = 'active'  -- GROUP BY 前に WHERE でフィルタ（効率的）
GROUP BY dept;