SQLのCASE式とは ― CASE WHENの条件分岐と集計

SQLのCASE式を使うと、条件分岐をSQLの中に書けます。注文の状態コード0・1・2を未処理・処理中・完了と表示したいときや、売上を月別に横へ並べたいときに役立ちます。

既存のSQLで CASE WHEN ... END を見かけたら、それがこの式です。行ごとに条件を調べて1つの値を返すので、SELECT句だけでなく、式を書ける場所ならどこにでも書けます。表のデータを変えずに表示を変えられ、1回のSQLで条件別の集計も出せます。

次の注文の表を例にします。状態は0が未処理、1が処理中、2が完了で、注文番号6の行にはまだ値が入っていません。

CASE式はCASEで始まり、ENDで終わります。間に、条件と返す値の組を WHEN 条件 THEN 値 の形で並べます。どの条件にも当たらないときの値は、最後のELSEに書きます。

検索CASE式は、WHENの後に条件を書く

WHENごとに条件を丸ごと書く形を、検索CASE式と呼びます。SELECT文のWHERE句に書ける条件は、ここにも書けます。

SELECT 注文番号,
       CASE
         WHEN 状態 = 0 THEN '未処理'
         WHEN 状態 = 1 THEN '処理中'
         WHEN 状態 = 2 THEN '完了'
         ELSE '未設定'
       END AS 状態名
FROM 注文;

単純CASE式は、CASEの後の式とWHENの値を比べる

CASEの直後に比べる式を書き、WHENには値だけを並べる形を、単純CASE式と呼びます。上の文は次のように書き換えられ、結果は同じです。1つの列を決まった値と比べるだけなら、こちらのほうが短く書けます。

SELECT 注文番号,
       CASE 状態
         WHEN 0 THEN '未処理'
         WHEN 1 THEN '処理中'
         WHEN 2 THEN '完了'
         ELSE '未設定'
       END AS 状態名
FROM 注文;

2つの違いは、WHENに書ける条件の幅です。単純CASE式で調べられるのは、等しいかどうかだけです。金額の範囲やNULLの判定には、検索CASE式を使います。

CASE式は、WHENの条件を上から1つずつ調べます。真になった時点で、そのTHENの値が結果に決まり、残りのWHENは調べません。1行に2つの条件が当てはまっても、返る値は上に書いたほうです。

金額で注文を大口・中口・小口に分ける例で確かめます。金額 >= 10000 は32000の行にも当てはまりますが、先に大口に決まるので、中口にはなりません。

SELECT 注文番号, 金額,
       CASE
         WHEN 金額 >= 30000 THEN '大口'
         WHEN 金額 >= 10000 THEN '中口'
         ELSE '小口'
       END AS 区分
FROM 注文;

範囲の条件を並べるときは、範囲の狭い条件を上に書きます。2つのWHENを入れ替えると、32000の行も中口になります。

PostgreSQLの文書には、結果に要らない部分は計算しないと書かれています。ただし、1/0 のような定数だけの式は実行前に計算されることがあり、選ばれないELSEに書いてあってもエラーになります。

ELSEを省くと、どのWHENにも当たらない行はNULLになる

ELSEは省略できます。その場合、どのWHENにも当たらない行の結果はNULLになります。PostgreSQLとMySQLのどちらの文書にも、同じ動きが書かれています。

エラーにならないので、コードの種類が増えたときなどに、表示が空になる行が黙って出てきます。分類から漏れる行が出そうなら、ELSE '不明' のように書いておくと、すぐに気づけます。

書き方を選ぶと、7行それぞれがどこに当たり、何を返すかが切り替わります。結果は、PostgreSQLで実際に実行して確かめた値です。

SELECT 注文番号,
  CASE
    WHEN 金額 >= 30000 THEN '大口'
    WHEN 金額 >= 10000 THEN '中口'
    ELSE '小口'
  END
FROM 注文;

SELECT 注文番号,
  CASE
    WHEN 金額 >= 10000 THEN '中口'
    WHEN 金額 >= 30000 THEN '大口'
    ELSE '小口'
  END
FROM 注文;

SELECT 注文番号,
  CASE
    WHEN 金額 >= 30000 THEN '大口'
    WHEN 金額 >= 10000 THEN '中口'
  END
FROM 注文;

SELECT 注文番号,
  CASE 状態
    WHEN NULL THEN '未設定'
    ELSE '設定済み'
  END
FROM 注文;

SELECT 注文番号,
  CASE
    WHEN 状態 IS NULL THEN '未設定'
    ELSE '設定済み'
  END
FROM 注文;

検索CASE式の節の文を実行すると、状態の0は未処理、1は処理中、2は完了と表示されます。値の無い注文番号6は未設定になります。変わるのは検索結果の見え方だけで、表の中のコードはそのままです。CASE式の後に AS 状態名 と書くと、結果の列に名前を付けられます。

WHENの条件には、サブクエリも書けます。次の文は、全注文の平均と比べて行を分けます。

SELECT 注文番号,
       CASE
         WHEN 金額 >= (SELECT AVG(金額) FROM 注文) THEN '平均以上'
         ELSE '平均未満'
       END AS 判定
FROM 注文;   -- 注文番号1と4が平均以上

同じ置き換えをいくつものSQLに書くなら、CASE式を増やすより、コードと名前の対応表を作るほうが向いています。その表をJOINでつなげば、コードが増えても対応表の1行を足すだけで済みます。

集約関数の中にCASE式を書くと、条件に合う行だけを集計できます。WHEREで絞ると、ほかの条件の行も結果から消えてしまいます。CASE式なら全行を残したまま、件数と完了件数のように条件の違う集計を1行に並べられます。

COUNTとCASE式で、条件に合う行を数える

店舗ごとの全件数と完了件数を、1回のSQLで求めます。

SELECT 店舗,
       COUNT(*) AS 件数,
       COUNT(CASE WHEN 状態 = 2 THEN 1 END) AS 完了件数
FROM 注文
GROUP BY 店舗
ORDER BY 店舗;

COUNTの中のCASE式には、ELSEを書きません。COUNTはNULLでない行を数えるので、条件に合わない行をNULLにしておく必要があります。ELSE 0 を足すと0も数えられ、完了件数が全件数と同じになります。SUM(CASE WHEN 状態 = 2 THEN 1 ELSE 0 END) と書けば、1と0を足して同じ値を求められます。

SUMとCASE式で、月別の売上を横に並べる

行と列に項目を置いて集計する形を、クロス集計と呼びます。月ごとに1列ずつSUMを書くと、1店舗が1行になり、月が列に並びます。

SELECT 店舗,
       SUM(CASE WHEN 月 = 4 THEN 金額 ELSE 0 END) AS "4月",
       SUM(CASE WHEN 月 = 5 THEN 金額 ELSE 0 END) AS "5月"
FROM 注文
GROUP BY 店舗
ORDER BY 店舗;

上野には4月の注文がありません。ここで ELSE 0 を省くと、上野の4月は0ではなくNULLになります。SUMは、足す値が1つも無いときにNULLを返すためです。表計算ソフトに渡すなど、空欄を避けたいときは ELSE 0 を書きます。

GROUP BYで、CASE式の区分ごとに集計する

SELECT句でCASE式に付けた別名は、GROUP BYに書けます。次の文は、大口・中口・小口ごとの件数と合計を求めます。

SELECT CASE
         WHEN 金額 >= 30000 THEN '大口'
         WHEN 金額 >= 10000 THEN '中口'
         ELSE '小口'
       END AS 区分,
       COUNT(*) AS 件数,
       SUM(金額) AS 合計
FROM 注文
GROUP BY 区分;
-- 大口 1件 32000／中口 2件 30000／小口 4件 25000

PostgreSQLとMySQLは、この書き方を受け付けます。別名を使えない製品では、同じCASE式をGROUP BYにもう一度書きます。WHERE句に別名は書けないので、WHEREで絞るときも式をそのまま書きます。

ORDER BYで、決まった順番に並べる

コードの大小とは違う順番に並べたいときは、CASE式で並び順の番号を返します。次の文は、処理中・未処理・完了・未設定の順に並べます。

SELECT 注文番号, 状態
FROM 注文
ORDER BY CASE 状態
           WHEN 1 THEN 1
           WHEN 0 THEN 2
           WHEN 2 THEN 3
           ELSE 4
         END,
         注文番号;
-- 注文番号の順：3、2、7、1、4、5、6

UPDATEで、行ごとに違う値を1つの文で書き込む

UPDATE文のSETにCASE式を書くと、行ごとに違う値を書き込めます。状態の0と1を入れ替える例を考えます。2つのUPDATEに分けると、0を1に変えた行が次の文でまた0に戻り、0と1の行がすべて0になります。

UPDATE 注文
SET 状態 = CASE 状態
             WHEN 0 THEN 1
             WHEN 1 THEN 0
             ELSE 状態
           END;

1つの文にまとめると、どの行も更新前の値で判定されるので、入れ替えが正しく終わります。UPDATEのCASE式では、ELSEを省かないようにします。上の文から ELSE 状態 を消すと、WHEREが無いので全行が対象のまま、状態が2の3行がNULLで上書きされます。

THENとELSEの値は、型をそろえる

CASE式が返す値は1つの列になるので、THENとELSEの値は同じ型にそろえます。PostgreSQLでは、数値の列と文字列を混ぜるとエラーになります。

SELECT CASE WHEN 状態 = 2 THEN 金額 ELSE '対象外' END
FROM 注文;
-- ERROR: invalid input syntax for type integer: "対象外"

PostgreSQLで金額も文字列として出したいなら、金額::text のように型を変換してそろえます。MySQLの文書では、数値と文字列を混ぜたCASE式の結果はVARCHARになります。エラーにならない製品でも、結果は数値として並べ替えられないので、型はそろえておくほうが安全です。

単純CASE式のWHEN NULLは、NULLの行に当たらない

CASE 状態 WHEN NULL THEN '未設定' と書いても、注文番号6の行は未設定になりません。単純CASE式は=で比べるので、NULL = NULL が真にならず、ELSEへ進みます。理由はNULLの記事にある3値論理です。NULLを調べるときは、検索CASE式で WHEN 状態 IS NULL THEN と書きます。

否定の条件では、NULLの行がELSEへ流れる

検索CASE式でも、NULLとの比較は真になりません。次の文の注文番号6は、状態が分からないのに完了と表示されます。

SELECT 注文番号,
       CASE WHEN 状態 <> 2 THEN '未完了' ELSE '完了' END AS 判定
FROM 注文;

ELSEには、WHENが真にならなかった行がすべて来ます。NULLの行は、次のように別のWHENで分けておきます。

CASE
  WHEN 状態 = 2 THEN '完了'
  WHEN 状態 IS NULL THEN '未設定'
  ELSE '未完了'
END

• PostgreSQL 18 文書「Conditional Expressions」（CASE式の2つの形、判定の順番、ELSEを省いたときの結果）
• PostgreSQL 18 文書「UNION, CASE, and Related Constructs」（THENとELSEの型の決まり方）
• PostgreSQL 18 文書「SELECT」（GROUP BY・ORDER BYでの別名）
• PostgreSQL 18 文書「Aggregate Functions」（COUNT・SUMとNULL）
• MySQL 8.4 Reference Manual「Flow Control Functions」（CASE演算子、結果の型）
• MySQL 8.4 Reference Manual「Problems with Column Aliases」（GROUP BY・ORDER BY・WHEREでの別名）