SQL in a Nutshell

Name

CASE

Synopsis

The CASE function provides IF-THEN-ELSE functionality within a SELECT or UPDATE statement. It evaluates a list of conditions and returns one value out of several possible values.

Vendor	Command
SQL Server	Supported
MySQL	Supported
Oracle	Not Supported (refer to the DECODE function in vendor documentation for similar functionality)
PostgreSQL	Supported

CASE has two usages: simple and searched. Simple CASE expressions compare one value, the input_value, with a list of other values, and return a result associated with the first matching value. Searched CASE expressions allow the analysis of several logical conditions and return a result associated with the first one that is true.

SQL99 Syntax and Description

-- Simple comparison operation
CASE input_value
WHEN when_condition THEN resulting_value
[...n]
[ELSE else_result_value]
END

-- Boolean searched operation
CASE
WHEN Boolean_condition THEN resulting_value
[...n]
[ELSE else_result_expression]
END

In the simple CASE function, the input_value is evaluated against each WHEN clause. The resulting_value is returned for the first TRUE instance of input_value = when_condition. If no when_condition evaluates as TRUE, the else_result_value is returned. If no else_result_value is specified, then NULL is returned.

In the more elaborate Boolean searched operation, the structure is essentially the same as the simple comparison operation, except that each WHEN clause has its own Boolean comparison operation.

In either ...