Simple CASE Function (simple_case_function)

The simple CASE function (simple_case_function) is a special function that compares an expression with a set of simple expressions to determine a result expression.

Structure

Syntax Syntax

<simple_case_function> ::=
  CASE <check_expression>
    WHEN <search_expression> THEN <result_expression>
    [...]
    [ELSE <default_expression>]
  END

<check_expression> ::=
  <expression>

<search_expression> ::=
  <expression>

<result_expression> ::=
  <expression>

<default_expression> ::=
  <expression>

End of the code.

Examples

SQL Tutorial, Functions

Explanation

CASE compares the value of the comparison expression check_expression with the values of the expressions search_expression, one after the other. If values match, the result of the simple CASE function is the value of the result_expression that belongs to the search_expression.

If no match is found, CASE returns the result of default_expression. If default_expression is not specified, the result of CASE is the NULL value.

The data types of check_expression and search_expression must be comparable. The data types of result_expression and default_expression must be comparable. The data types of search_expression and result_expression do not have to be comparable.

If the values of the expressions check_expression and search_expression are NULL values, then a match exists. The comparison of the special NULL value with any other value never results in a match.

More Information

General CASE Function (searched_case_function)