What is the difference between Decode and Case?

1. DECODE can be used Only inside SQL statement But CASE can be used anywhere even as a parameter of a function/procedure
2. DECODE can only compare discrete values (not ranges) continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1 Oracle introduced the searched CASE statement which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting).
3. CASE is almost always easier to read and understand and therefore it's easier to debug and maintain.
4. Another difference is CASE is an ANSI standard whereas Decode is proprietary for Oracle.
5. Performance wise there is not much differences. But Case is more powerful than Decode.

 

DECODE unlike the "searched" CASE treats NULL differently. Normally, including CASE, NULL = NULL

results in NULL, however when DECODE compares NULL with NULL result is TRUE:

 

SQL> SELECT  DECODE(NULL,NULL,-1) decode,

  2          CASE NULL WHEN NULL THEN -1 END case

  3    FROM  DUAL

  4  /

 

    DECODE       CASE

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

        -1

As per my experience:

 

1.DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc.

It takes some complex coding – forcing ranges of data into discrete form – to achieve the same effect with DECODE.

 

 

2.DECODE works with expressions that are scalar values only. CASE can work with predicates and

subqueries in searchable form.

 

An example of categorizing employees based on reporting relationship, showing these two uses of CASE.

 

 

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;

 

ENAME      EMP_CATEGORY

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

SMITH      Top Bosses

ALLEN      General Employees

WARD       Top Bosses

JONES      Managers

 

3. CASE expects datatype consistency, DECODE does not

 

Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.

 

 

 

SQL> select decode(2,1,1,

  2                 '2','2',

  3                 '3') t

  4  from dual;

 

         T

----------

         2

 

 

SQL> select case 2 when 1 then '1'

  2              when '2' then '2'

  3              else '3'

  4         end

  5  from dual;

            when '2' then '2'

                 *

ERROR at line 2:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR