NVL2

Oracle

NVL2(expr1, expr2, expr3)

• expr1: The expression that will be tested for a null value. This can be columns, constants, or return values from a function. If expr1 is not null, expr2 is returned; if expr1 is null, expr3 is returned.
• expr2: The expression that is returned if expr1 is not null. This expression can involve columns, constants, or function return values.
• expr3: The expression that is returned if expr1 is null. Similar to expr2, this could include columns, constants, or the result of a function.

Example

SELECT NVL2('Hello World', 'Not Null', 'Null') AS Example FROM dual;

Output

Example
-----------
Not Null

Explanation

The NVL2 function in Oracle evaluates whether the first expression is null or not. If it’s not null, it returns the second expression, and if it’s null, it returns the third expression. In this case, since ‘Hello World’ is not null, ‘Not Null’ is returned.

PostgreSQL

NVL2(expression, value_if_not_null, value_if_null)

• expression: This is the data whose Null values the function needs to detect. It can of any data type which needs to be evaluated for null or non-null values.
• value_if_not_null: This value is returned by the NVL2 function when the “expression” value is not null. The data type should either match with “expression” data type or should be compatible for automatic conversion.
• value_if_null: This parameter is returned by the NVL2 function if the “expression” value is null. The data type should either match with “expression” data type or should be compatible for automatic conversion.

Example

SELECT
    NVL2(user_name,
         'Present',
         'Absent') AS user_presence_status
FROM
    Users;

Output

user_presence_status
-----------------
Present
Absent
Present
Absent
Present

Explanation

The NVL2 function in PostgreSQL checks if a value is not NULL; if it’s not NULL, the function returns the second value provided, otherwise the third value. In this given example, the function checks the user_name field in the Users table. If user_name is not NULL, the function will return 'Present'. If user_name is NULL, it will return 'Absent'. The output hence provides a list of 'Present' or 'Absent' corresponding to each user’s existence in the database.