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 |
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 ...
Get SQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.