NVL Function in Oracle

NVL Function in Oracle | In this post we will discuss what is the NVL function in the Oracle database and how to use the NVL function in the Oracle database. How NUL function is related to CASE expression. How to use the NVL function In Oracle for Date?

What is the NVL function in the Oracle database?
NVL() function is used to replace the null with the user-defined values in the result query.

Syntax for the NVL() function:-
NVL(arg1, arg2 );

NVL() function accepts two arguments. If arg1 evaluates to null, then the NVL function returns arg2. If arg1 evaluates to something other than null, then NVL() method returns arg1 itself. The arguments i.e arg1 and arg2 data types may be the same or different.

The following condition is used by the oracle to implicitly transform one data type to another if data types are different.

1. If arg1 is a character-based data type, Oracle converts arg2 to arg1’s data type before comparing them and then returns VARCHAR2 in arg1’s character set.
2. If arg1’s data type is numeric,Oracle chooses the argument with the highest numeric precedence, implicitly converts the other argument to that data type, and then returns that data type.
3. Oracle generates an error if it is unable to implicitly convert one data type to another.

Oracle NVL Function Example

Let us demonstrate some NVL function in Oracle through examples:-

SELECT NVL(10, 20) FROM dual;

Output:-

NVL(10,20)
----------
        10

The first argument passed to NVL() function is not null i.e. 10 therefore the NVL() function returns the first argument itself. But in the below example first argument is null therefore NVL() function returns the second argument 20.

SELECT NVL(null, 20) FROM dual;

Output:-

NVL(NULL,20)
----------
        20

If both the first and second argument of the NVL() function is null then the NVL() function returns null.

SELECT NVL(null, null) FROM dual;

Output:-

N
--
        

Oracle NVL Function Example Through Tables

Let’s create one sample table and insert the data which can help in detailing the following examples:

CREATE TABLE student
(
   student_id INT,
   sem1marks INT,
   sem2marks INT
);

Let us insert some data into the student table.

INSERT INTO student
VALUES(1, 90, 70); 
INSERT INTO student
VALUES(2, 95, 100); 
INSERT INTO student
VALUES(3, NULL, 99);
INSERT INTO student
VALUES(4, NULL, NULL);

SELECT * FROM   student;

The output of the above query:-

STUDENT_ID  SEM1MARKS  SEM2MARKS
---------- ---------- ----------
         1         90         70
         2         95        100
         3                    99
         4

Oracle NVL Function Example-1

Let’s see the example which returns 0 if the first argument is null. When a null value is detected, you can replace it with another value using the Oracle/PLSQL NVL function.

SELECT NVL(sem1marks, '0') AS sem1marks,
       NVL(sem2marks, '0') AS sem2marks 
FROM student; 

It gives the following output:-

 SEM1MARKS  SEM2MARKS
---------- ----------
        90         70
        95        100
         0         99
         0          0

Oracle NVL Function Example-2

Let’s take the first example when the query returns the first argument value because the first argument is not null. In the Oracle NVL function if arg1 is not null then it will return arg1 itself.

SELECT NVL(sem1marks, sem2marks) FROM   student;

Now let’s see the output:-

NVL(SEM1MARKS,SEM2MARKS)
------------------------
                      90
                      95
                      99

In the above output, for the first and second row arg1 (sem1marks) is not null therefore NVL function returns arg1 itself. But for the third row arg1 ((sem1marks)) is null therefore NVL function returns arg2 (sem2marks) value. For the 4th row, arg1 is null so the NVL function return arg2 but there arg2 is also null hence finally it returns null.

How To Use NVL Function In Oracle

Now let us see some examples using the EMP table in the Oracle database to understand how to use the NVL function in Oracle. The EMP table has a comm column representing the commission. We want to calculate the total salary of every employee.

Question) Write a SQL query to find the total salary of employees in the EMP table. Total salary = salary + commission.

SELECT empno, sal, comm, (sal+comm) FROM emp;

Output:-

     EMPNO        SAL       COMM (SAL+COMM)
---------- ---------- ---------- ----------
      7839       5100
      7698       2850
      7782       2550
      7566       2975
      7788       3000
      7902       3000
      7369        800
      7499       1600        300       1900
      7521       1250        500       1750
      7654       1250       1400       2650
      7844       1500          0       1500
      7876       1100
      7900        950
      7934       1400

14 rows selected.

In the above example, only a few employees get the commission. While calculating the total salary, number + null yields a null value. To overcome this problem we can take the help of the NVL() function and returns 0 whenever the commission is null. See the below example:-

SELECT empno, sal, NVL(comm, 0), 
(sal + NVL(comm, 0)) AS total_sal
FROM emp;

Output:-

     EMPNO        SAL NVL(COMM,0)  TOTAL_SAL
---------- ---------- ----------- ----------
      7839       5100           0       5100
      7698       2850           0       2850
      7782       2550           0       2550
      7566       2975           0       2975
      7788       3000           0       3000
      7902       3000           0       3000
      7369        800           0        800
      7499       1600         300       1900
      7521       1250         500       1750
      7654       1250        1400       2650
      7844       1500           0       1500
      7876       1100           0       1100
      7900        950           0        950
      7934       1400           0       1400

14 rows selected.

Oracle NVL Function and CASE Expression

In terms of determining whether a value is NULL, the NVL() function is comparable to the CASE expression. The following method call of NVL(arg1, arg2 ) is equivalent to:-

CASE
WHEN arg1 IS NOT NULL THEN
   arg1
ELSE
   arg2
END

NVL Function In Oracle For Date

Let us create the table Orders which has the start date and end date. In some cases, the end date is set to null if there is no end date or if the record is active.

CREATE TABLE orders
(
   order_id   INT,
   order_name VARCHAR(25),
   start_date DATE,
   end_date   DATE
);

Let’s insert a few records in the orders table.

INSERT INTO orders VALUES 
(1, 'Table', DATE '2022-07-08', DATE '2022-09-09');

INSERT INTO orders VALUES 
(2, 'Teapot', DATE '2022-07-08', DATE '2022-09-07');

INSERT INTO orders VALUES
(3, 'Chair', DATE '2022-07-08', '');

INSERT INTO orders VALUES
(4, 'Sofa', DATE '2022-07-08', '');

SELECT * FROM orders;

Output for the following :

  ORDER_ID ORDER_NAME                START_DAT END_DATE
---------- ------------------------- --------- ---------
         1 Table                     08-JUL-22 09-SEP-22
         2 Teapot                    08-JUL-22 07-SEP-22
         3 Chair                     08-JUL-22
         4 Sofa                      08-JUL-22

From the above output, we can see that the start date represents when the record order has started, and the end date represents when the order has ended. The active record has the end date is NULL.

In the above output instead of showing the null, we can replace it with an alternative date like a static date i.e. 31 DEC 9999. Let’s see the query to execute it:-

SELECT order_id,
       order_name,
       start_date,
       NVL(end_date, '31-DEC-9999') AS end_date
FROM   orders; 

Output for the following query:-

  ORDER_ID ORDER_NAME                START_DAT END_DATE
---------- ------------------------- --------- ---------
         1 Table                     08-JUL-22 09-SEP-22
         2 Teapot                    08-JUL-22 07-SEP-22
         3 Chair                     08-JUL-22 31-DEC-99
         4 Sofa                      08-JUL-22 31-DEC-99

From the above output, we can say that the NULL date value can be converted to another using the NVL function.

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search