Evaluates a list of conditions and returns one of multiple possible result expressions.

​
Synopsis

The following 2 syntaxes are supported:

CASE WHEN condition THEN result

     [WHEN ...]

     [...]

     [ELSE result]

END

Or

CASE expression

    WHEN value THEN result

    [WHEN ...]

    [ELSE result]

END

In the first syntax:

Each condition is an expression that returns a boolean result. If the condition result is true, then the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the condition result is false, then any subsequent WHEN clauses are examined in the same manner. If no WHEN condition result is true, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is omitted and no condition is true, the result is NULL.

In the second syntax:

The first expression is computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If no value matches the expression, the result of the ELSE clause is returned. If the ELSE clause is omitted and no condition is true, the result is NULL.

The data types of input expression and each value must be the same or compatible data type that can be implicitly converted. See Type Conversion.

The CASE() can be used in all the statements and their clauses that allow a valid expression. This includes the clauses: select_list, IN, WHERE, ORDER BY, and HAVING.

​
Examples

CASE command using the first syntax above:

SELECT *,

  CASE

    WHEN o_totalprice >= 1000000 THEN o_totalprice * 0.80

    WHEN o_totalprice >= 500000 THEN o_totalprice * 0.90

    WHEN o_totalprice >= 100000 THEN o_totalprice * 0.95

    ELSE o_totalprice

  END AS price_after_discount

FROM orders;

CASE command using the second syntax above:

SELECT c_name,

  CASE c_gender

    WHEN 'F' THEN 'Female'

    WHEN 'M' THEN 'Male'

    ELSE NULL

  END AS customer_gender

FROM customers;

Using CASE in the WHERE clause:

SELECT COUNT(*), SUM(o_totalprice), o_name

FROM orders, customers

WHERE o_custkey=c_custkey

AND CASE WHEN o_name='this is first expression'

           THEN o_date>'2020-01-01'

         WHEN o_name='this is the second expression'

           THEN o_date<'2020-01-01'

         ELSE o_date IS NULL

    END

GROUP BY o_name;