Difference between decode and case.in which case we are using case and in which case we are using decode?with an example.

Showing Answers 1 - 11 of 11 Answers


  • Jul 19th, 2006

First I will give one example using 'decode'


I have used the decode function on 'deptno' column. It will give the user-friendly output. instead of using 'accounting', 'research'.. We can use anything we want to get the friendly outputs.

I have to check-out the 'case' function after that I will give an example

  Was this answer useful?  Yes


  • Aug 1st, 2006



SELECT CASE DEPTNO   when  10 then 'TEN'
                     when  20 then 'TWANTY'
                     when  30 then 'THIRTY'
                     when  40 then 'FORTY'
              ELSE    'OTHER' END FROM DEPT

Difference between CASE and DECODE

1) Decode is a function while Case statement.

2) Case cannot process null while decode can.

  Was this answer useful?  Yes

Decode                                                                  function

we will use this one in sql                                      we will use this one plsql

it will accept null values                                        it will not accept the null


  • Aug 28th, 2006

One of the measure diff. between decode and case statement :->

Decode does not evaluate inequality operator Where as case can do.

  Was this answer useful?  Yes


  • Oct 27th, 2006

Why cant we use CASE for checking nulls

select case when col1 is null then 'Heyitisnull' else col1 end from case;

  Was this answer useful?  Yes

decode is a function where case is expression .
decode gives result different when using null  see below

SQL> select decode( null,null,1,0) from dual;


  1* select case null when null then 1 else 0 end from dual
SQL> /

but see here
SQL> select case when null is null then 1 else 0 end from dual;


  Was this answer useful?  Yes


  • Aug 13th, 2007


The Main Difference is that
 1) Decode cannot be used in Where clause but Case can.
2)  In Decode Else can be specifed in the statement it self but in Case a seperate statement has to be written.


  • Feb 7th, 2008

  • DECODE can be used Only inside SQL statement, But CASE can be used any where even as a parameter of a function/procedure
  • 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).
  • CASE is almost always easier to read and understand, and therefore it's easier to debug and maintain.
  • Another difference is, CASE is an ANSI standard, where as Decode is proprietary for Oracle.
  • Performance wise there is not much differences. But Case is more powerful than Decode.

  Was this answer useful?  Yes

The main problem with DECODE is that we can compare only discrete values and not ranged values. So they introduced the CASE statements from oracle 8i onwards. This CASE was able to handle ranged values by using >, <, BETWEEN.

Hence the difference between the DECODE and CASE is that DECODE cannot be used handle Ranged values, whereas CASE is capleable of that.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions