NVL2 function with examples in Oracle

The NVL2 function in Oracle SQL is used to evaluate and return different expressions based on whether a specified expression is NULL or not NULL. It provides a shorthand way to handle NULL values and can be seen as a combination of the NVL and DECODE functions.

Syntax

NVL2(expr1, expr2, expr3)

• expr1: The expression to be checked for NULL.
• expr2: The value returned if expr1 is not NULL.
• expr3: The value returned if expr1 is NULL.

Examples

1. Basic Usage

Return different values based on whether a column is NULL or not.

SELECT NVL2(employee_id, 'Has ID', 'No ID') AS id_status
FROM employees;

This query checks if the employee_id is NULL or not and returns 'Has ID' if it's not NULL and 'No ID' if it is NULL.

2. Using NVL2 with Literal Values

Check a literal value and return different strings.

SELECT NVL2(NULL, 'Not Null', 'Is Null') AS result FROM DUAL;

-- Returns 'Is Null'

SELECT NVL2('Oracle', 'Not Null', 'Is Null') AS result FROM DUAL;

-- Returns 'Not Null'

3. Combining NVL2 with Column Values

Use NVL2 to determine a message based on column values.

SELECT employee_name,
       NVL2(bonus, 'Bonus Available', 'No Bonus') AS bonus_status
FROM employees;

This query checks if the bonus column is NULL or not and returns 'Bonus Available' if it's not NULL and 'No Bonus' if it is NULL.

4. Using NVL2 with Calculations

Perform calculations based on whether a column is NULL or not.

SELECT salary,
       NVL2(commission_pct, salary + (salary * commission_pct), salary) AS total_compensation
FROM employees;

This query checks if the commission_pct is NULL or not. If it is not NULL, it calculates the total compensation by adding the commission to the salary. If it is NULL, it returns the salary alone.

5. Handling Date Columns

Use NVL2 to handle date columns and return different date values.

SELECT order_id,
       order_date,
       NVL2(delivery_date, delivery_date, SYSDATE) AS actual_delivery_date
FROM orders;

This query checks if the delivery_date is NULL or not. If it is not NULL, it returns the delivery_date. If it is NULL, it returns the current date (SYSDATE).

Practical Use Cases

1. Conditional Formatting in Reports

Use NVL2 to conditionally format report outputs based on data availability.

SELECT employee_id, employee_name,
       NVL2(phone_number, phone_number, 'No Phone Number') AS contact_info
FROM employees;

This ensures that the report clearly indicates when contact information is missing.

2. Default Values for Calculations

Provide default values in calculations when data might be missing.

SELECT product_id,
       NVL2(discount, price - (price * discount), price) AS final_price
FROM products;

This ensures the calculation can proceed even if the discount value is missing.

3. Data Cleanup and Standardization

Standardize data outputs for downstream processing.

SELECT customer_id,
       NVL2(email, email, 'No Email Provided') AS contact_email
FROM customers;

This helps in ensuring consistent data handling in applications that consume this data.

Conclusion

The NVL2 function in Oracle is a versatile and powerful tool for handling NULL values in SQL queries. It allows you to provide conditional outputs based on the presence or absence of NULL, making it particularly useful for data validation, formatting, and ensuring robust query results. By using NVL2, you can simplify your SQL logic and improve the readability and maintainability of your code.