実行できるSQLiteドキュメント: CASE式

Q: SQLiteのCASE式とは何ですか？

CASE式はSQL版のif/elseのようなもので、条件を順番に評価して値を返します。ポイントは「文」ではなく「式」であること。つまり値を書ける場所ならどこでも使えるので、SELECTやWHERE、ORDER BY、UPDATE、さらには集計関数の中にも書けます。各分岐はWHEN 条件 THEN 値の形で書き、最後にELSEを付けるかどうかは任意です。

Q: 単純CASEと検索CASEの違いは？

単純CASEは1つの式を複数の値と比較する書き方です。例えばCASE status WHEN 'A' THEN ... WHEN 'B' THEN ... ENDのような形ですね。一方の検索CASEは分岐ごとに別々のboolean条件を書けます。CASE WHEN price > 100 THEN ... WHEN qty = 0 THEN ... ENDのような感じです。検索CASEのほうが柔軟で、複数のカラムや演算子、NULLチェックを自由に組み合わせられます。

Q: CASEとIIFはどう使い分ければいい？

IIF(条件, a, b)はCASE WHEN 条件 THEN a ELSE b ENDの短縮形です。2分岐で済むシンプルな条件ならIIFのほうがスッキリ書けます。ただし分岐が3つ以上になる場合や、上から順に評価される性質を活かしたいとき、WHEN col IS NULLのようにNULLを明示的に扱いたいときはCASEを使いましょう。

CASEはSQLにおけるif/else

CASEは、クエリの中に条件分岐を書くための仕組みです。WHENの条件を上から順にチェックし、最初にマッチしたもののTHENの値を返します。どれにもマッチしなかった場合はELSEの値が返り、ELSEを書かなかったときはNULLになります。

ポイントは 式（expression） という点です。CASEは値を返すので、値が書ける場所ならどこにでも置けます。SELECTの列、ORDER BYのキー、比較演算の右辺、関数の引数など、自由に使えます。

SELECT
    'リンゴ' AS item,
    CASE
        WHEN 5 > 3 THEN 'はい'
        ELSE 'いいえ'
    END AS bigger;

これで全体像が見えてきました。CASE から始まって、WHEN ... THEN ... を1つ以上、必要なら ELSE を挟んで、最後に END で締める。この END は省略不可で、書き忘れがいちばんよくあるタイポです。

実践的な例で見るCASE式

注文テーブル（orders）の各行を金額の大きさでラベル付けしたいとしましょう。試しながら動かせるように、小さなテーブルをその場で作ってみます。

WITH orders(id, total) AS (
    VALUES
        (1, 12.00),
        (2, 89.50),
        (3, 250.00),
        (4, 5.25),
        (5, 1200.00)
)
SELECT
    id,
    total,
    CASE
        WHEN total < 10 THEN '極小'
        WHEN total < 100 THEN '小'
        WHEN total < 500 THEN '中'
        ELSE '大'
    END AS size_bucket
FROM orders;

分岐は上から順にチェックされていきます。最初にマッチしたものが採用されるので、より具体的なものから一般的なものへという順番で並べるのがポイントです。ELSE はどの条件にも当てはまらなかった場合の受け皿で、これを書かないと 1200.00 は '大' ではなく NULL になってしまいます。

検索CASEと単純CASEの違い

ここまで見てきたのは「検索CASE」と呼ばれる書き方で、それぞれの WHEN に独立した真偽条件を書けるタイプです。一方、1つの式を複数の定数と比較するだけなら、もっと短く書ける「単純CASE」という形式があります。

WITH users(id, status) AS (
    VALUES
        (1, 'A'),
        (2, 'B'),
        (3, 'C'),
        (4, 'A')
)
SELECT
    id,
    status,
    CASE status
        WHEN 'A' THEN '有効'
        WHEN 'B' THEN '停止中'
        WHEN 'C' THEN '閉鎖'
        ELSE '不明'
    END AS status_label
FROM users;

CASE のあとに書いた式は一度だけ評価され、各 WHEN の値と = で比較されます。単一カラムに対する等価判定なら、こちらの書き方のほうがすっきりします。

ただし注意点が1つ。単純CASEは = で比較するので、SQLでは NULL = NULL が真になりません。もし status に NULL が入る可能性があるなら、'A'・'B'・'C' のどの分岐にもマッチせず、ELSE に流れてしまいます。NULL を明示的に扱いたい場合は、検索CASE（searched CASE）に切り替えて WHEN status IS NULL THEN ... と書きましょう。

ORDER BY で CASE を使う

ORDER BY には任意の式を書けるので、当然 CASE も使えます。アルファベット順や数値順では表現できない独自の並び順を指定したいときに便利です。

WITH tickets(id, priority) AS (
    VALUES
        (1, 'low'),
        (2, 'high'),
        (3, 'medium'),
        (4, 'high'),
        (5, 'low')
)
SELECT id, priority
FROM tickets
ORDER BY
    CASE priority
        WHEN 'high' THEN 1
        WHEN 'medium' THEN 2
        WHEN 'low' THEN 3
    END,
    id;

'high' < 'low' < 'medium' というアルファベット順になってしまい、トリアージにはまったく使えません。CASE で各優先度を数値にマッピングしてやれば、本当に欲しかった順序になります。末尾の , id は同点のときに安定した並びを保つためです。

WHERE句でCASEを使う

WHERE の中に CASE を書くこともできますが、たいていの場合は不要です。AND や OR をつなげたほうが読みやすいからです。CASE が真価を発揮するのは、条件そのもの が別の値によって変わるケースです。

WITH products(id, name, price, on_sale) AS (
    VALUES
        (1, 'マグカップ', 12.00, 1),
        (2, 'シャツ', 25.00, 0),
        (3, '帽子', 18.00, 1),
        (4, 'バッグ', 45.00, 0)
)
SELECT id, name, price
FROM products
WHERE price < CASE WHEN on_sale = 1 THEN 20 ELSE 30 END;

セール対象商品は20未満、通常商品は30未満が条件です。閾値そのものが条件によって変わるわけですね。CASEを使わなければ (on_sale = 1 AND price < 20) OR (on_sale = 0 AND price < 30) と書く羽目になります。結果は同じでも、ノイズが多くて読みにくい。

集計関数の中でCASEを使う

CASEが真価を発揮するのはここです。SUMやCOUNTと組み合わせれば、行のサブセットに対する集計を一度のスキャンで計算できます。SQL流の「条件に合うものだけ数える」テクニックです。

WITH orders(id, total, status) AS (
    VALUES
        (1, 50.00, 'paid'),
        (2, 30.00, 'pending'),
        (3, 75.00, 'paid'),
        (4, 20.00, 'cancelled'),
        (5, 100.00, 'paid')
)
SELECT
    COUNT(*) AS all_orders,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid_revenue
FROM orders;

CASEは条件に一致する行で1、それ以外で0を返すので、SUMがそのまま条件付きカウントになります。同じ手法は売上集計にも応用でき、一致する行ではtotalを、それ以外では0を返せばOKです。テーブルを1回スキャンするだけで、複数の条件付き集計を一気に計算できるわけです。

IIF：2分岐のショートカット

条件1つで結果が2択しかないケースなら、SQLiteにはIIF(cond, when_true, when_false)という関数が用意されています。これはCASE WHEN cond THEN when_true ELSE when_false ENDの純粋な省略記法です。

WITH accounts(id, balance) AS (
    VALUES
        (1, 100.00),
        (2, -25.00),
        (3, 0.00),
        (4, 1500.00)
)
SELECT
    id,
    balance,
    IIF(balance < 0, '残高不足', '正常') AS state
FROM accounts;

IIF は分岐が二択でワンライナーに収まるときに使うとスッキリ書けます。ただし、分岐が3つ以上になったり、NULL を別扱いにしたい、あるいは複数の WHEN を順番に評価させたいという場面では、素直に CASE に切り替えましょう。

ハマりやすい落とし穴

実際に書いていて引っかかりがちなポイントをいくつか挙げておきます。

END の書き忘れ。 CASE で開いたブロックは END で閉じる必要があります。SQLite のパースエラーは実際のミス箇所からかなり離れた場所で出ることが多いので注意してください。
ELSE がないと NULL になる。 どの WHEN にも一致せず、ELSE も書いていない場合、結果は NULL になります。それを意図しているならいいのですが、たいていは意図していないはずです。
分岐の順序が結果を左右する。 検索CASE では 最初に マッチした WHEN が採用されます。たとえば WHEN total < 500 を WHEN total < 100 より先に書くと、後者の分岐には永遠にたどり着けません。
型が混ざる問題。 各分岐が違う型を返してもSQLite自体は文句を言いませんが、その値を受け取る側のコードで困ることがあります。なるべく全分岐の戻り値の型を揃えておきましょう(全部テキスト、全部数値、など)。
単純CASE と NULL の相性。 前述のとおり、単純CASE は内部的に = で比較するため NULL には一致しません。NULL が絡む可能性があるなら検索CASE を使ってください。

次回:文字列関数

CASE は値によって分岐させるための仕組みでしたが、次の章では値そのものを 加工する 話に入ります。UPPER、LOWER、SUBSTR、REPLACE、LIKE パターンといった文字列関数は、テキストカラムの整形やクリーニングなど日常的な作業で大活躍します。次回をお楽しみに。

よくある質問

SQLiteのCASE式とは何ですか？

CASE式はSQL版のif/elseのようなもので、条件を順番に評価して値を返します。ポイントは「文」ではなく「式」であること。つまり値を書ける場所ならどこでも使えるので、SELECTやWHERE、ORDER BY、UPDATE、さらには集計関数の中にも書けます。各分岐はWHEN 条件 THEN 値の形で書き、最後にELSEを付けるかどうかは任意です。

単純CASEと検索CASEの違いは？

単純CASEは1つの式を複数の値と比較する書き方です。例えばCASE status WHEN 'A' THEN ... WHEN 'B' THEN ... ENDのような形ですね。一方の検索CASEは分岐ごとに別々のboolean条件を書けます。CASE WHEN price > 100 THEN ... WHEN qty = 0 THEN ... ENDのような感じです。検索CASEのほうが柔軟で、複数のカラムや演算子、NULLチェックを自由に組み合わせられます。

CASEとIIFはどう使い分ければいい？

IIF(条件, a, b)はCASE WHEN 条件 THEN a ELSE b ENDの短縮形です。2分岐で済むシンプルな条件ならIIFのほうがスッキリ書けます。ただし分岐が3つ以上になる場合や、上から順に評価される性質を活かしたいとき、WHEN col IS NULLのようにNULLを明示的に扱いたいときはCASEを使いましょう。

SQLite CASE式の使い方｜WHEN/THEN/ELSEとIIF