NVL2 Function in Oracle SQL

Topic Introduction: In Oracle SQL, the NVL2 function is used to perform different actions based on whether a specified expression is NULL or not. It takes three arguments: an expression, a value to return if the expression is not NULL, and a value to return if the expression is NULL. The function evaluates the expression and returns one of the two specified values based on whether the expression is NULL or not.

The syntax for the NVL2 function is as follows:

NVL2(expression, value_if_not_null, value_if_null)

expression: The expression that is checked for NULL.

value_if_not_null: The value to return if the expression is not NULL.

value_if_null: The value to return if the expression is NULL.

Here's an example of how to use the NVL2 function in Oracle SQL:

SELECT employee_name,

       NVL2(salary, 'Salary exists', 'Salary is missing') AS salary_status

FROM employees;

In this example:

If the salary column is not NULL for an employee, the NVL2 function will return 'Salary exists'.

If the salary column is NULL for an employee, the NVL2 function will return 'Salary is missing'.

So, the result set will indicate the status of each employee's salary based on whether it's NULL or not.

You can use the NVL2 function to perform conditional operations or provide different default values based on the presence or absence of NULL values in your data.