CASE

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;