CASE

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

Return type

Same data type as <result>

Example

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

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: