The SQL CASE expression is a
generic conditional expression, similar to if/else statements in
other languages:
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
CASE clauses can be used wherever
an expression is valid. condition is an
expression that returns a BOOLEAN result. If the result is true
then the value of the CASE expression is the
result that follows the condition. If the result is false any
subsequent WHEN clauses are searched in the same
manner. If no WHEN
condition is true then the value of the
case expression is the result in the
ELSE clause. If the ELSE clause is
omitted and no condition matches, the result is null.
An example:
SELECT * FROM test;
a
---
1
2
3
SELECT a,
CASE WHEN a=1 THEN 'one'
WHEN a=2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other
The data types of all the result
expressions must be convertible to a single output type.
The following "simple" CASE expression is a
specialized variant of the general form above:
CASE expression
WHEN value THEN result
[WHEN ...]
[ELSE result]
END
The
expression is computed and compared to
all the value specifications in the
WHEN clauses until one is found that is equal. If
no match is found, the result in the
ELSE clause (or a null value) is returned. This is similar
to the switch
statement in C.
The example above can be written using the simple
CASE syntax:
SELECT a,
CASE a WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
FROM test;
a | case
---+-------
1 | one
2 | two
3 | other
A CASE expression does not evaluate any subexpressions
that are not needed to determine the result. For example, this is a
possible way of avoiding a division-by-zero failure:
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
COALESCE
(value [, ...])
The COALESCE
function returns the first of its
arguments that is not null. Null is returned only if all arguments
are null. This is often useful to substitute a
default value for null values when data is retrieved for display,
for example:
SELECT COALESCE(description, short_description, '(none)') ...
Like a CASE expression, COALESCE
will
not evaluate arguments that are not needed to determine the result;
that is, arguments to the right of the first non-null argument are
not evaluated. This SQL-standard function provides capabilities
similar to NVL
and IFNULL
, which are used in some
other database systems.
NULLIF
(value1, value2)
The NULLIF
function returns a null value
if value1 and
value2 are equal; otherwise it returns
value1. This can be used to perform the
inverse operation of the COALESCE
example
given above:
SELECT NULLIF(value1, '(none)') ...
If value1 is (none), return a null, otherwise return value1.
GREATEST
(value [, ...])
LEAST
(value [, ...])
The GREATEST
and LEAST
functions select the largest or smallest value
from a list of any number of expressions. The expressions must all be convertible to a common
data type, which will be the type of the result see Section 9.2.5 for details.
NULL values in the list are ignored. The result will be NULL only if all the
expressions evaluate to NULL.
Note that GREATEST
and LEAST
are not in the SQL standard, but are
a common extension.