CASE expressions

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram                                   .-ELSE NULL---------------.
>>-CASE--+-searched-when-clause-+--+-------------------------+--END-><
         '-simple-when-clause---'  '-ELSE--result-expression-'
 
searched-when-clause:
 
   .-----------------------------------------------------.
   V                                                     |
|----WHEN--search-condition--THEN--+-result-expression-+-+------|
                                   '-NULL--------------'
 
simple-when-clause:
 
|--expression--------------------------------------------------->
 
   .-----------------------------------------------.
   V                                               |
>----WHEN--expression--THEN--+-result-expression-+-+------------|
                             '-NULL--------------'
 

CASE expressions allow an expression to be selected based on the evaluation of one or more conditions. In general, the value of the case-expression is the value of the result-expression following the first (leftmost) when-clause that evaluates to true. If no when-clause evaluates to true and the ELSE keyword is present then the result is the value of the ELSE result-expression or NULL. If no when-clause evaluates to true and the ELSE keyword is not present then the result is NULL. Note that when a when-clause evaluates to unknown (because of nulls), the when-clause is not true and hence is treated the same way as a when-clause that evaluates to false.

searched-when-clause
Specifies a search-condition that is applied to each row or group of table data presented for evaluation, and the result when that condition is true.
simple-when-clause
Specifies that the value of the expression prior to the first WHEN keyword is tested for equality with the value of the expression that follows each WHEN keyword. It also specifies the result when that condition is true.

The data type of the expression prior to the first WHEN keyword:

result-expression or NULL
Specifies the value that follows the THEN keyword and ELSE keywords. There must be at least one result-expression in the CASE expression with a defined data type. NULL cannot be specified for every case.

All result-expressions must have compatible data types, where the attributes of the result are determined based on the Rules for result data types.

search-condition
Specifies a condition that is true, false, or unknown about a row or group of table data.

The search-condition must not include a subquery in an EXISTS or IN predicate.

There are two scalar functions, NULLIF and COALESCE, that are specialized to handle a subset of the functionality provided by CASE. The following table shows the equivalent expressions using CASE or these functions.

Table 25. Equivalent CASE Expressions
CASE Expression Equivalent Expression
CASE WHEN e1=e2 THEN NULL ELSE e1 END NULLIF(e1,e2)
CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END COALESCE(e1,e2)
CASE WHEN e1 IS NOT NULL THEN e1 ELSE COALESCE(e2,...,eN) END COALESCE(e1,e2,...,eN)

Examples