CASE
Purpose
With the help of the CASE function, an IF THEN ELSE logic can be expressed within the SQL language.
Syntax
case::=
simple_case_expr::=
searched_case_expr::=
Usage Notes
- With the simple_case_expr, the expr is compared with the specified alternatives. The THEN part of the first match defines the result.
- With the searched_case_expr, the row is evaluated using all of the conditions until one equates to the TRUE value. The THEN part of this condition is the result.
- If none of the options apply, the ELSE value is returned. If this was not specified, the NULL value is returned.
Examples
SELECT name, CASE grade WHEN 1 THEN 'VERY GOOD'
WHEN 2 THEN 'GOOD'
WHEN 3 THEN 'SATISFACTORY'
WHEN 4 THEN 'FAIR'
WHEN 5 THEN 'UNSATISFACTORY'
WHEN 6 THEN 'POOR'
ELSE 'INVALID'
END AS GRADE FROM student;
Result
NAME | GRADE |
Fischer | VERY GOOD |
Schmidt | FAIR |
Result
NAME | CLASS |
Meier | STANDARD |
Huber | PREMIUM |