CASE

Reference material for CASE function

Conditional expression similar to if-then-else statements. If the result of the condition is true, then the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the result is not true, any subsequent WHEN clauses (conditions) are evaluated in the same manner. If no WHEN condition is true, then the value of the case expression is the result specified in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is NULL.

Syntax

CASE
    WHEN <condition> THEN <result>
    [ WHEN ...n ]
    [ ELSE <result> ]
END;

Parameters

Parameter	Description	Supported input types
<condition>	A condition can be defined for each WHEN clause.	BOOLEAN
<result>	The result of the CASE expression when the preceding condition holds. Every THEN clause receives a single result. All results in a single CASE expression must share the same data type.	Any

Return type

Same data type as <result>

Example

The following example categorizes each level in the levels table into a difficulty tier based on its level number:

SELECT level, name,
  CASE
    WHEN level <= 3 THEN 'Beginner'
    WHEN level <= 7 THEN 'Intermediate'
    ELSE 'Expert'
  END AS difficulty
FROM levels
ORDER BY level;

level int null	name text null	difficulty text
1	Thunderbolt Circuit	Beginner
2	Velocity Vale	Beginner
3	Raceway Ridge	Beginner
4	Nitro Narrows	Intermediate
5	Thunder Road	Intermediate
6	Burnout Boulevard	Intermediate
7	Speed Street	Intermediate
8	Racing Ravine	Expert
9	Drift District	Expert
10	Acceleration Alley	Expert

Rows: 10Execution time: 6.00ms