O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required