Oracle NVL

NULL is a special value in a Oracle which represents absence of value in a table. For this reason, any calculation performed on NULL values will give us a NULL value in result. This is not always desirable and for this reason we might want to replace NULL with some other value.

NVL is a function used in Oracle which is used to replace NULL value with some other value in columns for more meaningful results.

Syntax

The general syntax of NVL function is as follows:

NVL ( expression1. expression2)

Here,

Expression1– Expression1 could be a column name or a NULL value that we want to replace.

Expression2 – If Expression1 is NULL, then NVL function returns Expression2 in Output. If Expression1 is not NULL then NVL function returns Expression1 in the Output.

The data types of Expression1 and Expression2 could be same or different. If its different then Oracle converts Oracle converts both into same data type before comparing. if the data type can’t be converted then Oracle returns an error.

Examples

1. NVL Function Examples with Numeric Values

Lets use a simple query to see how NVL works:

SELECT
NVL(20,40)
FROM
dual;

Result:

If expression1 is NULL, then query will return expression2 that is 40. But in this case, since the expression1 is 20 and not NULL, it returns 20.

Lets see one more query where the expression1 is NULL:

SELECT
NVL(NULL,40)
FROM
dual;

Result:

In this case, the query returns expression2 since the expression1 is NULL.

For this example, we will be using Employee table from HR schema.

Let’s see this query to calculate the total salary with commission from the employee table:

SELECT first_name, last_name,
(salary + salary *(NVL(COMMISSION_PCT, 0)))Total_Salary
FROM EMPLOYEES ;

Result:

Since, many values in commission table contain NULL values, our final output would also have contained a lot of NULL values. But, we used NVL function to replace those NULL values with zero and we got the following result:

NVL and CASE Comparison

CASE function can also be used to calculate the same thing as NVL. Lets compare the two queries to understand the difference.

The syntax for NVL is as follows:

NVL (expression1, expression2)

Similarly, we can perform the same operation using a CASE function. Lets see the query:

CASE
WHEN Expression1 IS NOT NULL THEN
Expression1
ELSE
Expression2
END

Suppose, we want to use CASE function in the query we used for calculating total salary of employees. Here’s how that query will look:

SELECT first_name, last_name,
CASE
WHEN commission_pct IS NOT NULL THEN
(salary + salary * commission_pct)
ELSE
(salary + 0)
END Total_Salary
FROM employees ;

Result:

The query checks whether the values in commission_pct column are NULL or not and then performs the same operation as NVL does of replacing the NULL values with zero. We are able to accurately calculate the total salary of all employees using the CASE function.

NVL and COALESCE Comparison

COALESCE is a function that works the same way NVL does, except we can check more than 2 expressions for NULL values.

NVL(expression1, expression2);

COALESCE (expression1, expression2, expression3);

If expression1 is NULL then COALESCE function returns expression2, if it is also NULL then it returns expression3. It returns the first non-null value that it finds. If all the expressions are NULL then it returns NULL.

While, NVL replaces the NULL value in expression1 with expression2, the COALESCE function tries to find and return the first non-null value, but it returns a NULL values in case it doesn’t find an alternative.

In this tutorial, we learned how to use NVL function to replace the NULL values with other alternatives.