SQL in a Nutshell, 2nd Edition

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.

CASE has two usages: simple and searched. Simple CASE expressions compares 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.

All vendors provide the ANSI SQL2003 syntax for CASE.

SQL2003 Syntax and Description

-- Simple comparison operation
CASE input_valueWHEN 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 usage, multiple WHEN clauses are used, though only one ELSE clause is necessary.

Examples

Here is a simple comparison operation where the ...