PolarDB:Conditional functions (CASE, COALESCE, NULLIF, NVL, NVL2, GREATEST, and LEAST)

CASE

The CASE expression is the standard SQL conditional expression, equivalent to if/else logic in other programming languages.

Searched form (general):

CASE WHEN condition THEN result
   [ WHEN ... ]
   [ ELSE result ]
END

Each condition is evaluated in order. When a condition returns TRUE, CASE returns the corresponding result and stops evaluating further conditions. If no condition matches and an ELSE clause is present, CASE returns the ELSE result. If no condition matches and the ELSE clause is omitted, CASE returns NULL.

All result expressions must be convertible to a single output type.

Example:

SELECT * FROM test;

 a
---
 1
 2
 3
(3 rows)

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
(3 rows)

Simple form (equality matching):

CASE expression
    WHEN value THEN result
  [ WHEN ... ]
  [ ELSE result ]
END

The expression is evaluated once and compared to each WHEN value in order. When a match is found, the corresponding result is returned. If no match is found, CASE returns the ELSE result (or NULL if ELSE is omitted).

The previous example can be written in simple form:

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
(3 rows)

CASE does not evaluate subexpressions that are not needed to determine the result. Use this to guard against runtime errors:

SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;

In this example, the division y/x is only evaluated when x <> 0, preventing a division-by-zero error.

COALESCE

COALESCE returns the first non-null argument. If all arguments are null, it returns NULL.

COALESCE(value [, value2 ] ... )

Use COALESCE to substitute a default value for null values when retrieving data. For example:

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

This returns description if it is not null, otherwise short_description if it is not null, otherwise the string '(none)'.

Like CASE, COALESCE does not evaluate arguments to the right of the first non-null argument.

COALESCE is a SQL-standard function that provides the same capability as NVL and IFNULL in other databases. For the Oracle-compatible equivalent, see NVL.

NULLIF

NULLIF returns NULL if value1 equals value2. Otherwise, it returns value1.

NULLIF(value1, value2)

NULLIF is logically equivalent to the following CASE expression:

CASE WHEN value1 = value2 THEN NULL ELSE value1 END

Use NULLIF to reverse the effect of a COALESCE substitution. For example, if COALESCE substitutes '(none)' for null values:

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

NULLIF converts the sentinel value back to NULL:

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

If value1 is '(none)', NULLIF returns NULL. Otherwise, it returns value1.

NVL

NVL returns the first non-null argument. If expr1 is not null, NVL returns expr1. If expr1 is null, NVL returns expr2.

NVL(expr1, expr2)

The return type matches the argument type. All arguments must be the same data type, or must be convertible to a common data type. If all arguments are null, NVL returns NULL.

NVL is an Oracle-compatible function. For the SQL-standard equivalent, see COALESCE.

Example: Calculate a bonus based on commission. If an employee has a commission, use the commission amount. If the commission is null (no commission), use 10% of the salary instead.

bonus = NVL(emp.commission, emp.salary * .10)

NVL2

NVL2 evaluates the first expression and returns one of two values depending on whether it is null.

NVL2(expr1, expr2, expr3)

The return type matches the argument type. All arguments must be the same data type, or must be convertible to a common data type.

Example: Calculate a bonus for employees who receive commission. If an employee receives commission, this expression returns an amount that is equal to 110% of the employee's commission. If the employee does not receive commission (the commission is null), this expression returns 0.

bonus = NVL2(emp.commission, emp.commission * 1.1, 0)

GREATEST and LEAST

GREATEST returns the highest value and LEAST returns the lowest value from a list of expressions.

GREATEST(value [, value2 ] ... )

LEAST(value [, value2 ] ... )

All expressions must be convertible to a common data type, which is the type of the result. Null values in the list are ignored. The result is NULL only if all expressions evaluate to null.

GREATEST and LEAST are not part of the SQL standard but are a common extension.