CASE

Purpose

With the help of the CASE function, an IF THEN ELSE logic can be expressed within the SQL language.

Syntax

case::=

Case

simple_case_expr::=

Simple Case Expr

searched_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
SELECT name, CASE WHEN turnover>1000 THEN 'PREMIUM'
                                     ELSE 'STANDARD'
                                     END AS CLASS FROM customer;
Result
NAME CLASS
Meier STANDARD
Huber PREMIUM