NVL vs NVL2

• NVL , NVL2 are special functions of Oracle.
  
• NVL is similar to if-else.
  
  NVL2 is similar to conditional operator. { a>b? a:b }
  
• NVL:
  
  It is used to replace NULL value with Not Null value.
  
  Syntax:    NVL ( expr1, expr2 );
  
      If expr1 is NULL, expr2 value is returned.
  
      If expr1 is Not Null, expr1 is only returned.
  
  Example:
  
      Query: Display employee name and total salary.
  
     
  
      Select ename as 'Employee Name', sal+NVL(comm,0) as 'Total Salary' from emp;
  
  
  
  
  
  
  
  
  
  
  
  NVL(comm,0) = If comm is NULL, then return 0. Else return comm.
  
  This is used because 10+NULL = NULL;
  
  
  
  Example:
  
      Select comm, sal, NVL(comm,sal) from emp;
  
  
  
  
  
  
  Here where ever comm is null, sal is printed.
  
• NVL2:
  
  It is used to convert NULL values and NOT NULL values.
  
  It is same as conditional operator.    { a>b?a:b }
  
  Syntax:
  
      NVL2( expr1, expr2, expr3 );
  
  If expr1 = NOT NULL, returns expr2.
  
  If expr1 = NULL, returns expr3.
  
  
  
  Example:
  
      Query:    Update the employee comm as follows.
  
              Comm is NULL, update to 500.
  
              Comm is NOT NULL, update
  
     
  
  Initial Query:
  
     
  
  
  
     
  
  
  
     
  
  
  
      Updated Query: