NULLIF, NVL, NVL2, COALESCE Functions

create table hr.dummy (id number, name varchar2(32), bdate date) tablespace users;

insert into hr.dummy (id,name,bdate) values (3,'Carl',SYSDATE);

insert into hr.dummy (id,name,bdate) values (7,'Gary',SYSDATE);

insert into hr.dummy (id,name,bdate) values (2,NULL,SYSDATE);

insert into hr.dummy (id,name,bdate) values (6,NULL,SYSDATE);

insert into hr.dummy (id,name,bdate) values (1,'Anna', SYSDATE);

insert into hr.dummy (id,name,bdate) values (5,'Fitzgerald',SYSDATE);

insert into hr.dummy (id,name,bdate) values (4,NULL,SYSDATE);

insert into hr.dummy (id,name,bdate) values (0,NULL,SYSDATE);

commit;

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null

NVL just return something whether expression is NULL.

SELECT NAME, NVL2(NAME,'Registered','Pending') NLV2_, NVL(NAME, 'Nameless') NVL_

FROM hr.dummy ORDER BY NAME ASC;

NAME                             NLV2_      NVL_

-------------------------------- ---------- --------------------------------

Anna                             Registered Anna

Carl                             Registered Carl

Fitzgerald                       Registered Fitzgerald

Gary                             Registered Gary

                                 Pending    Nameless

                                 Pending    Nameless

                                 Pending    Nameless

                                

                                

NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1. You cannot specify the literal NULL for expr1.       

The arguments must be of the same datatype.

SQL> select nullif(id,'1') from  hr.dummy;

select nullif(id,'1') from  hr.dummy

                 *

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

                        

SQL> select nullif(id,1), id from  hr.dummy order by 1 asc nulls first;

NULLIF(ID,1)         ID

------------ ----------

                      1

           2          2

           3          3

           4          4

           5          5

           6          6

           7          7

It is most commonly used for handling the ZERO_DIVIDE exception or ORA-01476.

SQL> insert into hr.dummy (id,name,bdate) values (0,NULL,SYSDATE);

commit;

SQL> SELECT 12/ID FROM hr.dummy;

ERROR:

ORA-01476: divisor is equal to zero

          

          

SQL> SELECT ID, 12/NULLIF(ID,0) FROM hr.dummy ORDER BY ID ASC;

        ID 12/NULLIF(ID,0)

---------- ---------------

         0

         1              12

         2               6

         3               4

         4               3

         5             2.4

         6               2

         7      1.71428571

      

alter table hr.dummy add col01 number default null;

alter table hr.dummy add col02 number default null;

alter table hr.dummy add col03 number default null;

update hr.dummy set col01=1 where mod(id,3)=0;

update hr.dummy set col02=2  where mod(id-1,3)=0;

update hr.dummy set col03=3 where mod(id+1,3)=0;

COALESCE function returns the first non-null expression in the list.

SQL>  SELECT * from hr.dummy order by id asc;

        ID NAME                             BDATE          COL01      COL02      COL03

---------- -------------------------------- --------- ---------- ---------- ----------

         0                                  20-FEB-15          1

         1 Anna                             20-FEB-15                     2

         2                                  20-FEB-15                                3

         3 Carl                             20-FEB-15          1

         4                                  20-FEB-15                     2

         5 Fitzgerald                       20-FEB-15                                3

         6                                  20-FEB-15          1

         7 Gary                             20-FEB-15                     2

8 rows selected.

SQL> SELECT id, coalesce(col01,col02,col03) from hr.dummy order by id asc;

        ID COALESCE(COL01,COL02,COL03)

---------- ---------------------------

         0                           1

         1                           2

         2                           3

         3                           1

         4                           2

         5                           3

         6                           1

         7                           2

8 rows selected.

alter table hr.dummy add alt_name varchar2(32);

UPDATE hr.dummy SET ALT_NAME='Mary' where id=2;

UPDATE hr.dummy SET ALT_NAME='Jhon' where id=0;

UPDATE hr.dummy SET ALT_NAME='Maggie' where id=4;

UPDATE hr.dummy SET ALT_NAME='Carol' where id=6;

E.g.

SQL> select coalesce(name, ALT_NAME) Name, nvl(name,alt_name), nvl2(name,name,alt_name) from hr.dummy order by id asc;

NAME                             NVL(NAME,ALT_NAME)               NVL2(NAME,NAME,ALT_NAME)

-------------------------------- -------------------------------- --------------------------------

Jhon                             Jhon                             Jhon

Anna                             Anna                             Anna

Mary                             Mary                             Mary

Carl                             Carl                             Carl

Maggie                           Maggie                           Maggie

Fitzgerald                       Fitzgerald                       Fitzgerald

Carol                            Carol                            Carol

Gary                             Gary                             Gary

Regards