CASE expression

A CASE expression is a conditional expression, i.e. an expression that contains conditions. Each condition is assigned an expression or the NULL value.
When the CASE expression is evaluated, the assigned expression value or NULL value is returned to whichever condition is true.

There are different types of CASE expression:

• CASE expression with search condition

• Simple CASE expression

• CASE expression with NULLIF

• CASE expression with COALESCE

• CASE expression with MIN or MAX

The syntax of the various types of expression is shown in the following overview:

---

case_expression ::=

{

	CASE WHEN search_condition THEN ... [ELSE { expression | NULL }] END | CASE expressionx WHEN expression1 [, expression2 ] ... THEN { expression | NULL } ... [ELSE { expression | NULL }] END | NULLIF ( expression1 , expression2 ) | COALESCE ( expression1 , expression2, ... expressionn ) | { MIN | MAX }( expression1,expression2, ..., expressionn )
}

---

The types of CASE expression are described below.

The SQL statement CASE also exists in routines, see section "CASE - Execute SQL statements conditionally".