CASE — Conditional expression evaluation


image with no caption

Example Usage

CASE  WHEN  col IS NULL  THEN 'null'
      WHEN      NOT col  THEN 'false'  -- convert to logic value
      WHEN  NOT NOT col  THEN 'true'   -- convert to logic value
      ELSE   'unknown'   END


The CASE expression is similar to the C switch statement, or a series of if-then statements. The expression consists of an optional test expression followed by one or more WHEN...THEN clauses. The statement is finished with an optional ELSE clause and a required END keyword.

Each WHEN...THEN clause is evaluated in order. The first WHEN expression that is found to be equivalent to the test expression will cause the whole CASE expression to take the value of the corresponding return expression. If no equivalent WHEN expression is found, the default expression value is used. If no ELSE clause is provided, the default expression is assumed to be NULL.

If no test expression is given, the first WHEN expression that evaluates to true will be used.

Get Using SQLite now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.