Link Search Menu Expand Document

CASE

The CASE expression is a 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. If the result is false any subsequent WHEN clauses (conditions) are searched 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;
Parameter Description
<condition> An expression that returns a boolean result. A condition can be defined for each WHEN, and ELSE clause.
<result> The result of any condition. Every THEN clause receives a single result. All results in a single CASE function must share the same data type.

Example

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

Movie Length
Citizen Kane 114
Happy Gilmore 82
Silence of the Lambs 110
The Godfather 150
The Jazz Singer 40
Tropic Thunder 90

The following example categorizes each entry by length. If the movie is longer than zero minutes and less than 50 minutes it is categorized as SHORT. When the length is 50-120 minutes, it’s categorized as Medium, and when even longer, it’s categorized as Long.

SELECT
	movie,
	length,
	CASE
		WHEN length > 0
		AND length <= 50 THEN 'Short'
		WHEN length > 50
		AND length <= 120 THEN 'Medium'
		WHEN length > 120 THEN 'Long'
	END duration
FROM
	movie_test
ORDER BY
	movie;

Returns:

Title Length duration
Citizen Kane 114 Medium
Happy Gilmore 82 Medium
Silence of the Lambs 110 Medium
The Godfather 150 Long
The Jazz Singer 40 Short
Tropic Thunder 90 Medium