Skip to Main Content
SQL in a Nutshell
book

SQL in a Nutshell

by Kevin Kline
December 2000
Intermediate to advanced content levelIntermediate to advanced
224 pages
9h 52m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

SQL in a Nutshell, 3rd Edition

SQL in a Nutshell, 3rd Edition

Kevin Kline

Publisher Resources

ISBN: 1565927443