CASE

Purpose

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

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

SELECT name, CASE WHEN turnover>1000 THEN 'PREMIUM'
                                     ELSE 'STANDARD'
                                     END AS CLASS FROM customer;

Result