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

ParameterDescriptionSupported 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

This example references a table player_level with the following columns and values:

playercurrent_level
kennethpark3
esimpson8
sabrina2111
rileyjon15
burchdenise4

The following example categorizes each player by level. If the level is higher than zero and less than 5 they are categorized as beginner. When the level is 6-12, they are categorized as intermediate, and when even higher, they are categorized as expert.

SELECT
	player,
	current_level,
	CASE
		WHEN current_level > 0 AND current_level <= 5 
			THEN 'Beginner'
		WHEN current_level > 5 AND current_level <= 12 
			THEN 'Intermediate'
		WHEN current_level > 12
			THEN 'Expert'
	END ranking
FROM
	player_level
ORDER BY
	player;

Returns:

playercurrent_levelranking
burchdenise4Beginner
esimpson8Intermediate
kennethpark3Beginner
rileyjon15Expert
sabrina2111Intermediate