SQL CASE WHEN THEN — クエリ内の条件ロジック

CASEによる条件ロジック

CASE式はSQLのif-else構文です。条件を順番に評価し、最初に一致するWHEN分岐の値を返します。

検索CASE（最も一般的）

SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 70000  THEN 'Mid'
    WHEN salary >= 40000  THEN 'Junior'
    ELSE 'Intern'
  END AS level
FROM employees;

シンプルCASE

SELECT name, department_id,
  CASE department_id
    WHEN 1 THEN 'Engineering'
    WHEN 2 THEN 'Marketing'
    WHEN 3 THEN 'Sales'
    ELSE 'Other'
  END AS department_name
FROM employees;

WHERE句でのCASE

SELECT * FROM orders
WHERE CASE
  WHEN status = 'urgent' THEN priority > 5
  ELSE priority > 2
END;

ORDER BYでのCASE

SELECT * FROM tasks
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    ELSE 4
  END;

UPDATEでのCASE

UPDATE employees
SET bonus = CASE
  WHEN rating >= 5 THEN salary * 0.20
  WHEN rating >= 3 THEN salary * 0.10
  ELSE salary * 0.05
END;

条件付き集約

SELECT
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count,
  SUM(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_total
FROM accounts;

CASE式はSQLで最も汎用性の高い構文の一つです。SELECT、WHERE、ORDER BY、GROUP BY、UPDATE、INSERT文で機能します。