Difference Between DECODE and CASE

DECODE and CASE both provide conditional constructs in 'if...else if...else' form. Prior to Oracle 8.1.6 DECODE was the only function available for conditional check. In Oracle 8.1.6, CASE was introduced which was more efficient, more powerful, more structured than DECODE.

Everything DECODE can do CASE can do, but CASE can do a lot more which DECODE cannot.


Following are the high level differences between CASE and DECODE.

1. CASE can work with logical operators other than '=' : Decode performs only equality checks whereas is capable of working with operators like '<', '>', '<=', '>='.
   SQL> select ename
     2       , case
     3           when sal < 1000
     4                then 'Grade I'
     5           when (sal >=1000 and sal < 2000)
     6                then 'Grade II'
     7           when (sal >= 2000 and sal < 3000)
     8                then 'Grade III'
     9           else 'Grade IV'
    10         end sal_grade
    11  from emp
    12  where rownum < 4;
   
   
2. CASE can work with predicates and searchable subqueries : For example 'IN' statement or simple subqueries as shown below
   
   SQL> select e.ename,
     2         case
     3           -- predicate with "in"
     4           -- mark the category based on ename list
     5           when e.ename in ('KING','SMITH','WARD')
     6                then 'Top Bosses'
     7           -- searchable subquery
     8           -- identify if this emp has a reportee
     9           when exists (select 1 from emp emp1
    10                        where emp1.mgr = e.empno)
    11                then 'Managers'
    12           else
    13               'General Employees'
    14         end emp_category
    15  from emp e
    16  where rownum < 5;
    
3. CASE can work as a PL-SQL construct : Decode can be used as a function inside PL-SQL query whereas CASE can be used as a PL-SQL block or code construct as follows as a great substitute of IF-THEN-ELSE
   
   SQL> declare
     2    grade char(1);
     3  begin
     4    grade := 'b';
     5    case grade
     6      when 'a' then dbms_output.put_line('excellent');
     7      when 'b' then dbms_output.put_line('very good');
     8      when 'c' then dbms_output.put_line('good');
     9      when 'd' then dbms_output.put_line('fair');
    10      when 'f' then dbms_output.put_line('poor');
    11      else dbms_output.put_line('no such grade');
    12    end case;
    13  end;
    14  /
    
   CASE can even work as a parameter to the procedures whereas DECODE cannot
    
   SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end);

    
4. CASE handles NULL differently :
   Check out the different results with DECODE vs NULL.
   

   1 2 3 4 5 6 7 8 9

   SQL> select decode(null   2              , null, 'NULL'   3                    , 'NOT NULL'   4               ) null_test   5  from dual;   NULL_TEST ---- NULL

   1 2 3 4 5 6 7 8 9 10

   SQL> select case null   2         when null   3         then 'NULL'   4         else 'NOT NULL'   5         end null_test   6  from dual;   NULL_TEST -------- NOT NULL

   The searched CASE works as does DECODE.
   

   1 2 3 4 5 6 7 8 9 10 11

   SQL>  select case   2         when null is null   3         then 'NULL'   4         else 'NOT NULL'   5         end null_test   6* from dual SQL> /   NULL_TEST -------- NULL

5. CASE expects data type consistency : CASE expects data type consistency in expression / parameters provided to the function whereas DECODE ignores the parameter data types.
6. CASE is ANSI SQL compliant :
   CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
7. READABILITY : In simple cases DECODE is short and sweet to read and understand. However in complex cases it is always preferred to go with CASE to avoid bug prone code.

Thank you to Oratable for such a nice information.