PolarDB:Conditional expressions

CASE

The CASE expression in SQL is a generic condition expression, similar to the if/else statements in other programming 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, 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, 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.

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)

The data types of all the result expressions must support conversion into 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 a match is found. If no match is found, the result in the ELSE clause (or a null value) is returned.

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

A CASE expression does not evaluate any subexpressions that are not used to determine the result. For example, you can avoid a division-by-zero failure by using the following method:

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

COALESCE

The COALESCE function returns the first of its arguments that is not null. Null is only returned when all arguments are null.

COALESCE(value [, value2 ] ... )

This function is often used to substitute a default value for null values when data is retrieved for display or further computation. For example:

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

Sam as a CASE expression, COALESCE does not evaluate any arguments that are not used to determine the result. 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 can be used in some other database systems.

NULLIF

The NULLIF function returns a null value if value1 and value2 are equal. If the values are not equal, value1 is returned.

NULLIF(valuel, value2)

This function can be used to perform the inverse operation of the preceding COALESCE example:

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

If value1 is (none), a null value is returned. Otherwise, value1 is returned.

NVL

The NVL function returns the first of its arguments that is not null. This function evaluates the first expression. If that expression is evaluated to null, NVL returns the second expression.

NVL(exprl, expr2)

The return type is the same as the argument type. All arguments must be of the same data type (or must support conversion into a common data type). If all arguments are null, NVL returns null.

The following example calculates a bonus for employees who have no commission. If an employee receives commission, this expression returns the commission of the employee. If the employee does not receive commission (the commission is null), this expression returns a bonus that is equal to 10% of the employee's salary.

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

NVL2

NVL2 evaluates an expression and returns the second or third expression, depending on the value of the first expression. If the first expression is not null, NVL2 returns the value in expr2. If the first expression is null, NVL2 returns the value in expr3.

NVL2(expr1, expr2, expr3)

The return type is the same as the argument type. All arguments must be of the same data type (or must support conversion into a common data type).

The following example calculates 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

The GREATEST and LEAST functions select the highest or lowest value from a list of any number of expressions.

GREATEST(value [, value2 ] ... )

LEAST(value [, value2 ] ... )

The expressions must support conversion into a common data type, which will be the data type of the result. Null values in the list are ignored. The result is null only if all expressions are evaluated to null.

Note that the GREATEST and LEAST functions are not in the SQL standard, but are a common extension.