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?
- Decode,Case Function with Syntax and usages
- Difference with real examples
- Difference in tabular format
Decode Statement is faster than Case Statement when it passes from optimizer
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
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 :
8.Syntax : Case Statement
Case When Statement1 then value1
else when Statement2 then value2
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.
Case When Salary > 10000 and Salary < 20000 Then ‘Class-2’
When Salary >=20000 then ‘Class-1’
End As ‘Class_of_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*/
else ‘Employees’ end
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 :
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’
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.
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.