Movatterモバイル変換

9.18.1.CASE

9.18.2.COALESCE

9.18.3.NULLIF

9.18.4.GREATEST andLEAST

Tip

If your needs go beyond the capabilities of these conditional expressions, you might want to consider writing a server-side function in a more expressive programming language.

Note

AlthoughCOALESCE,GREATEST, andLEAST are syntactically similar to functions, they are not ordinary functions, and thus cannot be used with explicitVARIADIC array arguments.

TheSQLCASE expression is a generic conditional expression, similar to if/else statements in other programming languages:

CASE WHENcondition THENresult     [WHEN ...]     [ELSEresult]END

CASE clauses can be used wherever an expression is valid. Eachcondition is an expression that returns aboolean result. If the condition's result is true, the value of theCASE expression is theresult that follows the condition, and the remainder of theCASE expression is not processed. If the condition's result is not true, any subsequentWHEN clauses are examined in the same manner. If noWHENcondition yields true, the value of theCASE expression is theresult of theELSE clause. If theELSE clause is omitted and no condition is true, the result is null.

An example:

SELECT * FROM test; a--- 1 2 3SELECT 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 theresult expressions must be convertible to a single output type. SeeSection 10.5 for more details.

There is a“simple” form ofCASE expression that is a variant of the general form above:

CASEexpression    WHENvalue THENresult    [WHEN ...]    [ELSEresult]END

The firstexpression is computed, then compared to each of thevalue expressions in theWHEN clauses until one is found that is equal to it. If no match is found, theresult of theELSE clause (or a null value) is returned. This is similar to theswitch statement in C.

The example above can be written using the simpleCASE 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

ACASE 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 [, ...])

TheCOALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:

SELECT COALESCE(description, short_description, '(none)') ...

This returnsdescription if it is not null, otherwiseshort_description if it is not null, otherwise(none).

The arguments must all be convertible to a common data type, which will be the type of the result (seeSection 10.5 for details).

Like aCASE expression,COALESCE only evaluates the arguments that are 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 toNVL andIFNULL, which are used in some other database systems.

NULLIF(value1,value2)

TheNULLIF function returns a null value ifvalue1 equalsvalue2; otherwise it returnsvalue1. This can be used to perform the inverse operation of theCOALESCE example given above:

SELECT NULLIF(value, '(none)') ...

In this example, ifvalue is(none), null is returned, otherwise the value ofvalue is returned.

The two arguments must be of comparable types. To be specific, they are compared exactly as if you had writtenvalue1 =value2, so there must be a suitable= operator available.

The result has the same type as the first argument — but there is a subtlety. What is actually returned is the first argument of the implied= operator, and in some cases that will have been promoted to match the second argument's type. For example,NULLIF(1, 2.2) yieldsnumeric, because there is nointeger=numeric operator, onlynumeric=numeric.

GREATEST(value [, ...])

LEAST(value [, ...])

TheGREATEST andLEAST 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 (seeSection 10.5 for details). NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.

Note thatGREATEST andLEAST are not in the SQL standard, but are a common extension. Some other databases make them return NULL if any argument is NULL, rather than only when all are NULL.