Difference between DECODE and CASE in Oracle

DECODE function and CASE statement are very similar but CASE is an extended version of DECODE. CASE is introduced in Oracle 9i version. I would say there can be two differences. Wait a minute. Is it a difference ? not sure. still………..

1. Decision making statements cannot be used in the place of DECODE expression

DECODE(expression,search1, result1, search2, result2,…., searchN, resultN, default)

Below statement will return an error

select decode(  salary = 12000,’high’,10000,’good’,’ok’  )  as decode_test from emp where empno in (10,11,12,13,14,15);

ORA-00907: missing right parenthesis

00907. 00000 – “missing right parenthesis”
Error at Line: 27 Column: 23

But below statement with CASE will work properly

select salary,empno,
when salary = 12000 then ‘high’
when salary = 10000 then ‘good’
else ‘ok’
end salary_details
from emp e where e.empno in (10,11,12,13,14);

2. CASE can be directly used in PL/SQL but DECODE can be used in PL/SQL through SQL only

Note : – All examples tested in Oracle 11g Release 2.

Any other difference ? Please comment.

Oracle Cursors
Find Nth highest salary
Polymorphism in Oracle
Install Notepad++ in Ubuntu
Oracle 12c New Features
Oracle Interview questions and answers
5 Different ways to delete records Oracle


Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

17 thoughts on “Difference between DECODE and CASE in Oracle”

      1. I Think decode processing time faster because decode is a function and case is a expression

  1. In case you can specify complex conditions not only consider equality for one value (for example >= or an other conditions too)

  2. decode function logic always works on equality operator but searched case expression works on multiple condition for evaluation

  3. you can use use the like operator for comparison in ‘case statements’ however can’t be used in decode

  4. There is an error:

    select decode( salary = 12000,’high’,10000,’good’,’ok’ ) as decode_test from emp where empno in (10,11,12,13,14,15);

    The correct statment is:

    select decode( salary,12000,’high’,10000,’good’,’ok’ ) as decode_test from emp where empno in (10,11,12,13,14,15);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s