Decode and Case

Decode and Case

Difference Between DECODE and CASE :- Both are used for conditional construct in SQL.

1. CASE can work with logical operators other than ‘=’ while DECODE performs an equality check only.
  
Example:- To display grade based on their salaries.

SQL>select ename, case when sal < 1000  then 'A'
            when (sal >=1000 and sal < 2000)  then 'B'
            when (sal >= 2000 and sal < 3000) then 'C'
            else 'D'
            end sal_grade
from emp where rownum < 6;

Output:-
ENAME    SAL    SAL_GRADE
SMITH    800    A
ALLEN    1600    B
WARD    1250    B
JONES    2975    C
MARTIN    1250    B


2. CASE can work with predicates (like ,in etc) and searchable subqueries

DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.

Example:-

SQL>select e.ename,case when e.job in ('PRESODENT')  then 'Boss'
                        when e.job in ('MANAGER')    then 'Managers'
                        else 'General Employees'
                        end emp_category
from emp e
where rownum < 6;


ENAME    EMP_CATEGORY
SMITH    General Employees
ALLEN    General Employees
WARD    General Employees
JONES    Managers
MARTIN  General Employees



3. CASE can work as a PL/SQL construct

DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.

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  /

PL/SQL procedure successfully completed.

CASE can even work as a parameter to a procedure call, while DECODE cannot.

SQL> var a varchar2(5);
SQL> exec :a := 'THREE';

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace procedure proc_test (i number)
  2  as
  3  begin
  4    dbms_output.put_line('output = '||i);
  5  end;
  6  /

Procedure created.


SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end);
output = 3

PL/SQL procedure successfully completed.

4. NULL handled in different way by both


select decode(null, null, 'NULL', 'NOT NULL') test from dual;

TEST
----
NULL

select case null when null then 'NULL'
              else 'NOT NULL'
                      end test
from dual;

TEST
--------
NOT NULL

Below case query will work same as decode

select case when null is null then 'NULL'
        else 'NOT NULL'
        end test
from dual

TEST
--------
NULL

5. CASE expects datatype consistency, DECODE does not

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

select decode(2,1,1,'2','2','3') t from dual;

       T
--------
       2

select case 2 when 1 then 1
              when '2' then '2'
              else '3'
              end t
from dual;


ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:   
*Action:


6. CASE is ANSI SQL-compliant and decode is the Oracle proprietary

7. The DECODE is shorter and easier to understand than CASE.
Example:-
select ename, decode (deptno, 10, 'Accounting',
                  20, 'Research',
                  30, 'Sales',
                                  'Unknown') as department
from   emp
where rownum < 6;

ENAME      DEPARTMENT
---------- ----------
SMITH    Research
ALLEN    Sales
WARD    Sales
JONES    Research
MARTIN    Sales

select ename, case deptno when 10 then 'Accounting'
              when 20 then 'Research'
              when 30 then 'Sales'
              else         'Unknown'
              end as department
from emp
where rownum < 6;

ENAME      DEPARTMENT
---------- ----------
SMITH    Research
ALLEN    Sales
WARD    Sales
JONES    Research
MARTIN    Sales