The NVL Function (Oracle)

Use Case

This function is particularly useful when dealing with data where NULL values may cause issues in calculations or comparisons, allowing you to provide a suitable default value in such cases.

In SQL, NVL is a function that is used to replace NULL values with a specified default value. It is primarily associated with Oracle SQL, but similar functions exist in other database systems with different names (such as COALESCE in standard SQL and some other database systems).

General Syntax

NVL(expression, -- value to be checked; if NULL, it is repalced by the next argument
	default_value_if_null)

Example:

SELECT NVL(salary, 0) AS salary_with_default
FROM employees;

If the salary column contains NULL values, they will be replaced with 0. Otherwise, the original value of salary will be returned.

Behaviour

If expression is NULL, the NVL function returns replacement_value.
If expression is not NULL, the NVL function returns expression.

Alternative: `NVL2()` Function

The NVL2 functions Evaluates a value for NULL and provides different outputs depending on whether the value is NULL or not, therefore, handling both NULL and NON-NULL conditions:

NVL2(expression, value_if_not_null, value_if_null)

General Syntax

Behaviour

Alternative: NVL2() Function

Alternative: `NVL2()` Function