Oracle SQL - Select CASE Expressions

  1. HOME
  2. Oracle SQL
  3. Select
  4. CASE Expressions

Introduction

You can solve complicated procedural problems with CASE expressions.

Oracle supports two CASE expression types:

  • simple CASE expressions and
  • searched CASE expressions.

In the simple CASE expression, you specify an input expression to be compared with the values in the WHEN ... THEN loop.

The implicit comparison operator is always the equal sign.

The left operand is always the input expression, and the right operand is the value from the WHEN clause.

In searched CASE expression you specify conditions in the WHEN clause.

You can use any logical operator in each individual WHEN clause.

CASE expressions are evaluated as follows:

  • Oracle evaluates the WHEN expressions in the order in which you specified them, and returns the THEN result of the first condition evaluating to TRUE.
  • Oracle does not evaluate the remaining WHEN clauses; therefore, the order of the WHEN expressions is important.
  • If none of the WHEN expressions evaluates to TRUE, Oracle returns the ELSE expression.
  • If you didn't specify an ELSE expression, Oracle returns a null value.

You must handle data types in a consistent way.

The input expressions and the THEN results in the simple CASE expression must have the same data type.

In both CASE expression types the THEN results should have the same data type, too.

Example