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”
*Cause:
*Action:
Error at Line: 27 Column: 23

But below statement with CASE will work properly

select salary,empno,
case
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

21 responses to “Difference between DECODE and CASE in Oracle”

  1. CASE is a statement which will executes faster than the DECODE, because DECODE is function.

    1. Thanks for the comment. But I don’t think your statement is true.

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

  2. Thanks for the comment. But I don’t think your statement is true.

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

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

    1. Thats true. Thanks for the comment

      1. In Decode we can not use relatioal operators but in Case allows relational operators

  4. Thats true. Thanks for the comment

    1. In Decode we can not use relatioal operators but in Case allows relational operators

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

  6. When ever we compare null with another null decode returns “TRUE” and case returns “FALSE”

    1. Hi,

      I tried this,but both are returns false only

      Can u plz provide some examples on this

      1. Your question is not clear, elaborate it.

  7. decode is a function
    where case is a expression
    case is faster wen compare to decode.

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

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

  10. Hi,

    can u please explain the performance tuning and optimization.

  11. In Decode we can not use relatioal operators but in Case allows relational operators

  12. Hi,

    I tried this,but both are returns false only

    Can u plz provide some examples on this

    1. Your question is not clear, elaborate it.

  13. Your question is not clear, elaborate it.

  14. 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);

  15. -> In case statement we can use operators but come to decode not possible to use operators..

  16. can u please explain the performance tuning and optimization

  17. Hello There,
    The challenge however, is we don’t yet know how it will be used making to hard to assess their value proposition and consequently value.
    how to apply precursor in package with multiple
    instead of multiple cursors in oracle? Give one example.
    Thank you very much and will look for more postings from you.
    Regards,
    Henry

  18. […] Difference between DECODE and CASE 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 […]