How to use NVL() function in Oracle

This Oracle SQL tutorial provides explanations, examples for NVL function in Oracle

What is NVL() function in Oracle

NVL function in Oracle is a nesting function which is used to substitute null values with certain values

In the above syntax

expr1 : it is the source values or expression which may contain null. You can provide column name,function on the column name
expr2 : It is the target value which will be placed in case null is returned from expr1

If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

Important Points to note
(1) The arguments expr1 and expr2 can have any datatype. If their datatypes are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.
If expr1 is character data, then Oracle Database converts expr2 to the datatype of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.

If expr1 is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.

So following statement would succeed

select nvl(num-col ,10) from <table>
select nvl(char-col ,'NA') from <table>
select nvl(end_date,'01-MAY-18') from <table>

Following statement would do implicit conversion

select nvl(char-col ,1) from <table>

Following statement would fail

select nvl(end_date,'m/a') from <table>
select nvl(user_id,'abc') from <table>

How to use NVL() function

This function is very useful where we dont want to return null values or in numeric calculation as null would turn whole values into null

Lets take an example to understand it
We have sales_people table which contain the sales done’s by sales people,commission % on the sales ,salary of the people. We want to calculate total monthly incomes for the sales people. Sales can have null values

Statement would be

select name,(salary + sales*commission/100)  Monthy_income from sales_people;

It will return null values of all the people who has not done any sales in that period as calculation will have null values

This can be fixed by using nvl function as

select name,(salary + nvl(sales,0)*commission/100) Monthly_income from sales_people;

So it is clearly very helpful while doing Mathematical operations. NVL Function in Oracle is quite a useful function and it can be used in many places

Also Reads
oracle sql date functions
NULLIF function in Oracle
Update statement in oracle
Coalesce Function in Oracle
change date format in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm