The NVL() function in Oracle is used to replace NULL / Empty / NA values with some meaningful value. The NVL is a short form for Null Value. As you must be knowing that, NULL value means undefined, empty or Not Assigned. Remember, a blank space is not null and also 0 is not null. Null means undefined value.

So let's get going,

The NVL() function has the following syntax

NVL(exp, replacement-value)

Where the arguments are

exp: The expression which you want to evaluate and want to replace in case it is empty

replacement-value: The value which you want to return if the first argument is empty.

NVL() Examples

For example, suppose we have table: emp, with the following structure

As we can see the COMM column is NULL for some employees.

We can use the NVL function to replace NULL values for COMM with 0 by giving the following command

select empno,ename,job,mgr,hiredate,sal,nvl(comm,0),deptno,city from emp

NVL Function a necessity

There are some situations where NVL function becomes essential, like for example, if we need to show the total salary by adding SAL+COMM columns then, if we give the following command we will get the Total salary as NULL for those employees whose COMM is NULL.

In these situations, the NVL function becomes very handy and we can use the NVL function to replace NULL / EMPTY / NA  values with our own value. So to calculate total salary we can give the following command

select empno,ename,sal,comm,(sal+nvl(comm,0)) as Total from emp;

Also see NVL2() function in Oracle