case … when … (simple)

Case is a conditional SQL expression: It searches for the first when clause whose value equals (=) that of the case clause and returns the value of the corresponding then clause—or that of else if nothing matches or null if there is no else clause.

SELECT CASE <expr> WHEN 0 THEN 'zero'
                   WHEN 1 THEN 'one'
                   ELSE 'not zero or one'
        END
  FROM …

Note that when null never matches. Use case when <expr> is null instead.

Related

• Tutorial: Case — Conditional Expressions

• Other conditional SQL expressions

  • case when … (searched)

  • coalesce(…, …[, …]*)

  • nullif(…, …)

  • case … when <op> … (extended)

  • case … when …, … (comma-separated)

• Product specific features: if, decode.

Normative References

The simple case expression is defined in ISO/IEC 9075-2:2023 as mandatory feature F261-01.