PolarDB:NULL-related functions

NANVL

Returns a fallback value when a floating-point expression evaluates to NaN (Not a Number).

• If n1 is NaN, returns n2.

• If n1 is a number, returns n1.

Syntax

NANVL(n1, n2)

Parameters

Return value

Returns a value of type BINARY_FLOAT or BINARY_DOUBLE.

Example

CREATE TABLE float_point_demo (bf BINARY_FLOAT, bd BINARY_DOUBLE);
INSERT INTO float_point_demo VALUES (123.456, 'NaN');
INSERT INTO float_point_demo VALUES ('NAN', 123.456);

SELECT * FROM float_point_demo;

   bf    |   bd
---------+---------
 123.456 |     NaN
     NaN | 123.456

Replace NaN values with 0 using NANVL:

SELECT NANVL(bf, 0) "nanvl_1", NANVL(bd, 0) "nanvl_2" FROM float_point_demo;

 nanvl_1 | nanvl_2
---------+---------
 123.456 |       0
       0 | 123.456

NVL2

Returns one of two values based on whether an expression is NULL.

• If expr1 is not NULL, returns expr2.

• If expr1 is NULL, returns expr3.

Syntax

NVL2(expr1, expr2, expr3)

Parameters

Return value

• If expr2 and expr3 are the same type, returns a value of that type.

• If they differ and expr2 is character data, the database converts expr3 to the type of expr2 (unless expr3 is a null constant) and returns a VARCHAR2 value in the character set of expr2.

• If they differ and expr2 is numeric, the database determines the highest numeric precedence, implicitly converts the other parameter to that type, and returns a value of that type.

Example

-- expr1 is not NULL: returns expr2
SELECT NVL2('A'::text, 'B', 'C') FROM DUAL;

 nvl2
------
 B

-- expr1 is NULL: returns expr3
SELECT NVL2(NULL, 2, 3) FROM DUAL;

 nvl2
------
    3

NVL

Replaces a NULL value with a specified substitute. A common use case is computing a value when the primary column may be NULL — for example, substituting a fixed bonus when an employee's commission column is NULL:

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

• If expr1 is NULL, returns expr2.

• If expr1 is not NULL, returns expr1.

Syntax

NVL(expr1, expr2)

Parameters

Return value

If expr1 and expr2 are different types, the database implicitly converts one to match the other. If the types cannot be implicitly converted, the database returns an error. Implicit conversion follows these rules:

• Character data: converts expr2 to the type of expr1 and returns a VARCHAR2 value in the character set of expr1.

• Numeric data: determines the highest numeric precedence, converts the other parameter to that type, and returns a value of the converted type.

Example

SELECT NVL(NULL::text, 'B') FROM DUAL;

 nvl
-----
 B

NULLIF

Compares two expressions and returns NULL if they are equal, or the first expression if they differ.

NULLIF(expr1, expr2) is logically equivalent to:

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

Syntax

NULLIF(expr1, expr2)

Parameters

Return value

• If expr1 = expr2, returns NULL.

• If expr1 ≠ expr2, returns expr1.

Example

-- Values differ: returns expr1
SELECT NULLIF('a', '') FROM DUAL;

 nullif
--------
 a

-- Values are equal: returns NULL
SELECT NULLIF('', '') FROM DUAL;

 nullif
--------

LNNVL

Evaluates a condition and inverts its truth value, including the UNKNOWN case for NULL operands. Use LNNVL to include rows with NULL values that a standard inequality condition would exclude.

LNNVL can only be used in the WHERE clause. It supports scalar conditions only — AND, OR, and BETWEEN conditions are not supported. PolarDB for Oracle may internally rewrite NOT IN conditions to NOT EXISTS using LNNVL; this is reflected in EXPLAIN PLAN output.

Syntax

LNNVL(condition)

Parameters

Return value

The following table shows the return value for each truth state of the condition, given a = 2 and b IS NULL:

In short: LNNVL returns TRUE when the condition is FALSE or UNKNOWN, and FALSE when the condition is TRUE.

Example

CREATE TABLE lnnvltbl (name VARCHAR(10), id INT);
INSERT INTO lnnvltbl VALUES (NULL, 1);
INSERT INTO lnnvltbl VALUES ('', 2);
INSERT INTO lnnvltbl VALUES ('null', 3);
INSERT INTO lnnvltbl VALUES ('s', 4);
INSERT INTO lnnvltbl VALUES ('ss', 5);

-- Returns rows where name IS NULL or empty (ids 1 and 2)
SELECT * FROM lnnvltbl WHERE LNNVL(name IS NOT NULL) ORDER BY id;

 name | id
------+----
      |  1
      |  2

COALESCE

Returns the first non-NULL expression from a list. A typical use is providing a fallback chain for display values:

SELECT COALESCE(description, short_description, '(none)') FROM products;

This returns description if it is not NULL, then short_description if that is not NULL, otherwise '(none)'.

COALESCE uses short-circuit evaluation: it stops evaluating as soon as it finds a non-NULL value. This is equivalent to a nested CASE expression:

-- COALESCE(expr1, expr2) is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

-- COALESCE(expr1, expr2, expr3) is equivalent to:
CASE WHEN expr1 IS NOT NULL THEN expr1
     ELSE COALESCE(expr2, expr3) END

If all expressions are NULL, COALESCE returns NULL.

Syntax

COALESCE(expr [, expr]...)

Parameters

Return value

If all expressions are numeric or implicitly convertible to numeric, the database determines the highest numeric precedence, implicitly converts all other expressions to that type, and returns a value of the converted type.

Example

-- Returns the first non-NULL value
SELECT COALESCE(NULL, 3.934, 1) FROM DUAL;

 coalesce
----------
    3.934

-- Short-circuit evaluation stops at the first non-NULL argument
SELECT COALESCE(NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                NULL, NULL, NULL, 1) FROM DUAL;

 coalesce
----------
        1