SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意

SQL の CASE 式の使い方 - 検索 CASE と単純 CASE、条件付き集計・並べ替え・UPDATE と評価順序の注意

作成日:
読了:約68分
更新日:

当サイトは Amazon.co.jp を宣伝しリンクすることで紹介料を得る手段を提供する、Amazonアソシエイト・プログラムの参加者です。価格・在庫はリンク先の最新情報をご確認ください。

ステータスのコードを「有効」「休止」のような表示名に変える、年齢を「30歳未満」「30〜49歳」のような区分にまとめる、月ごとの売上を横に並べる、決まった順番で並べ替える。SQL で「条件によって値を変えたい」と思ったときに使うのが CASE 式です。

CASE 式そのものは、プログラミング言語の if や switch に近い単純な仕組みです。それでも、「上の条件に吸い込まれて下の分岐に届かない」「ELSE を書き忘れて NULL で上書きした」「PostgreSQL だけ型のエラーになる」「0 除算を CASE で避けたはずなのにエラーになる」といったつまずきがよく起きます。どれも、CASE が「式」であり、決まった規則で評価されることを押さえると説明できます。

この記事では、小さなサンプルテーブルを最初に1回だけ定義し、すべてのクエリをそのデータに対して実行して結果を並べます。CASE 式の2つの形から始めて、書ける場所、区分けと条件付き集計、並べ替えと UPDATE、0 除算の回避、似た関数との関係、型と評価順序の注意、インデックスとの関係までを順に確認します。

NOTE

記事中の結果表は、特記のない限り SQLite 3.54.0、PostgreSQL 18.6、MySQL 8.4.13 の3つで実行し、同じ結果になることを確かめたものです。結果表の NULL は NULL 値を表します。製品で結果が違うものは、その都度どの製品の結果かを書いています。SQL Server は手元で実行しておらず、Microsoft のドキュメントの記述に基づいています。

先に結論 - 早見表

やりたいこと書き方注意点
値に応じて別の値を返す単純 CASE CASE 列 WHEN 値 THEN ... END比較は = だけ。WHEN NULL は一致しない
条件に応じて別の値を返す検索 CASE CASE WHEN 条件 THEN ... END上から順に評価し、最初に真になった分岐を返す
どれにも当てはまらないときの値ELSE 値省略すると NULL。NULL の行も ELSE に落ちる
区分けして集計するGROUP BY に同じ CASE 式を書く境界の重なり・漏れは分岐の順番で決まる
条件ごとに別々に集計するSUM(CASE WHEN 条件 THEN 列 END)COUNT(CASE ... ELSE 0 END) は全件を数えてしまう
行を列に並べ替える(ピボット)列ごとに SUM(CASE ...) を書く列の数はクエリを書く時点で決める
任意の順番で並べるORDER BY CASE 列 WHEN ... THEN 1 ... ENDNULL の行の位置も ELSE で決めておく
複数の行を別々の値に1文で更新SET 列 = CASE ... END + WHEREELSE も WHERE もないと他の行が NULL になる
0 除算を避けるa / NULLIF(b, 0)0 除算の扱いそのものが製品で違う
型をそろえる数値と文字列を混ぜない。必要なら CASTPostgreSQL はエラー、MySQL・SQLite は通る
危険な計算を避ける分岐で守るのは1行ずつの計算だけ定数式と集約関数は CASE より先に評価されることがある

サンプルデータ

以降の例はすべて次のデータを使います。サンプル商店(仮)の会員5人と、注文7件です。

サンプルテーブルの定義とデータ
CREATE TABLE members (
  id     INTEGER PRIMARY KEY,
  name   TEXT NOT NULL,
  age    INTEGER,          -- 未回答は NULL
  status TEXT,             -- 'A' 有効 / 'S' 休止 / 'X' 退会、未設定は NULL
  plan   TEXT NOT NULL     -- 'free' / 'pro'
);
 
CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  member_id  INTEGER NOT NULL REFERENCES members(id),
  ordered_on DATE NOT NULL,
  amount     INTEGER NOT NULL,
  quantity   INTEGER NOT NULL   -- 入力漏れで 0 の行がある
);
 
INSERT INTO members VALUES
  (1, '会員A', 25,   'A',  'pro'),
  (2, '会員B', 34,   'A',  'free'),
  (3, '会員C', 47,   'S',  'pro'),
  (4, '会員D', 61,   'X',  'free'),
  (5, '会員E', NULL, NULL, 'free');
 
INSERT INTO orders VALUES
  (101, 1, '2026-09-12', 1200, 2),
  (102, 1, '2026-10-03', 3000, 3),
  (103, 2, '2026-10-15',  800, 1),
  (104, 2, '2026-11-02', 2500, 0),
  (105, 3, '2026-11-08', 1500, 1),
  (106, 3, '2026-09-20', 4000, 3),
  (107, 5, '2026-11-18',  600, 2);

members テーブル:

idnameagestatusplan
1会員A25Apro
2会員B34Afree
3会員C47Spro
4会員D61Xfree
5会員ENULLNULLfree

orders テーブル:

idmember_idordered_onamountquantity
10112026-09-1212002
10212026-10-0330003
10322026-10-158001
10422026-11-0225000
10532026-11-0815001
10632026-09-2040003
10752026-11-186002

ポイントは次の3つです。

  • 会員E は年齢もステータスも NULL(未回答・未設定)
  • 会員D には注文が1件もない
  • 注文 104 は quantity が 0(入力漏れ)

CASE 式とは - 条件で値を切り替える式

CASE 式は、条件に応じて1つの値を返す式です。PostgreSQL のドキュメントは、CASE 式を「他のプログラミング言語の if/else 文に似た、汎用の条件式」と説明しています。

ここで大事なのは「文」ではなく「式」だという点です。CASE 式は 1 + 2 や UPPER(name) と同じように1つの値になるので、値を書ける場所ならどこにでも書けます。逆に、CASE 式で「どの SQL 文を実行するか」を切り替えることはできません。Microsoft の SQL Server のドキュメントも、CASE 式は Transact-SQL の文やストアドプロシージャの実行の流れを制御するのには使えない、と明記しています(文の流れを切り替える CASE「文」は後の節で扱います)。

CASE 式には2つの形があります。

単純 CASE 式 - 1つの値と比べる

CASE の直後に式を1つ書き、それを各 WHEN の値と順に比べる形です。

単純 CASE 式でステータスを表示名にする
SELECT id, name, status,
       CASE status
         WHEN 'A' THEN '有効'
         WHEN 'S' THEN '休止'
         WHEN 'X' THEN '退会'
         ELSE '未設定'
       END AS status_label
FROM members
ORDER BY id;
idnamestatusstatus_label
1会員AA有効
2会員BA有効
3会員CS休止
4会員DX退会
5会員ENULL未設定

PostgreSQL のドキュメントは、単純 CASE を「最初の式を計算し、等しいものが見つかるまで WHEN 句の値と比較する。見つからなければ ELSE 句の結果(または NULL)を返す。C 言語の switch 文に似ている」と説明しています。

検索 CASE 式 - 条件を書く

CASE の直後には何も書かず、WHEN ごとに真偽を返す条件を書く形です。範囲や複数の列を使った条件は、こちらでしか書けません。

検索 CASE 式で年齢層に分ける
SELECT id, name, age,
       CASE
         WHEN age < 30  THEN '30歳未満'
         WHEN age < 50  THEN '30〜49歳'
         WHEN age >= 50 THEN '50歳以上'
         ELSE '未回答'
       END AS age_group
FROM members
ORDER BY id;
idnameageage_group
1会員A2530歳未満
2会員B3430〜49歳
3会員C4730〜49歳
4会員D6150歳以上
5会員ENULL未回答

単純 CASE で書けるものは、WHEN status = 'A' のように検索 CASE でも書けます。SQLite のドキュメントは、CASE x WHEN w1 THEN r1 ... END と CASE WHEN x=w1 THEN r1 ... END の違いは、前者では x がちょうど1回だけ評価され、後者では複数回評価されうることだけだ、と説明しています。迷ったら検索 CASE で書いておけば困ることはありません。

ELSE を省略すると NULL

どの WHEN にも当てはまらず、ELSE も書かれていない場合、CASE 式の結果は NULL です。これは PostgreSQL・MySQL・SQLite・SQL Server のいずれのドキュメントにも同じ趣旨で書かれています。

ELSE を省略した場合
SELECT id, status,
       CASE status WHEN 'A' THEN '有効' WHEN 'S' THEN '休止' END AS no_else
FROM members
ORDER BY id;
idstatusno_else
1A有効
2A有効
3S休止
4XNULL
5NULLNULL

ELSE を省略すると NULL になる、という性質は、後で見る条件付き集計ではむしろ便利に使えます。一方、表示用のラベルや UPDATE では、想定外の値が黙って NULL になる原因になります。NULL を返したいのでなければ ELSE を書く、と決めておくと事故が減ります。

上から順に評価され、最初に一致した分岐だけが返る

検索 CASE は、WHEN を上から順に調べ、最初に真になった WHEN の THEN を返して終わります。PostgreSQL のドキュメントは「条件の結果が真なら、CASE 式の値はその条件に続く結果になり、CASE 式の残りは処理されない。真でなければ、続く WHEN 句が同じ方法で調べられる」と書いています。

そのため、範囲の条件を並べるときは順番が結果を左右します。同じ3つの条件を、順番だけ変えて並べてみます。

条件の順番を間違えた CASE と正しい CASE
SELECT id, age,
       CASE
         WHEN age >= 20 THEN '20歳以上'
         WHEN age >= 40 THEN '40歳以上'
         WHEN age >= 60 THEN '60歳以上'
       END AS wrong_order,
       CASE
         WHEN age >= 60 THEN '60歳以上'
         WHEN age >= 40 THEN '40歳以上'
         WHEN age >= 20 THEN '20歳以上'
       END AS right_order
FROM members
ORDER BY id;
idagewrong_orderright_order
12520歳以上20歳以上
23420歳以上20歳以上
34720歳以上40歳以上
46120歳以上60歳以上
5NULLNULLNULL

wrong_order では、47歳も61歳も最初の age >= 20 に当てはまってしまい、2番目以降の分岐には一度も届きません。エラーにはならないので、テストデータに境界付近の値がないと気付けない書き方です。

範囲で区分けするときは、次のどちらかにそろえると間違えにくくなります。

  • 「未満」で区切るなら小さい順に並べる(前節の age_group のように age < 30、age < 50、…)
  • 「以上」で区切るなら大きい順に並べる(right_order のように age >= 60、age >= 40、…)

上の分岐で当てはまった行は下に来ないので、2番目の条件に age >= 30 AND age < 50 のような下限を重ねて書く必要はありません。

複数の条件を組み合わせる

1つの WHEN には、AND・OR・IN・BETWEEN などを使った任意の条件を書けます。キャンペーンの対象を、ステータス・プラン・年齢の組み合わせで決めてみます。

WHEN に複数の条件を書く
SELECT id, name, plan, age, status,
       CASE
         WHEN status IN ('S', 'X')         THEN '対象外'
         WHEN plan = 'pro' AND age < 30    THEN '若年プロ向け'
         WHEN plan = 'pro' OR age >= 30    THEN '一般向け'
         ELSE '未分類'
       END AS campaign
FROM members
ORDER BY id;
idnameplanagestatuscampaign
1会員Apro25A若年プロ向け
2会員Bfree34A一般向け
3会員Cpro47S対象外
4会員Dfree61X対象外
5会員EfreeNULLNULL未分類

会員C は pro ですが、先に status IN ('S', 'X') に当てはまるので「対象外」になります。除外したい条件は先頭に書くのが基本です。

会員E が「未分類」になっている点にも注目してください。status が NULL なので status IN ('S', 'X') は真にならず、age が NULL なので age < 30 も age >= 30 も真になりません。CASE 式は WHEN の条件が真のときだけその分岐を選ぶので、NULL を含む比較の結果(UNKNOWN)は「当てはまらない」として扱われ、最後に ELSE に落ちます。SQLite のドキュメントも「WHEN 項を評価するとき、NULL の結果は真ではないとみなされる」と書いています。

単純 CASE の WHEN NULL は一致しない

同じ理由で、単純 CASE に WHEN NULL と書いても、NULL の行には一致しません。

単純 CASE の WHEN NULL と、検索 CASE の IS NULL
SELECT id, status,
       CASE status WHEN NULL THEN '未設定' ELSE '設定あり' END    AS simple_case,
       CASE WHEN status IS NULL THEN '未設定' ELSE '設定あり' END AS searched_case
FROM members
ORDER BY id;
idstatussimple_casesearched_case
1A設定あり設定あり
2A設定あり設定あり
3S設定あり設定あり
4X設定あり設定あり
5NULL設定あり未設定

単純 CASE は内部で status = NULL と比べるのと同じで、その結果は真ではなく UNKNOWN だからです。SQLite のドキュメントは、単純 CASE の比較には = 演算子と同じ NULL の扱いが適用され、基準の式が NULL なら結果は常に ELSE(なければ NULL)になる、と書いています。MySQL のリファレンスでも、ストアドプログラムの CASE 文の説明で「NULL = NULL は偽なので、この構文では NULL との等価を判定できない」と注意しています。

NULL を判定したいときは、検索 CASE で WHEN 列 IS NULL と書きます。= で NULL を比べると真にならない理由(3値論理)は SQL の NULL の扱い方 で詳しく扱っています。

NOTE

単純 CASE に WHEN 'S', 'X' のようにカンマで複数の値を並べる書き方は、PostgreSQL 18.6 では syntax error at or near "," になりました。複数の値をまとめたいときは、検索 CASE で WHEN status IN ('S', 'X') と書きます。

CASE 式を書ける場所

CASE 式は値を返す式なので、値や条件を書ける場所ならほぼどこにでも書けます。SQL Server のドキュメントは、「有効な式を書ける任意の文・句で CASE を使える」として、SELECT・UPDATE・DELETE・SET の各文と、選択リスト・IN・WHERE・ORDER BY・HAVING の各句を例に挙げています。MySQL のリファレンスにも、CASE を含むフロー制御の関数がまとめられています。

場所書き方の例この記事の節
SELECT リストCASE status WHEN 'A' THEN '有効' ... END AS label上の各例
GROUP BYGROUP BY CASE WHEN age < 30 THEN ... END区分けして集計する
集約関数の中SUM(CASE WHEN 条件 THEN amount END)条件付き集計とピボット
HAVINGHAVING SUM(CASE ... END) >= 1000WHERE・HAVING で使う
ORDER BYORDER BY CASE status WHEN 'S' THEN 1 ... END任意の並び順
WHEREWHERE CASE WHEN ... END >= 1000WHERE・HAVING で使う
UPDATE の SETSET plan = CASE plan WHEN ... ENDUPDATE で書き換える
JOIN の ONON t.code = CASE WHEN ... END(式なので同じように書ける)
関数の引数・CASE の中COALESCE(CASE ... END, 0)、CASE の入れ子0 除算・似た関数との関係

入れ子にもできますが、SQL Server のドキュメントには「CASE 式の入れ子は10階層まで」という制限が書かれています。PostgreSQL・MySQL・SQLite の入れ子の上限は今回は確認していません。いずれにしても、入れ子が深くなったら、分岐を1段の検索 CASE にまとめられないかを先に考えたほうが読みやすくなります。

区分けして集計する - 年齢層・金額帯

CASE 式のいちばん多い使い方の1つが、連続した値を区分(ビン)にまとめて集計することです。GROUP BY に、SELECT と同じ CASE 式を書きます。

年齢層ごとの人数
SELECT CASE
         WHEN age < 30  THEN '30歳未満'
         WHEN age < 50  THEN '30〜49歳'
         WHEN age >= 50 THEN '50歳以上'
         ELSE '未回答'
       END AS age_group,
       COUNT(*) AS cnt
FROM members
GROUP BY CASE
           WHEN age < 30  THEN '30歳未満'
           WHEN age < 50  THEN '30〜49歳'
           WHEN age >= 50 THEN '50歳以上'
           ELSE '未回答'
         END
ORDER BY MIN(age) IS NULL, MIN(age);
age_groupcnt
30歳未満1
30〜49歳2
50歳以上1
未回答1

区分名(文字列)で並べると、照合順序によって順番が製品ごとに変わることがあります(手元でも MySQL だけ順番が違いました)。ここでは各グループの最小年齢 MIN(age) で並べ、未回答を最後に回しています。

同じ CASE 式を2回書くのが冗長なら、GROUP BY age_group のように SELECT の別名で書く方法もあります。手元では PostgreSQL 18.6・MySQL 8.4.13・SQLite 3.54.0 のいずれでも GROUP BY で別名が使えましたが、HAVING や WHERE で別名が使えるかは製品で違います。詳しくは SQL の GROUP BY と HAVING の使い方 の「SELECT の別名をどこで使えるか」を参照してください。GROUP BY 1 のように列番号で書く方法も3製品で動きましたが、列を足し引きしたときにずれやすい書き方です。

金額帯も同じ形で書けます。

金額帯ごとの件数と合計
SELECT CASE
         WHEN amount < 1000 THEN '1000円未満'
         WHEN amount < 3000 THEN '1000〜2999円'
         ELSE '3000円以上'
       END AS amount_band,
       COUNT(*)    AS cnt,
       SUM(amount) AS total
FROM orders
GROUP BY 1
ORDER BY MIN(amount);
amount_bandcnttotal
1000円未満21400
1000〜2999円35200
3000円以上27000

GROUP BY は実在する値からしかグループを作らないので、該当する行が1件もない区分(たとえば「10000円以上」)は結果に出てきません。0 件の区分も表に出したい場合は、区分の一覧を別のテーブルや CTE で用意して LEFT JOIN します。この考え方は GROUP BY の記事の「LEFT JOIN と組み合わせる」と同じです。

条件付き集計とピボット - SUM(CASE ...)・COUNT(CASE ...)

集約関数の中に CASE 式を書くと、行ごとに「集計に入れる値」を切り替えられます。条件ごとに別々の集計を1行に並べる、いわゆる条件付き集計です。基本の形(SUM(CASE WHEN 条件 THEN 列 END))と FILTER 句との対応は GROUP BY と HAVING の記事 の「条件付き集計」で扱ったので、ここでは CASE 式ならではの使い方と落とし穴を中心に見ます。

行を列に変換する(ピボット)

会員ごとの月別の売上を、月を列にして横に並べます。

会員ごとの月別売上を横に並べる
SELECT member_id,
       SUM(CASE WHEN ordered_on >= '2026-09-01' AND ordered_on < '2026-10-01'
                THEN amount ELSE 0 END) AS sep,
       SUM(CASE WHEN ordered_on >= '2026-10-01' AND ordered_on < '2026-11-01'
                THEN amount ELSE 0 END) AS oct,
       SUM(CASE WHEN ordered_on >= '2026-11-01' AND ordered_on < '2026-12-01'
                THEN amount ELSE 0 END) AS nov,
       SUM(amount) AS total
FROM orders
GROUP BY member_id
ORDER BY member_id;
member_idsepoctnovtotal
11200300004200
2080025003300
34000015005500
500600600

縦に7行あった注文が、会員ごとに1行、月ごとに1列の表になりました。各行は、自分の月の列には amount を、それ以外の列には 0 を出し、それを SUM で足し合わせています。

月の判定は「月初以上、翌月初未満」の範囲で書いています。月を取り出す関数は製品ごとに名前が違いますが、この書き方なら3製品で同じように動きます。

ピボットで作れる列は、クエリを書く時点で決まっている必要があります。SQL の結果の列の数と名前はクエリの文面で決まるので、「データにある月の数だけ列を作る」ことは1つの静的なクエリではできません。列が増減する集計表は、アプリケーション側でクエリを組み立てるか、縦持ちのまま取り出して表示側で並べ替えます。

COUNT と SUM の数え方の違い - ELSE 0 の罠

条件に合う行の件数を数える書き方は2通りあります。そして、その2つを混ぜると全件を数えてしまいます。

条件に合う件数を数える3つの書き方
SELECT COUNT(*)                                          AS all_cnt,
       COUNT(CASE WHEN amount >= 2000 THEN 1 END)        AS count_case,
       SUM(CASE WHEN amount >= 2000 THEN 1 ELSE 0 END)   AS sum_case,
       COUNT(CASE WHEN amount >= 2000 THEN 1 ELSE 0 END) AS count_wrong
FROM orders;
all_cntcount_casesum_casecount_wrong
7337
  • COUNT(CASE WHEN 条件 THEN 1 END) は 3。条件に合わない行は ELSE がないので NULL になり、COUNT(式) は NULL を数えない
  • SUM(CASE WHEN 条件 THEN 1 ELSE 0 END) も 3。合う行の 1 を足し合わせる
  • COUNT(CASE WHEN 条件 THEN 1 ELSE 0 END) は 7。0 も「NULL ではない値」なので、COUNT は全行を数えてしまう

COUNT を使うなら ELSE を書かない、ELSE 0 を書くなら SUM を使う、と組み合わせを決めておくと間違えません。

該当がないときは NULL か 0 か

ELSE を書かない SUM(CASE ...) は、条件に合う行が1件もないグループで NULL を返します。会員を起点に LEFT JOIN して、11月の売上を出してみます。

ELSE の有無で、該当なしの結果が変わる
SELECT m.id,
       SUM(CASE WHEN o.ordered_on >= '2026-11-01' THEN o.amount END)        AS nov_no_else,
       SUM(CASE WHEN o.ordered_on >= '2026-11-01' THEN o.amount ELSE 0 END) AS nov_else0,
       COUNT(CASE WHEN o.ordered_on >= '2026-11-01' THEN 1 END)            AS nov_cnt
FROM members m
LEFT JOIN orders o ON o.member_id = m.id
GROUP BY m.id
ORDER BY m.id;
idnov_no_elsenov_else0nov_cnt
1NULL00
2250025001
3150015001
4NULL00
56006001

会員A は11月の注文がなく、会員D は注文自体がありません。どちらも ELSE を書かない列は NULL、ELSE 0 の列は 0 です(会員D の行は LEFT JOIN で o.ordered_on が NULL になり、条件が真にならないので ELSE に落ちます)。COUNT は該当がなければ 0 を返します。

「売上 0 円」と「集計対象がない」を区別したいなら NULL のまま、帳票で 0 を表示したいなら ELSE 0 か COALESCE(SUM(...), 0) にします。

FILTER 句との比較

PostgreSQL と SQLite では、集約関数ごとに入力行を絞る FILTER 句も使えます。PostgreSQL のドキュメントは、FILTER を指定すると filter_clause が真になる入力行だけが集約関数に渡され、他の行は捨てられる、と説明しています。

FILTER 句で書いた条件付き集計(PostgreSQL・SQLite)
SELECT member_id,
       SUM(amount) FILTER (WHERE ordered_on >= '2026-11-01') AS nov,
       COUNT(*)    FILTER (WHERE amount >= 2000)             AS big_cnt
FROM orders
GROUP BY member_id
ORDER BY member_id;
member_idnovbig_cnt
1NULL1
225001
315001
56000

結果は SUM(CASE WHEN ... THEN amount END)・COUNT(CASE WHEN ... THEN 1 END) と同じです。MySQL 8.4.13 では同じクエリが ERROR 1064 (42000): You have an error in your SQL syntax になりました。

CASE 式FILTER 句
使える製品(手元で確認)PostgreSQL・MySQL・SQLitePostgreSQL・SQLite
書き方集約関数の引数の中で値を切り替える集約関数の後ろで入力行を絞る
該当なしの結果ELSE なしなら NULL、ELSE 0 なら 0SUM は NULL、COUNT は 0
向いている場面複数の製品で動かす SQL、値そのものを変えたいとき「この行を集計に入れるか」だけを決めたいとき

もう1つ、短く書ける方法として、MySQL では SUM(amount >= 2000) のように比較の結果(真なら 1、偽なら 0)を直接足せます。手元の MySQL 8.4.13 と SQLite 3.54.0 では 3 が返りましたが、PostgreSQL 18.6 では function sum(boolean) does not exist のエラーになりました。製品をまたぐなら SUM(CASE WHEN ... THEN 1 ELSE 0 END) か COUNT(CASE WHEN ... THEN 1 END) で書くのが無難です。

ORDER BY で任意の並び順にする

ORDER BY に CASE 式を書くと、値に並び順の番号を割り当てて、アルファベット順でも数値順でもない任意の順番で並べられます。フォローが必要な休止中の会員を先頭に、次に有効、未設定、退会を最後にしてみます。

ステータスを任意の順番で並べる
SELECT id, name, status
FROM members
ORDER BY CASE status
           WHEN 'S' THEN 1
           WHEN 'A' THEN 2
           WHEN 'X' THEN 4
           ELSE 3
         END,
         id;
idnamestatus
3会員CS
1会員AA
2会員BA
5会員ENULL
4会員DX

ここでも ELSE が効いています。status が NULL の会員E は単純 CASE のどの WHEN にも一致しないので ELSE 3 になり、有効と退会の間に入っています。ELSE を書かなければ並び順の値が NULL になり、NULL を先頭に置くか末尾に置くかは製品の既定に任されます(NULL の並び順の製品差は SQL の NULL の扱い方 を参照)。また、同じ順番の値の中での並びは決まらないので、最後に id を足して順番を固定しています。

NOTE

MySQL には並び順の指定によく使われる FIELD() 関数もあります。手元の MySQL 8.4.13 で ORDER BY FIELD(status, 'S', 'A', 'X'), id と書いたところ、リストにない NULL の会員E が先頭に来ました。NULL やリスト外の値の位置まで明示したいなら、ELSE を書ける CASE 式のほうが意図が読み取りやすくなります。

CASE 式は値を返すだけなので、ASC・DESC というキーワードそのものは切り替えられません。SQL Server のドキュメントには、降順用と昇順用に2つの CASE 式を並べ、条件に合わない側を NULL にして向きを切り替える例が載っています。

WHERE・HAVING で使う

WHERE にも CASE 式を書けます。数量が 0 の行は単価を 0 とみなし、単価が 1000 以上の注文を探してみます。

WHERE で CASE 式を使う
SELECT id, member_id, amount, quantity
FROM orders
WHERE CASE WHEN quantity = 0 THEN 0 ELSE amount / quantity END >= 1000
ORDER BY id;
idmember_idamountquantity
102130003
105315001
106340003

ただし、WHERE の CASE 式は、多くの場合 AND・OR だけで書き直せます。上の条件は quantity <> 0 AND amount / quantity >= 1000 と同じ結果になりました(PostgreSQL 18.6 と SQLite 3.54.0 で、どちらも 102・105・106 の3行)。普通の条件で書けるならそのほうが読みやすく、後で見るようにインデックスも使われやすくなります。WHERE で CASE 式が本当に必要になるのは、評価の順番を強制したい場合です(評価順序の節で扱います)。

HAVING では、集約関数の中に CASE 式を書いて、グループを絞り込む条件にできます。11月の売上が 1000 以上ある会員だけを残します。

HAVING で CASE 式を使う
SELECT member_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
GROUP BY member_id
HAVING SUM(CASE WHEN ordered_on >= '2026-11-01' THEN amount ELSE 0 END) >= 1000
ORDER BY member_id;
member_idcnttotal
223300
325500

cnt と total は全期間の値のまま、絞り込みの条件にだけ11月の売上を使っています。WHERE ordered_on >= '2026-11-01' で絞ると全期間の集計が出せなくなるので、こうした「集計はすべての行で、条件は一部の行で」という場面で CASE 式が役立ちます。

UPDATE で複数の行を別々の値に1文で書き換える

UPDATE の SET に CASE 式を書くと、行ごとに違う値を1つの文で設定できます。たとえば、全会員のプランを free と pro で入れ替えます。

free と pro を1文で入れ替える
UPDATE members
SET plan = CASE plan WHEN 'free' THEN 'pro' WHEN 'pro' THEN 'free' END;
 
SELECT id, plan FROM members ORDER BY id;
idplan
1free
2pro
3free
4pro
5pro

UPDATE members SET plan = 'pro' WHERE plan = 'free' と UPDATE members SET plan = 'free' WHERE plan = 'pro' を順に実行すると、1文目で pro になった行が2文目で free に戻ってしまいます。1文の CASE 式なら、各行の更新前の plan を見て新しい値を決めるので、入れ替えが正しくできます。

ELSE も WHERE もないと、他の行まで書き換わる

UPDATE の CASE 式でいちばん危険なのが、ELSE の書き忘れです。注文 104 と 106 の数量だけを直すつもりで、次のように書いたとします。

ELSE も WHERE もない UPDATE(危険)
UPDATE orders
SET quantity = CASE id WHEN 104 THEN 1 WHEN 106 THEN 4 END;

WHERE がないので、この文は全行を更新対象にします。104 と 106 以外の行は CASE 式のどの WHEN にも当てはまらず、ELSE もないので、新しい値は NULL です。今回は quantity に NOT NULL 制約があるため、3製品ともエラーになって何も更新されませんでした。

エラー(PostgreSQL 18.6)
ERROR:  null value in column "quantity" of relation "orders" violates not-null constraint
DETAIL:  Failing row contains (101, 1, 2026-09-12, 1200, null).
エラー(MySQL 8.4.13)
ERROR 1048 (23000): Column 'quantity' cannot be null

制約がない列なら、エラーにならずに他の行が NULL で上書きされます。status には NOT NULL 制約がないので、「60歳以上を退会扱いにする」つもりで次の文を実行すると、こうなります。

NOT NULL 制約のない列で ELSE を忘れた UPDATE
UPDATE members SET status = CASE WHEN age >= 60 THEN 'X' END;
 
SELECT id, age, status FROM members ORDER BY id;
idagestatus
125NULL
234NULL
347NULL
461X
5NULLNULL

会員A〜C の「有効」「休止」のステータスが消えてしまいました。エラーにならないので、気付くのは後になってからです。防ぎ方は2つあり、両方を書いておくと確実です。

  • 更新したい行だけを WHERE で絞る
  • ELSE 列名 を書いて、どの WHEN にも当てはまらない行は元の値のままにする
WHERE で対象行を絞った UPDATE
UPDATE orders
SET quantity = CASE id WHEN 104 THEN 1 WHEN 106 THEN 4 ELSE quantity END
WHERE id IN (104, 106);
 
SELECT id, quantity FROM orders ORDER BY id;

3製品とも、104 の数量が 1、106 が 4 になり、他の行は元のままでした。CASE 式を使う UPDATE は、実行前に同じ WHERE と CASE 式で SELECT して、新しい値を確かめてから流すと安全です。トランザクションの中で実行して結果を確認してから確定する方法は トランザクションと ACID の記事 も参考になります。

0 除算を避ける - NULLIF と CASE

注文 104 は quantity が 0 なので、単価 amount / quantity を計算すると 0 除算になります。分母が 0 のときに NULL を返すようにするには、NULLIF か CASE 式を使います。

NULLIF と CASE で 0 除算を避ける
SELECT id, amount, quantity,
       amount / NULLIF(quantity, 0)                                AS unit_price,
       CASE WHEN quantity = 0 THEN NULL ELSE amount / quantity END AS unit_price_case
FROM orders
ORDER BY id;
idamountquantityunit_priceunit_price_case
10112002600600
1023000310001000
1038001800800
10425000NULLNULL
1051500115001500
1064000313331333
1076002300300

NULLIF(a, b) は、a と b が等しければ NULL を、そうでなければ a を返す関数です。MySQL のリファレンスは、NULLIF(expr1, expr2) は CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END と同じだと説明しています。分母を NULLIF(quantity, 0) で包むと、0 のときだけ NULL で割ることになり、結果が NULL になります。

NULLIF の基本や、空文字を NULL にそろえる使い方は SQL の NULL の扱い方 の「COALESCE・NULLIF」で扱っています。ここでは、CASE 式と組み合わせるときに知っておきたい製品差を2つ補足します。

0 除算そのものの扱いが製品で違う

NULLIF を付けずに amount / quantity をそのまま計算すると、製品ごとに結果が分かれました。

製品注文 104 の amount / quantity
PostgreSQL 18.6ERROR: division by zero(クエリ全体が失敗)
MySQL 8.4.13NULL(SELECT 2500 / 0 の後の SHOW WARNINGS には Warning 1365 Division by 0 が出た)
SQLite 3.54.0NULL(エラーも警告もなし)

MySQL と SQLite では、0 除算がエラーにならずに NULL になるため、問題に気付きにくい点に注意が必要です。どの製品でも同じ結果にしたいなら、分母を NULLIF で包むか、CASE 式で分岐しておきます。NULL ではなく 0 を表示したいなら、COALESCE(amount / NULLIF(quantity, 0), 0) のように外側を包みます。

整数どうしの割り算の結果も製品で違う

上の表で、注文 106 の単価は 4000 / 3 = 1333 になっています。これは PostgreSQL 18.6 と SQLite 3.54.0 の結果で、整数どうしの割り算は小数点以下が切り捨てられます。MySQL 8.4.13 では同じクエリが 1333.3333 を返し、他の行も 600.0000 のように小数付きで表示されました。

小数で計算したいなら、PostgreSQL と SQLite では amount * 1.0 / NULLIF(quantity, 0) や CAST(amount AS NUMERIC) のように、どちらかを小数の型にしてから割ります。CASE 式の分岐ごとに型が違う値を返すと、後で見る型の問題も起きるので、割り算の型は先にそろえておくのが安全です。

COALESCE・NULLIF・GREATEST・LEAST・IF・iif との関係

CASE 式で書ける処理のうち、よく使う形には専用の関数があります。どれも「CASE 式の省略形」として理解できます。

関数CASE 式で書くと使える製品
COALESCE(a, b)CASE WHEN a IS NOT NULL THEN a ELSE b ENDPostgreSQL・MySQL・SQLite
NULLIF(a, b)CASE WHEN a = b THEN NULL ELSE a ENDPostgreSQL・MySQL・SQLite
IF(条件, x, y)CASE WHEN 条件 THEN x ELSE y ENDMySQL、SQLite(3.48.0 以降の別名)
iif(条件, x, y)CASE WHEN 条件 THEN x ELSE y ENDSQLite(3.32.0 以降)、SQL Server の IIF
GREATEST(a, b) / LEAST(a, b)大きい方・小さい方を返す CASEPostgreSQL・MySQL(NULL の扱いが違う)

PostgreSQL のドキュメントは、COALESCE について「CASE 式と同じように、結果を決めるのに必要な引数だけを評価する。つまり最初の NULL ではない引数より右の引数は評価されない」と書いています。

IF() と iif() - NULL は「偽」の側に落ちる

MySQL の IF(expr1, expr2, expr3) は、expr1 が真(expr1 <> 0 かつ expr1 IS NOT NULL)なら expr2 を、そうでなければ expr3 を返します。SQLite の iif(x, y, z) は、ドキュメントによると CASE WHEN x THEN y ELSE z END と論理的に同じで、同じバイトコードを生成します。SQL Server の IIF も、ドキュメントで「CASE 式の省略形」とされ、内部で CASE に変換されるため入れ子の上限も CASE と同じ10階層だと書かれています。

IF()(MySQL)と CASE 式
SELECT id, age,
       IF(age >= 40, '40歳以上', '40歳未満')                   AS if_func,
       CASE WHEN age >= 40 THEN '40歳以上' ELSE '40歳未満' END AS case_expr
FROM members
ORDER BY id;

3製品とも(PostgreSQL は IF() を除く)、会員A・B は「40歳未満」、会員C・D は「40歳以上」になりました。

注意したいのは、年齢が未回答の会員E も「40歳未満」になったことです。age >= 40 は NULL(UNKNOWN)なので真ではなく、IF() も CASE 式も「偽の側」(ELSE)を返します。2択の関数では NULL の行が黙ってどちらかに入るので、NULL がありうる列では CASE 式で WHEN age IS NULL THEN '未回答' の分岐を先に書くほうが安全です。

SQLite の iif() は、3.48.0 で2引数の形と if() という別名が、3.49.0 で任意の数の引数を取れる形が追加されました(SQLite の変更履歴より)。引数は「条件, 値」の組で並べ、最初に真になった条件の値を返します。

SQLite の if() に条件を複数並べる(SQLite 3.49.0 以降)
SELECT id, age,
       if(age >= 60, '60歳以上', age >= 40, '40歳以上', '40歳未満') AS if_multi
FROM members
ORDER BY id;

結果は会員D が「60歳以上」、会員C が「40歳以上」、それ以外(年齢が NULL の会員E を含む)が「40歳未満」でした。

PostgreSQL 18.6 には IF() も iif() もなく、どちらも function ... does not exist のエラーになりました。複数の製品で動かす SQL では、CASE 式で書いておくのがいちばん確実です。

GREATEST・LEAST は NULL の扱いが製品で逆

複数の値から最大・最小を選ぶ GREATEST・LEAST は、引数に NULL があるときの結果が製品で違います。

式PostgreSQL 18.6MySQL 8.4.13SQLite 3.54.0
GREATEST(10, NULL, 30)30NULL関数なし(max(10, NULL, 30) は NULL)
LEAST(10, NULL, 30)10NULL関数なし(min(10, NULL, 30) は NULL)

PostgreSQL のドキュメントは、引数の NULL は無視され、すべてが NULL のときだけ結果が NULL になるとしたうえで、「これは SQL 標準からの逸脱で、標準ではいずれかの引数が NULL なら NULL を返す。他のいくつかのデータベースはこのように振る舞う」と書いています。MySQL のリファレンスは「GREATEST() はいずれかの引数が NULL なら NULL を返す」と明記しています。SQLite には GREATEST・LEAST という名前の関数はなく、複数の引数を取る max()・min() が同じ役割を持ちます(ドキュメントでは、複数引数の max() はいずれかの引数が NULL なら NULL を返すとされ、手元では min() も NULL を返しました)。

NULL の扱いを製品に依存させたくなければ、COALESCE で NULL を先に埋めるか、CASE 式で比較を書きます。

結果の型の決まり方とエラー

CASE 式は1つの値を返すので、THEN と ELSE が返す値は1つの型にそろう必要があります。数値を返す分岐と文字列を返す分岐を混ぜると、製品によって結果が分かれます。

数値と文字列を混ぜた CASE
SELECT id,
       CASE WHEN age IS NULL THEN '未回答' ELSE age END AS age_label
FROM members
ORDER BY id;

MySQL 8.4.13 と SQLite 3.54.0 は、エラーにならずに次の結果を返しました。

idage_label
125
234
347
461
5未回答

PostgreSQL 18.6 では、同じクエリがエラーになります。

エラー(PostgreSQL 18.6)
ERROR:  invalid input syntax for type integer: "未回答"

PostgreSQL - 1つの型を決めてから変換する

PostgreSQL のドキュメントは、CASE 式について「すべての結果の式のデータ型は、1つの出力型に変換できなければならない」とし、詳細を型変換の章(UNION・CASE などの型解決)に譲っています。そこに書かれている規則のうち、CASE でつまずきやすいのは次の点です。

  • すべての入力が同じ型ならその型になる
  • 型が決まっていない文字列リテラル('未回答' のように型を付けていないもの)は、他に型の決まった入力があれば、型の決定では無視され、最後にその型へ変換される
  • 型の決まった入力どうしが、数値と文字列のように別のカテゴリなら失敗する
  • 型の候補は左から順に検討するが、CASE は歴史的な理由で ELSE 句を「最初」の入力として扱う

上の例では、'未回答' は型の決まっていないリテラルなので、age の integer 型に合わせられ、そこで '未回答' を整数に変換できずにエラーになりました。同じ形でも THEN '0' のように整数に変換できる文字列なら、エラーにならず 0 が返ります。

型の決まった値どうしを混ぜると、型の組み合わせ自体がエラーになります。

text と integer を混ぜた CASE(PostgreSQL)
SELECT CASE WHEN age > 30 THEN 1 ELSE 'abc'::text END FROM members;
エラー(PostgreSQL 18.6)
ERROR:  CASE types text and integer cannot be matched

ELSE の text が先、THEN の integer が後の順でメッセージに出ているのは、上で見た「ELSE を最初の入力として扱う」規則のとおりです。

数値どうしなら、より広い型に寄せられます。手元の PostgreSQL 18.6 では、CASE WHEN true THEN 1 ELSE 2.5 END の型(pg_typeof)は numeric でした。integer の 1 も numeric として返ります。

MySQL・SQLite - エラーにならない代わりに型が変わる

MySQL のリファレンスは、CASE 式の戻り値の型を「すべての結果の値を集約した型」とし、すべて数値なら数値、すべて文字列なら VARCHAR、それ以外の組み合わせは VARCHAR、などの規則を挙げています。手元で CREATE TABLE ... AS SELECT で型を確かめると、上の age_label は varchar(11)、CASE WHEN true THEN 1 ELSE 2.5 END は decimal(2,1) になりました。

SQLite は値ごとに型を持つので、同じ列の中に integer と text が混ざります(typeof() で見ると、会員A〜D の行は integer、会員E の行は text でした)。

どちらもエラーにはなりませんが、数値の列が文字列になると、並べ替えが数値順ではなく文字列順になったり、アプリケーション側で受け取る型が変わったりします。どの製品でも、分岐ごとの型は自分でそろえるのが安全です。表示用に数値を文字列にしたいなら、明示的に変換します。

型をそろえて書く
-- PostgreSQL・SQLite
SELECT id, CASE WHEN age IS NULL THEN '未回答' ELSE CAST(age AS TEXT) END AS age_label
FROM members;
 
-- MySQL(CAST の型に TEXT は使えないので CHAR を使う)
SELECT id, CASE WHEN age IS NULL THEN '未回答' ELSE CAST(age AS CHAR) END AS age_label
FROM members;

MySQL 8.4.13 で CAST(age AS TEXT) と書くと構文エラー(ERROR 1064)になりました。逆に PostgreSQL で CAST(age AS CHAR) と書くと長さ1の文字型になるので、この用途には使えません。キャストの書き方は製品ごとに確かめてから使います。

NOTE

SQL Server のドキュメントには、戻り値の型は結果の式と ELSE の式の型のうち「優先順位がいちばん高い型」になる、と書かれています。また、THEN・ELSE のすべてが NULL 定数だとエラー 8133 になるので、少なくとも1つは NULL 定数以外にする必要があるとされています。

評価順序と短絡評価の注意

CASE 式は、上から順に条件を調べ、結果が決まったら残りを評価しない、と説明されることが多い機能です。PostgreSQL のドキュメントも「CASE 式は、結果を決めるのに必要のない部分式を評価しない」と書いています。SQLite のドキュメントも「どちらの形の CASE 式も、遅延評価(短絡評価)を行う」としています。

この性質を使うと、危険な計算を分岐で守れます。

WHERE の評価順を CASE で固定する

PostgreSQL のドキュメントは、式の部分式の評価順序は定義されておらず、WHERE の AND・OR の組み合わせはブール代数の規則が許す範囲で自由に並べ替えられる、と書いています。そのため、WHERE x > 0 AND y/x > 1.5 は 0 除算を避ける書き方として信頼できないとし、評価の順番を強制したいときは WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END のように CASE 式を使う方法を示しています。

ただし同じ箇所で、CASE をこのように使うと最適化を妨げるので、必要なときだけにすべきだとも書かれています(この例では y > 1.5*x と書き直すほうがよい、としています)。前の節の 0 除算の例なら、分母を NULLIF で包むのがいちばん簡単です。

NOTE

手元の PostgreSQL 18.6 と SQLite 3.54.0 では、WHERE quantity <> 0 AND amount / quantity >= 1000 は 0 除算のエラーにならず、3行を返しました。しかし PostgreSQL のドキュメントのとおり、これは評価の順番が保証された結果ではありません。データ量や実行計画が変わったときにも安全とは言えないので、順番に依存する条件は CASE か NULLIF で書きます。

CASE 式の「必要な部分だけを評価する」性質には、ドキュメントに書かれた例外が2つあります。

定数式は先に評価されることがある(PostgreSQL)

PostgreSQL のドキュメントは、CASE は定数の部分式の早期評価を防げない、と注意しています。IMMUTABLE な関数や演算子は、実行時ではなくクエリの計画時に評価されることがあるためです。

実行されないはずの分岐にある 1/0
SELECT id,
       CASE WHEN quantity >= 0 THEN quantity ELSE 1/0 END AS q
FROM orders
WHERE id = 101;

quantity は 0 以上なので、ELSE 1/0 に入る行はありません。それでも PostgreSQL 18.6 では ERROR: division by zero になりました。プランナが定数の 1/0 を前もって計算しようとするからです。MySQL 8.4.13 と SQLite 3.54.0 では、q に 2 が返りました。

ドキュメントは、関数の中で実行されるクエリでは、引数やローカル変数の値が計画のために定数として埋め込まれることがあるので、明らかに定数に見えない式でも同じことが起こりうる、とも書いています。PL/pgSQL の関数の中で危険な計算を守るなら、CASE 式ではなく IF-THEN-ELSE 文を使うほうがはるかに安全だ、としています。

集約関数は CASE より先に計算される

もう1つの例外が集約関数です。PostgreSQL のドキュメントは「集約式は SELECT リストや HAVING 句の他の式より先に計算されるので、CASE はその中に含まれる集約式の評価を防げない」と書いています。

最小値で守ったつもりの平均単価
SELECT CASE WHEN MIN(quantity) > 0
            THEN AVG(amount / quantity)
       END AS avg_unit
FROM orders;

「数量の最小値が 0 より大きいときだけ平均単価を出す」つもりですが、MIN(quantity) と AVG(amount / quantity) は、どちらも全行を読みながら同時に計算されます。注文 104 の amount / quantity を計算した時点で 0 除算が起き、MIN の結果を調べる前にエラーになります。PostgreSQL 18.6 では ERROR: division by zero でした(MySQL 8.4.13 と SQLite 3.54.0 では、0 除算が NULL になるのでエラーにならず、MIN(quantity) が 0 なので結果は NULL でした)。

SQL Server のドキュメントにも同じ趣旨の注意があり、WHEN に書いた集約式は先に評価されてから CASE 式に渡されるので、WHEN の評価順序に頼ってよいのはスカラー式(スカラーを返す相関のないサブクエリを含む)だけで、集約式には頼るべきではない、としています。

PostgreSQL のドキュメントが勧める対処は、問題のある行が集約関数にそもそも入らないように、WHERE か FILTER 句で絞ることです。集約関数の中に CASE 式を書く方法でも、行ごとの計算を守れます。

集約関数の中で行ごとに守る
-- FILTER 句(PostgreSQL・SQLite)
SELECT AVG(amount / quantity) FILTER (WHERE quantity > 0) AS avg_unit FROM orders;
 
-- CASE 式(3製品)
SELECT AVG(CASE WHEN quantity > 0 THEN amount / quantity END) AS avg_unit FROM orders;

PostgreSQL 18.6 では、どちらも 922.1666666666666667 を返しました(SQLite 3.54.0 は浮動小数点の 922.16666666666663、MySQL 8.4.13 は割り算が小数で計算されるため 922.22222222)。CASE で守れるのは「1行ずつの計算」であり、「集約の結果を見てから集約するかどうかを決める」ことはできないと覚えておくと整理しやすくなります。

NOTE

MySQL のリファレンスの CASE 式の説明には、評価しない分岐についての記述は見当たりませんでした。一方で NULLIF() については「引数が等しくない場合、MySQL は expr1 を2回評価する」という注意が書かれています。NULLIF の第1引数に、重い計算や副作用のある関数を書くときは気を付けます。

WHERE の CASE とインデックス

WHERE に書いた CASE 式は、インデックスを使った絞り込みを妨げることがあります。PostgreSQL 18.6 で、20 万行のテーブル big の member_id 列と amount 列にインデックスを作り、実行計画を比べました。

普通の条件と、同じ意味の CASE 式(PostgreSQL)
EXPLAIN (COSTS OFF) SELECT * FROM big WHERE member_id = 42;
 
EXPLAIN (COSTS OFF)
SELECT * FROM big WHERE CASE WHEN member_id = 42 THEN true ELSE false END;
実行計画(PostgreSQL 18.6)
 Bitmap Heap Scan on big
   Recheck Cond: (member_id = 42)
   ->  Bitmap Index Scan on big_member
         Index Cond: (member_id = 42)
 
 Seq Scan on big
   Filter: CASE WHEN (member_id = 42) THEN true ELSE false END

普通の条件ではインデックス(big_member)で該当行だけを読んでいますが、CASE 式で包んだ条件はテーブル全体を読み(Seq Scan)、1行ずつ CASE 式を評価して絞っています。CASE member_id WHEN 42 THEN 1 ELSE 0 END = 1 のように単純 CASE で書いても、同じく全件走査になりました。

MySQL 8.4.13(5000 行のテーブル)と SQLite 3.54.0 でも、同じ傾向でした。

条件MySQL 8.4.13 の EXPLAINSQLite 3.54.0 の EXPLAIN QUERY PLAN
member_id = 42type: ref、key: big_memberSEARCH big USING INDEX big_member (member_id=?)
CASE WHEN member_id = 42 THEN 1 ELSE 0 END = 1type: ALL、key: NULLSCAN big

インデックスは「列の値」で並んでいるので、列を式で包むと、その式の結果で探すことはできません。関数で列を包むとインデックスが効かなくなるのと同じ理由で、詳しくは データベースインデックス入門 で扱っています。

列を含まない CASE は先に計算される

一方、CASE 式が列を含まず定数だけで決まる場合は、PostgreSQL 18.6 では計画時に計算されて普通の条件になりました。psql の変数で WHERE CASE WHEN :'kind' = 'big' THEN amount >= 99990 ELSE amount < 10 END と書き、kind に 'big' を渡すと、実行計画は Index Cond: (amount >= 99990) の Bitmap Index Scan on big_amount になりました。前の節の「定数の部分式は計画時に評価されることがある」性質の良い面です。プリペアドステートメントのパラメータのように、計画時に値が決まらない場合も同じになるかは確認していません。

書き換えと式インデックス

列を含む CASE 式で絞る必要があるなら、次の方法を検討します。

  • AND・OR に書き換える。CASE WHEN status = 'X' THEN amount >= 99990 ELSE member_id = 42 END は全件走査でしたが、(status = 'X' AND amount >= 99990) OR (status <> 'X' AND member_id = 42) に書き換えると、PostgreSQL 18.6 は2つのインデックスを BitmapOr で組み合わせて使いました。ただし status が NULL の行は、CASE 版では ELSE 側に入るのに対し、書き換え版ではどちらの条件も真にならないので、NULL がありうる列では意味が変わらないか確認が必要です
  • 式インデックスを作る。PostgreSQL では CREATE INDEX big_band ON big ((CASE WHEN amount >= 99990 THEN 'top' ELSE 'other' END)) のように CASE 式そのものにインデックスを作れます。WHERE に同じ CASE 式を書いたクエリは Index Scan using big_band になりました

式インデックスは、区分の境界を変えたら作り直しになります。まずは CASE を使わない条件に書き換えられないかを考えるのが先です。

ストアドプロシージャの CASE 文との違い

ストアドプロシージャや関数の中には、名前の似た CASE 文があります。CASE 式が値を返すのに対し、CASE 文はどの処理を実行するかを切り替えます。

CASE 式CASE 文(MySQL のストアドプログラム、PL/pgSQL)
何を返すか1つの値何も返さない。分岐した先の文を実行する
書ける場所SQL の式を書ける場所プロシージャ・関数の本体
終わり方ENDEND CASE
どれにも当てはまらず ELSE もないNULL を返すエラー

MySQL のリファレンスは、CASE 式(演算子)と CASE 文は構文が少し違い、CASE 文は ELSE NULL 句を持てず、END ではなく END CASE で終わる、と注意しています。そしていちばん大きな違いが、当てはまる分岐がないときの動作です。MySQL のリファレンスによると、WHEN のどれにも一致せず ELSE 句もない CASE 文は、Case not found for CASE statement のエラーになります。

ELSE のない CASE 文(MySQL のストアドプロシージャ)
DELIMITER //
CREATE PROCEDURE label_status(IN s CHAR(1))
BEGIN
  CASE s
    WHEN 'A' THEN SELECT '有効' AS label;
    WHEN 'S' THEN SELECT '休止' AS label;
  END CASE;
END//
DELIMITER ;
 
CALL label_status('A');  -- 有効
CALL label_status('X');  -- エラー
エラー(MySQL 8.4.13)
ERROR 1339 (20000): Case not found for CASE statement

MySQL のリファレンスは、どの WHEN にも当てはまらない場合を扱うには、空の BEGIN ... END ブロックを持つ ELSE を書けばよい、と例を示しています。

PostgreSQL の PL/pgSQL の CASE 文も同じで、ドキュメントによると一致する分岐がなく ELSE もない場合は CASE_NOT_FOUND 例外が発生します。手元の PostgreSQL 18.6 では次のエラーになりました。

エラー(PostgreSQL 18.6、PL/pgSQL の CASE 文)
ERROR:  case not found
HINT:  CASE statement is missing ELSE part.

SQL の中の CASE 式なら黙って NULL になるところが、CASE 文ではエラーになります。プロシージャの中で SQL を組み立てるとき、2つを取り違えないように注意します。

よくあるエラーとつまずき

ここまでに出てきたものを、PostgreSQL 18.6・MySQL 8.4.13・SQLite 3.54.0 の結果でまとめます。

書いたものPostgreSQL 18.6MySQL 8.4.13SQLite 3.54.0対処
数値の列と文字列を返す分岐を混ぜるinvalid input syntax for type integer通る(VARCHAR になる)通る(行ごとに型が混ざる)CAST で型をそろえる
型の決まった text と integer を混ぜるCASE types text and integer cannot be matched通る通る同上
SUM(amount >= 2000)function sum(boolean) does not exist33SUM(CASE WHEN ... THEN 1 ELSE 0 END)
FILTER (WHERE ...)使えるERROR 1064(構文エラー)使えるSUM(CASE ...) で書く
IF() / iif()function ... does not existIF() は使える両方使える(if() は 3.48.0 以降)CASE 式で書く
実行されない分岐に 1/0division by zero通る通る定数の危険な式を書かない
CASE WHEN MIN(x) > 0 THEN AVG(y / x) ENDdivision by zeroNULLNULLFILTER か CASE を集約関数の中に
ELSE のない CASE 文case not foundERROR 1339(ストアドプログラムなし)ELSE を書く

エラーにならずに結果だけが変わるつまずきも、改めて並べておきます。

  • 範囲の条件の順番を間違えて、下の分岐に一度も届かない
  • WHEN NULL と書いて、NULL の行が ELSE に落ちる
  • NULL の行が IF() や2択の CASE で「偽の側」に入る
  • COUNT(CASE WHEN ... THEN 1 ELSE 0 END) で全件を数える
  • UPDATE で ELSE と WHERE を忘れ、他の行を NULL で上書きする
  • MySQL・SQLite で 0 除算が NULL になり、問題に気付かない
  • 整数どうしの割り算が、PostgreSQL・SQLite では切り捨てになる

どれもテストデータに NULL や境界値、0 が入っていないと見逃しやすいものです。CASE 式を書いたら、NULL の行・境界ちょうどの値・どの WHEN にも当てはまらない値で一度結果を確かめておくと安心です。

まとめ

  • CASE 式は、条件に応じて1つの値を返す「式」。値を書ける場所なら、SELECT・WHERE・GROUP BY・HAVING・ORDER BY・UPDATE の SET・JOIN の ON・関数の引数のどこにでも書ける。
  • 単純 CASE は1つの値と = で比べる形、検索 CASE は WHEN ごとに条件を書く形。単純 CASE で書けるものは検索 CASE でも書ける。
  • WHEN は上から順に評価され、最初に真になった分岐だけが返る。範囲の条件は「未満なら小さい順」「以上なら大きい順」に並べ、除外の条件は先頭に書く。
  • ELSE を省略すると NULL。NULL を含む比較は真にならないので、NULL の行は ELSE に落ちる。単純 CASE の WHEN NULL は一致しないので、WHEN 列 IS NULL と書く。
  • 条件付き集計は SUM(CASE WHEN 条件 THEN 列 END)。件数は COUNT(CASE WHEN 条件 THEN 1 END) か SUM(CASE WHEN 条件 THEN 1 ELSE 0 END) で、COUNT(... ELSE 0) は全件を数えてしまう。PostgreSQL・SQLite では FILTER 句でも書けるが、MySQL 8.4 は構文エラー。
  • ORDER BY の CASE 式で任意の並び順を作れる。NULL の行の位置も ELSE で決めておく。
  • UPDATE の CASE 式で複数の行を別々の値に1文で更新できる。ELSE 列名 と WHERE を書かないと、他の行が NULL で上書きされる。
  • 0 除算は a / NULLIF(b, 0) で避ける。0 除算そのものは PostgreSQL ではエラー、MySQL は警告付きの NULL、SQLite は NULL と製品で違う。
  • COALESCE・NULLIF・IF()・iif() は CASE 式の省略形として理解できる。GREATEST・LEAST は、PostgreSQL は NULL を無視し、MySQL は NULL を返す。
  • 分岐ごとの型は自分でそろえる。PostgreSQL は数値と文字列を混ぜるとエラーになり、MySQL・SQLite は通る代わりに型が変わる。
  • CASE は必要な分岐だけを評価するが、PostgreSQL では定数の部分式が計画時に評価されることがあり、どの製品でも集約関数は CASE より先に計算される。集約に入る行は WHERE・FILTER・集約関数の中の CASE で絞る。
  • WHERE で列を CASE 式で包むと、インデックスが使われにくくなる。AND・OR に書き換えられないかを先に考える。
  • ストアドプロシージャの CASE 文は、値を返さずに処理を切り替える別物で、当てはまる分岐がなく ELSE もないとエラーになる。

CASE 式が思った結果にならないときは、CASE 式だけを SELECT リストに出して、元の列の値と並べて見てみてください。どの行がどの分岐に入っているか、NULL の行がどこに落ちているかが見えると、条件の順番や ELSE の抜けにすぐ気付けます。

参考リンク

関連記事

SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意

SQL の UPSERT - ON CONFLICT・ON DUPLICATE KEY UPDATE・MERGE の書き方と同時実行の注意

約73分

「あれば更新、なければ挿入」を SQL で書く UPSERT を、同じ小さなサンプルデータに対する実行結果を並べて整理します。SELECT してから INSERT・UPDATE する書き方が同時実行で壊れる理由とユニーク制約が必要な理由、PostgreSQL の INSERT ... ON CONFLICT(DO NOTHING・DO UPDATE・EXCLUDED・ON CONSTRAINT・WHERE 付きの更新・部分インデックス・RETURNING と PostgreSQL 18 の OLD / NEW)、PostgreSQL 15 の MERGE と 17 で入った RETURNING・merge_action()・WHEN NOT MATCHED BY SOURCE、MySQL の INSERT ... ON DUPLICATE KEY UPDATE(非推奨の VALUES() と行エイリアス AS new、affected rows が 1・2・0 になる挙動、代入の順番、複数のユニークキーで別の行が更新される問題、AUTO_INCREMENT の欠番)、REPLACE と INSERT IGNORE の危険性、SQLite の ON CONFLICT と INSERT OR REPLACE の違い、SQL Server の MERGE と HOLDLOCK、2つのセッションを同時に動かしたときの挙動、1文の中で同じキーを2回更新したときのエラー、NULL がユニーク制約で重複扱いにならない問題と NULLS NOT DISTINCT、DO NOTHING で既存の行が返らない問題と回避策、一括 UPSERT、よくあるエラーまでを、PostgreSQL 18.6・MySQL 8.4.13・SQLite 3.54.0 の実行結果と各公式ドキュメントをもとにまとめます。

SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴

SQL のサブクエリの使い方 - IN・EXISTS・相関サブクエリ・LATERAL と NOT IN の落とし穴

約58分

SQL のサブクエリ(副問い合わせ)を、同じ小さなサンプルデータに対する実行結果を並べて整理します。スカラーサブクエリと 0 行・複数行のときの挙動、IN と JOIN の違い、EXISTS と NOT EXISTS、NOT IN に NULL が混ざると 0 行になる罠と回避方法、相関サブクエリ、FROM 句のサブクエリ(派生テーブル)と別名の要否、ANY・SOME・ALL と空集合、LATERAL と LEFT JOIN LATERAL、JOIN・CTE との使い分け、UPDATE・DELETE での注意、セミジョイン・アンチジョインへの変換と PostgreSQL の EXPLAIN での見え方、MySQL 8.4 の semijoin・antijoin 最適化、よくあるエラーまでを、各公式ドキュメントを一次ソースにまとめます。

SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序

SQL の GROUP BY と HAVING の使い方 - WHERE との違い・集約関数・論理的な処理順序

約54分

SQL の GROUP BY と HAVING を、同じ小さなサンプルデータに対する実行結果を並べて整理します。COUNT(*) と COUNT(列) と COUNT(DISTINCT 列) の違い、SUM・AVG・MIN・MAX と NULL、WHERE と HAVING の違いと FROM・WHERE・GROUP BY・HAVING・SELECT・ORDER BY の論理的な処理順序、SELECT の別名を GROUP BY や HAVING で使えるかの製品差、PostgreSQL の関数従属と MySQL の ONLY_FULL_GROUP_BY、SQLite の bare column、LEFT JOIN で 0 件のグループを出す方法、FILTER 句と CASE 式による条件付き集計、ROLLUP・GROUPING SETS・CUBE と MySQL の WITH ROLLUP、ウィンドウ関数との違い、よくあるエラー、WHERE で先に絞る理由とインデックスまでを、各公式ドキュメントを一次ソースにまとめます。

楽天市場のおすすめPR / 楽天アフィリエイト