Decode vs Coalesce

Decode:

• Works similarly to 'switch' in c, c++.
  
• Syntax:
  

  Decode ( Expression    , value1, return expr1
  

              , value2, return expr2
  

              ------
  

              [ default expr ] );
  

• If default expr is not provided, Oracle returns NULL.
  
• Decode must be used only if the comparison is '='.
  
• Example
  

  Query: Display employee names, salaries, job from emp.
  

          If Job = clerk, then display 'worker'
  

          If Job = manager, then display 'Boss'
  

          If Job = president, then display 'Big Boss'
  

  Select ename, sal, decode(lower(job), 'clerk', 'worker', 'manager', 'boss', 'president', 'big boss') from emp;
  

  
  

  Here, few of the employee's job is null. This is because, we didn't provided default value.
  

  Select ename, sal, decode(lower(job), 'clerk', 'worker', 'manager', 'boss', 'president', 'big boss', 'employee') from emp;
  

  
  

 

Coalesce:

• Works similar to Decode.
  
• It returns the first not null value from the list of fields.
  
• Syntax:
  

  Coalesce (expr1,expr2, ---);
  

• Example:
  

  Select comm, sal, coalesce(comm,sal) as 'coalesce' from emp;
  

  
  

  Here if we observe,
  

  First row, comm= null. Hence sal is printed.
  

  Second row, comm=300. Hence Comm is printed.