The Difference Between DECODE and CASE

July 19, 2010

in FAQ, Keywords, PL/SQL, SQL

Difference Between DECODE and CASE

DECODE and CASE statements in Oracle both provide a conditional construct, of this form:
if A = n1 then A1
else if A = n2 then A2
else X

Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.

Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.

1. CASE can work with logical operators other than ‘=’

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.

An example of putting employees in grade brackets based on their salaries. This can be done elegantly with CASE.

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;

ENAME      SAL_GRADE
---------- ---------
SMITH      Grade I
ALLEN      Grade II
WARD       Grade II

2. CASE can work with predicates and searchable subqueries

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           -- set 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 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(decode(:a,'THREE',3,0));
BEGIN proc_test(decode(:a,'THREE',3,0)); END;

                *
ERROR at line 1:
ORA-06550: line 1, column 17:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL
statement only
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


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

PL/SQL procedure successfully completed.

4. Careful! CASE handles NULL differently

Check out the different results with DECODE vs NULL.

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

NULL
----
NULL

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

NULL_TES
--------
NOT NULL

The “searched CASE” works as does DECODE.


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

NULL_TES
--------
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.

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

6. CASE is ANSI SQL-compliant

CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

7. The difference in readability

In very simple situations, DECODE is shorter and easier to understand than CASE.

SQL> -- An example where DECODE and CASE
SQL> -- can work equally well, and 
SQL> -- DECODE is cleaner

SQL> select ename
  2       , decode (deptno, 10, 'Accounting',
  3                         20, 'Research',
  4                         30, 'Sales',
  5                             'Unknown') as department
  6  from   emp
  7  where rownum < 4;

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

SQL> select ename
  2       , case deptno
  3           when 10 then 'Accounting'
  4           when 20 then 'Research'
  5           when 30 then 'Sales'
  6           else         'Unknown'
  7           end as department
  8  from emp
  9  where rownum < 4;

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

Complicated logical comparisons in DECODE, even if technically achievable, are a recipe for messy, bug-prone code. When the same can be done more cleanly with CASE, go for CASE.

[Photo by natematias]

{ 71 comments… read them below or add one }

1 mahesh kumar December 14, 2011 at 1:50 pm

veny nice explanation

2 subhashini March 22, 2012 at 12:46 pm

Thanks for providing the information very precisely:)

3 varun April 12, 2012 at 1:05 pm

brilliant explanations 🙂

4 Gurujothi April 30, 2012 at 9:36 am

Very clear Expalanation,Thank you.

5 Dinup May 4, 2012 at 10:14 am

Excellent explanation..hats off…….Thanks

6 abhishek June 5, 2012 at 12:58 pm

nice explaination

7 Fazarudeen June 15, 2012 at 3:29 pm

Very nice and excellent explanation.
Thanks a lot.

8 Rajeev Thakur June 17, 2012 at 12:07 am

Brilliant explanation..it really easy to understand ..!!!
Thnx a lot.

9 Jayant phad November 25, 2012 at 8:52 pm

Very nice explanation . Awesome. Explained very properly.

10 praveen November 27, 2012 at 7:38 pm

Excellent!!!!!!!!!!!!!!

11 Vijay Begur May 14, 2013 at 7:58 pm

Clear and Concise, Thanks

12 ramesh September 2, 2013 at 7:29 pm

good and super…

13 Shilpi September 4, 2013 at 11:48 pm

Excellent!!!!..Thanks a lot 🙂

14 Mohamed Shahid September 17, 2013 at 10:02 pm

Super Expalnation

15 isaiahbabu September 21, 2013 at 11:21 pm

nice explanation dude..
thanks alot

16 Ramesh October 10, 2013 at 12:19 am

nice explanation dude..
thanks alot

17 soumya October 29, 2013 at 8:24 pm

good explanations

18 Mehul November 4, 2013 at 9:35 am

Good job…..!!
Thank u so much……….!!!

19 NAGA December 11, 2013 at 10:16 am

Thanks a lot 🙂

20 sandy January 15, 2014 at 12:19 pm

very excellent explanations

21 sanjai February 18, 2014 at 3:44 pm

Good job..

22 Naren March 19, 2014 at 5:19 pm

Excellent Job…

23 GANESH March 25, 2014 at 9:20 am

Very clearly explained and very useful information.
Thank you.

24 shakeer May 9, 2014 at 11:31 am

Thanks man , very nice explanation

25 KUNAL May 23, 2014 at 8:14 pm

Its very usefull explanation……. Thank you DUDE

26 Ramakrishna.Bala May 29, 2014 at 2:17 am

Superb man.

Atleast I have learned one new thing out of the ocean of knowledge.

27 ravikanth June 14, 2014 at 9:18 am

Very nice way of explanation… 🙂

28 prashant mishra June 26, 2014 at 3:17 pm

Could you please expalin point 4th (i.e 4. Careful! CASE handles NULL differently)
it is good example but there is no explanation for example..tell me the logic behind the different outputs.

29 anjum June 28, 2014 at 9:27 pm

perfect answers and good explanation

30 sindhu June 29, 2014 at 6:57 am

very good explanation.
Thanks for sharing this info.

31 sumalatha August 20, 2014 at 6:40 pm

Explanation given is informative and simple.:)

32 hardik December 2, 2014 at 5:06 pm

Excellent explanation !!!

33 pavi January 8, 2015 at 8:19 am

Excellent…!!!

34 siddhu January 15, 2015 at 4:52 pm

Excellent superbb…

35 Garuda January 23, 2015 at 12:46 pm

Its very usefull explanation…….

Please post this kind of things…

36 joswa February 19, 2015 at 12:45 pm

Explanation given is simple to understand , good job

37 Venkadesh February 20, 2015 at 1:32 pm

Good one… covered all 😉

38 Pankaj April 8, 2015 at 10:43 am

Very nice artical…:)

39 Joyjeet Mukherjee May 4, 2015 at 4:29 pm

Very well explained.
Thanks. 🙂

40 koti June 3, 2015 at 8:08 pm

very nice

41 chidananda June 5, 2015 at 1:12 pm

really very nice one

42 rizwan June 6, 2015 at 2:22 am

Very Good Explanation..!!

43 vasanth July 23, 2015 at 4:17 pm

Good article….. and also solved my problem.

44 Gaurav Khurana August 29, 2015 at 7:48 pm

One of the best pieces of articles read on the internet. Thanks for all the work did behind this post

45 srilatha September 7, 2015 at 5:58 pm

o’sum article and very nice explanation .Thank u

46 shanmugapriya September 28, 2015 at 7:25 pm

Superb explanation. Thanks a lot!!!!

47 Narendar October 29, 2015 at 3:41 pm

Thank you very much……………..Good explanation……..

48 mohan December 8, 2015 at 12:52 pm

very good explanation : )

49 Hari Shirsat February 17, 2016 at 8:45 pm

Superbly , Excellent explanation. Thanks .

50 Shekhar February 26, 2016 at 11:14 am

Well done, very precisely explained differences. I really did not knew that CASE and DECODE has so many differences. Now obviously Case is superior candidate to be used in decision making constructs.
Thanks a lot.

51 Jay Prakash March 31, 2016 at 12:24 pm

Very good information and that too precisely.

52 chenna May 7, 2016 at 1:32 pm

superb…………

53 Karthik May 31, 2016 at 11:58 am

Awesome ! Good Explanation ! Thanks for the info !

54 Dhiraj June 13, 2016 at 9:47 am

Helpful !!! Used to ask in every interview

55 aarti June 28, 2016 at 2:56 pm

good explanation.

56 Abinash July 11, 2016 at 7:10 am

well explained…….very helpful…so simple.
thanks for that.

57 ramkishan November 7, 2016 at 11:12 am

Good explanation

58 nilesh November 18, 2016 at 10:15 am

nice explanation.

one diff is there decode is function and case is expression

59 rammohan November 18, 2016 at 5:45 pm

excellent ..very very good explanation

60 nupu0109 November 20, 2016 at 10:39 am

Wonderful explanation..Many Thanks!

61 raju December 15, 2016 at 5:09 pm

excellent ………

62 TRIRATNA January 4, 2017 at 9:29 pm

very helpful.. Thanks a lot.!!!

63 Nandhini January 19, 2017 at 3:27 pm

well explained with examples

64 ramkishan March 24, 2017 at 10:15 am

Nice explanation, thank you

Ramkishan

65 Hariprasad October 17, 2017 at 11:18 pm

Excellent!!!

66 Abhishek Jaiswal March 8, 2018 at 7:36 pm

Great explanation

67 Likitha July 14, 2018 at 1:10 am

Very nicely explained in depth.. Thankyou……!!!

68 Bhagyashree September 28, 2018 at 11:39 pm

Very well explained… thank you !!

69 MK December 27, 2019 at 3:20 pm

Excellent

70 Abhinav Singh June 30, 2021 at 10:43 am

crisp and clear explanation

71 Ramesh Karnewar June 14, 2023 at 1:05 am

well explained with so many Examples, very helpful , many thanks

Leave a Comment

Previous post:

Next post: