ほとんどのレポートには、注文に大小のラベルを付ける、支払い済みの注文と保留中の注文を並べて数える、緊急品目を最初に表示するなど、何らかのロジックが必要です。 SQL CASE WHEN はこれらすべてをクエリ内で直接処理し、すべての主要なデータベースで機能します。

ここではすべてのクエリを SQLite 3.50 で実行し、表示された結果はその実行から得られたものです。 CASE は標準 SQL の一部であるため、これらの例では PostgreSQL、MySQL、SQL Server、Oracle で同じように動作します。

ケース・いつとは何ですか?

CASE は式であり、別個のコマンドではありません。条件を順番に評価して値を返すため、SELECT、WHERE、GROUP BY、ORDER BY、または関数内など、値が許可されている場所であればどこにでも使用できます。必ずENDで終わります。

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

サンプルテーブル

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

次数 6 には合計がないことに注意してください。その行は後で重要になります。

検索されたケース

各 WHEN には独自の完全な条件があるため、これはより柔軟な形式です。

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

最後の行を見てください。オーダー 6 には合計がありませんが、「小さい」というラベルが付けられています。 NULL との比較はどちらも true ではなかったので、ELSE に落ちました。これを修正する方法を NULL セクションで示します。常に AS を使用して結果に名前を付けてください。そうしないと、扱いにくい列名が付けられる可能性があります。

シンプルなケース

1 つの列を固定値と比較する場合は、短い形式の方が読みやすくなります。

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

注文事項

CASE は最初の true 条件で停止します。これは最も一般的なロジック バグです。前のクエリの 2 つの条件を交換します。

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

今では何も「大きく」なることはありません。合計 2500 は少なくとも 500 なので、最初のブランチが勝ち、2 番目のブランチには到達しません。エラーは表示されず、ラベルが間違っているだけです。範囲の場合は、最も制限的な条件を最初に指定するか、total >= 500 AND total < 1000 のように完全な範囲を記述します。

ELSE がない場合はどうなりますか?

上記の単純な CASE の例では、注文 5 がキャンセルされ、どの支店もそれを処理しなかったため、結果は NULL になりました。次のセクションで示すように、それがまさにあなたが望むことである場合もあります。ただし、ラベルを作成する場合は、レポートに空白を残すよりも、予期しない値が表示されるため、通常は明確な ELSE 'other' の方が適しています。

SUM を使用したピボット スタイルのレポート

これは、日常の SQL で最も役立つパターンの 1 つです。 CASE を集計でラップすると、行が列に変わり、顧客ごとに 1 行になります。

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

1 つのクエリで、別のクエリや結合を必要とせずに、支払われた収益と保留中の注文数の両方が得られるようになりました。このパターンは、一部のデータベースのみが提供する専用の PIVOT 構文とは異なり、ほぼすべてのデータベースで機能します。 GROUP BY が初めての場合は、SQL GROUP BY と HAVING をお読みください。

条件付きカウント

COUNT は NULL をスキップするため、ELSE のない CASE は一致する行のみをカウントします。

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

注意: COUNT の中に ELSE 0 を追加すると、0 は NULL ではないため、すべての行がカウントされます。 ELSE 0はSUMで使用し、COUNTは省略します。 PostgreSQL と SQLite も COUNT(*) FILTER (WHERE ...) をサポートしており、これは読みやすいですが、どこでも利用できるわけではありません。

CASEごとにグループ化する

独自のバケットを作成して、それらを要約することができます。

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

エイリアスによるグループ化は、SQLite、PostgreSQL、MySQL で機能します。 SQL Server ではこれが許可されていないため、式全体を繰り返すか、最初にクエリを CTE またはサブクエリに入れる必要があります。

ORDER BYによるカスタムソート

アルファベット順が企業にとって望まれることはほとんどありません。 CASE を使用すると、独自の順序を定義できます。

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

保留中の注文が最初に来るため、作業キューに便利です。 2 番目の並べ替え列 id は、各グループ内で行を予測可能な順序に保ちます。

更新中のケース

CASE を使用すると、1 つのステートメントで異なる行に異なる変更を適用できます。ここでは、少額の未決注文にレビュー用のフラグが付けられています。

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

ELSE status は他の行を変更しないままにします。 これを省略すると危険です。一致しない行は NULL に設定されるためです。 WHERE 句がない場合、このステートメントはすべての行にも影響するため、大規模なテーブルではフィルターを追加し、コミットする前に結果を確認できるように変更を トランザクション でラップすることを検討してください。次数 6 は、その NULL 合計が total < 500 を満たしていないため、変更されていないことにも注目してください。

NULLトラップ

NULL は CASE に関して 2 つの別々の問題を引き起こします。まず、単純な形式では等価性を使用して比較しますが、NULL と等しいものはなく、NULL 自体も同様です。

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

したがって、CASE col WHEN NULL は決して一致しません。代わりに IS NULL の検索フォームを使用してください。次に、順序 6 で見たように、NULL 値は比較をすり抜けて ELSE に到達します。修正するには、まずそれを確認します。

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

オーダー 6 には正直なラベルが付けられます。 SQL NULL 値 に関するガイドでは、この動作について完全に説明しています。

CASE と他のオプションの比較

最後の選択肢については一考の価値があります。 CASE が 30 個のブランチに成長し、5 つのクエリに出現する場合は、マッピングをテーブルに移動し、それに結合します。この場合、変更は 5 か所のコード変更ではなく、1 行の更新になります。

よくある間違い

• 条件の順序が間違っています。 広い条件が最初に狭い条件を隠します。
• 忘れています END. これは最も頻繁に起こる構文エラーです。
• WHEN NULLを使用します。 一致しないため、WHEN col IS NULLを使用します。
• COUNTの中にELSE 0を足します。 すべての行をカウントします。
• UPDATE の ELSE を省略します。 NULL で値を消去できます。
• WHEREのCASEによるフィルタリング。 インデックスの使用を停止できます。 SQL クエリの最適化 に関するガイドをご覧ください。

まとめ

CASE WHEN は、非常に多くの機能を実行する小さな機能です。 3 つのルールを覚えておいてください。最初の真の分岐が勝ち、一致しない行は ELSE なしで NULL になり、NULL には独自のチェックが必要です。練習するには、現在複数のクエリを使用して作成しているレポートを使用し、SUM(CASE ...) を使用してそれを 1 つのクエリとして作成してみます。

よくある質問

学び続ける