Series: Subject: Topic:
Question: 103 of 193

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

Asked by: Interview Candidate | Asked on: Jul 17th, 2006
Showing Answers 1 - 11 of 11 Answers

Answered On : 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

Login to rate this answer.

Answered On : 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.

Login to rate this answer.

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

Yes  2 Users have rated as useful.
Login to rate this answer.

Answered On : Aug 28th, 2006

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

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

Login to rate this answer.

Answered On : Oct 27th, 2006

Why cant we use CASE for checking nulls

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

Login to rate this answer.

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;


Login to rate this answer.

Answered On : 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.

Yes  1 User has rated as useful.
Login to rate this answer.

Answered On : 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.

Login to rate this answer.

Answered On : Jul 14th, 2009

View all answers by jaspreet.nagra

Hi Murali,

You can also use Decode in where clause:

select * from dual
where decode( null, null, 1, 0)=1;

Login to rate this answer.

Answered On : Nov 17th, 2009

View all answers by joyfun23

select 'RAGHAV' from dual where dummy =(
select CASE when 'A' ='A'

'X' else 'Y'
end from dual

CASE Works in where clause and Decode too!!

Yes  1 User has rated as useful.
Login to rate this answer.

Answered On : Dec 21st, 2009

View all answers by Sirajuddin84

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.

Login to rate this answer.

Give your answer:

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

Related Open Questions



twitter fb Linkedin GPlus RSS


Interview Question

 Ask Interview Question?


Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.