Introduction
CASE expression allows to evaluate a list of conditions and returns one of the possible results. The CASE expression has two formats.
Syntax
The following illustrates the simple CASE expression:
CASE expression WHEN expression_1 THEN result_1 WHEN expression_2 THEN result_2 WHEN expression_3 THEN result_3 … ELSE else_result END
CASE expression compares an expression to a set of expression (expression_1, expression_2, expression_3, …) using the equality operator (=). CASE statement returns the result_1, result_2, or result_3 if the expression matches the corresponding expression in the WHEN clause. If the expression does not match any expression in the WHEN clause, it returns the else_result in the ELSE clause. The ELSE clause is optional. If you omit the ELSE clause and the expression does not match any expression in the WHEN clause, the CASE expression returns NULL.
To use other comparison operators such as greater than (>), less than (<), etc., use below syntax.
CASE WHEN expression_1 THEN result_1 WHEN expression_2 THEN result_2 WHEN expression_3 THEN result_3 ELSE else_result END;
Boolean expression for each WHEN clause are evaluated in the order specified in the CASE expression. If the Boolean expression in each WHEN clause evaluates to true, the searched CASE statement returns the result in the corresponding THEN clause. It return else_result in the ELSE clause if none WHEN clause evaluate to true. If you omit the ELSE clause and no Boolean expression evaluates to true, the CASE expression returns a NULL value.
Example
Consider below table.
SELECT firstName, lastName, age,
CASE (age/10)
WHEN 1 THEN '10+ years'
WHEN 2 THEN '20+ years'
WHEN 3 THEN '30+ years'
WHEN 4 THEN '40+ years'
WHEN 5 THEN '50+ years'
WHEN 6 THEN '60+ years'
WHEN 7 THEN '70+ years'
END ageRange
FROM
employleinfo
/* Query Output */
firstName lastName age ageRange
Rahul Bhagwat 20 20+ years
Stephen Fleming 17 NULL
Sebastian Smith 19 NULL
New Name Sharma 50 50+ years
Erica Edwards 40 40+ years
Priya Gowda 50 50+ years
Shekar Chandra 43 NULL
Garima Singh 30 30+ years
Rahul Singh 19 NULL
SELECT firstName, lastName, age,
CASE
WHEN age < 25 THEN 'Less Than 25'
WHEN age >= 25 AND age <= 40 THEN 'Between 25 and 40'
WHEN age > 40 THEN 'Greater than 40'
END ageRange
FROM employleinfo
/* Query Output */
firstName lastName age ageRange
Rahul Bhagwat 20 Less Than 25
Stephen Fleming 17 Less Than 25
Sebastian Smith 19 Less Than 25
New Name Sharma 50 Greater than 40
Erica Edwards 40 Between 25 and 40
Priya Gowda 50 Greater than 40
Shekar Chandra 43 Greater than 40
Garima Singh 30 Between 25 and 40
Rahul Singh 19 Less Than 25