Decode Vs Case

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot.
Differences between DECODE and CASE are listed below:

---

1. DECODE performs an equality check only but CASE can work with logical operators other than "=".
2. DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.
3. CASE is a statement while DECODE is a function.
4. DECODE and CASE treats NULL differently. Normally, including CASE, NULL = NULL results in NULL, however when DECODE compares NULL with NULL result is TRUE.
5. CASE expects datatype consistency, DECODE does not.
6. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

Example 1: DECODE and CASE treats NULL differently

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

            CASE NULL WHEN NULL THEN -1 END case

 FROM  DUAL;
/

Output:

  DECODE       CASE
---------- ----------

        -1

Example 2: CASE expects datatype consistency, DECODE does not

SELECT  CASE 2 WHEN 1 THEN 1 WHEN to_number('2') THEN to_number('2') END case
FROM  DUAL
/
Output:
      CASE
----------
         2

SELECT  DECODE(2,1,1,'2','2') decode

FROM  DUAL

/

DECODE

-------

     2

SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END case

FROM  DUAL

/

SELECT  CASE 2 WHEN 1 THEN 1 WHEN '2' THEN '2'  END case

                                  *

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR