What is Difference between Decode and Case statement ?

In my previous article i have given the many examples of difference between technically.In this article i would like to throw light on Difference between Decode and Case statement with multiple real life scenarios.The short form of if..else is Oracle case or decode statement in oracle.

What we will cover in this article?

1. Decode,Case Function with Syntax and usages
2. Difference with real examples
3. Difference in tabular format

Decode Statement is faster than Case Statement when it passes from optimizer

Decode ,Case Function with Syntax and usages :

1.Both oracle function and oracle case functions are important functions which are used to transform the values from single value to another separate value.

2.The Oracle as well as Oracle case statement will give us the transformation of values in following format.

if Variable = Value1 then Value/variable/statement
else if Variable= Value2 then Value/variable/statement
else Value/variable/statement;

3.Both Decode as well as Case statement allows us to create the indexes on the multiple columns which are not exists in the database . This feature will be used to improve the performance of query.

4.Oracle started the decode functions in its oracle 9i version but they have defined the different functionality of case statement where they have added the decode functionalities and more functionalities than decode in CASE Statement.

5.Decode is oracle system defined function and case statement is the statement.

6.Decode function can not work other than equal to operator whereas case statement will work with other operators like < Less than > Greater than = Equal to == Equal to equal to.

7.Syntax : Decode Statement

Decode (Column_name, Value_1, Mapping_1,Value_2,Mapping_2….Value_N,Mapping_N);

Example of Decode Statement :

select
decode (
Gender,
‘M’,’Male’,
‘F’,’Female’,
‘O’,’Other’,
‘Dont_Know’
)
from
Employee;

8.Syntax : Case Statement

Case When Statement1 then value1

else when Statement2 then value2

else value3

End;

Example of case statement :

Select case when ‘M’ then ‘Male’

when ‘F’ then ‘Female’ else

‘Others’ As ‘Gender’ from Employee;

Difference between Decode and Case statement with real examples

1.Case Statement is working with other logical operators except ‘=’ equal to operator

The Decode function is used to perform only equality condition. The Case statement is capable of using other operators as well rather than equal to operator.

Example :

Select Employee_name,

Case When Salary > 10000 and Salary < 20000 Then ‘Class-2’

When Salary >=20000 then ‘Class-1’

else ‘Class-3’

End As ‘Class_of_Employee’

From Employee;

The above query will give us information about the Class of Employee. Here you can see that we have used the greater than and other operators.

2.Case statement can work with sub-queries and predicates as well

We have limited functionality in decode as the decode function only uses the scalar values.Oracle overcomes this in case statement and case statement can work with the sub-queries as well as searchable values.

Select Employee_id ,

Case when Employee_name in (‘Amit’,’Pradnya’,’Rahul’) then ‘Management People’ /*This is In predicate*/

when Employee_name exists (select Employee_name from Employee where manager_code is not null)

/* You can use sub-queries as well*/

then ‘Managers’

else ‘Employees’ end

From Employee;

3.You can use Case statement in PL SQL

The case statement you can use in PL SQL but as decode is function then you require to use only in SQL statements. You can also use in PLSQL but not like Case statement. You always need to use it in select statement.

PL SQL block :

Declare

Marks Number(10);

begin Case When Marks <=80 Then ‘A’ when Marks <=60 and Marks <=79 then ‘B’ When Marks>=40 and Marks<=59 then ‘C’ else ‘D’

End;

End;

The above PL SQL block is used to check the marks and give grades accordingly.

4.Case when statement can be used in procedure calls.

You can also use the Case..When statement in procedure calling. The decode statement will give the error when you use it in procedure calls.

Assumption : Procedure named P_Customer_database is there in the database.

Exec P_Customer_database(decode(name,’Amit’,’Male’,Female));

The above statement will give you the error like,

ORA-06550: line 1, column17:PLS-00204: functionorpseudo-column’DECODE’may be used inside a SQLstatement only

But you can use case when statement,

Exec P_Customer_database(case when name=’Amit’ then ‘Male’ else ‘Female’);

The above procedure will execute successfully.

These are some most important examples of Case and decode statements.

Table Format :

Decode Statement	Case Statement
Decode is oracle system function	Case is oracle statement not a function
Decode can not work other than = Equal to operator	Case statement can work other than equal to operator
Decode Statement can not work with the Sub-queries and predicates	Case statement works with sub-queries and predicates
Decode can only be used in function inside SQL only.	Case statement can be used in PL SQL blocks
You can not use Decode function in stored procedures call	You can use case statement in procedure calls
Case statement Compiles ANSI SQL statement	Decode is nothing but the proprietary of oracle

I hope you like this detailed level article; If you like this article or if you have any comments kindly comment in comments section.