7 Key Difference between CASE and DECODE in Oracle
Sometimes Difference between CASE and DECODE in Oracle is a little puzzling as to what to use and when.
While working with data you may need to write SQL for complex reporting which requires CASE and DECODE functionalities. Most of the PL SQL developers have this question of when to use CASE and when to use DECODE while doing PL SQL programming. In this post, we will see the difference between CASE and Decode in SQL from Oracle’s perspective.
Prior to Oracle 8.1.6 it had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more language-oriented with more features. Both CASE and Decode helps us to write complex SQL queries to achieve our desired results. The ORA-06550 error sometimes comes due to conditions getting invalidated.
The common conditional Block is like
IF x=y then
do Z
Else if a<> b
do C
else
do M
End ifAnd this is what Case and DECODE basically do in PL SQL.
| CASE | DECODE |
| CASE is a Statement in SQL / PL SQL | DECODE is a function in Oracle that can be used in SQL only |
| CASE is ANSI SQL-compliant | DECODE is proprietary to Oracle. |
| CASE helps in building conditional logic | DECODE also helps in building conditional logic |
| CASE can use comparison operators like ( IF > ) | Decode cannot use comparison operators (like > ) but the Decode logic is based on Equality |
| CASE can be used as constructs in PL SQL blocks | DECODE can be part of SQL statements only. Though the SQL can be used in PL SQL |
| CASE can handle predicates (like IN) and searchable subqueries | DECODE Cannot handle Predicates and searches. |
| You can use Parameter as CASE statements to procedure calls | Decode cannot handle parameters as procedure calls |
| CASE treats NULL statement differently | DECODE treats as NULL as NULL 🙂 |
| CASE expects datatype consistency, | DECODE does not expect datatype consistency |
Let’s see the important ones with example.
| CASE is a Statement in SQL / PL SQL | DECODE is a function in Oracle that can be used in SQL only |
DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.
SQL with DECODE
select ename , decode (sal, 1000, 'Grade 1',
2000,'Grade 2',
3000,'Grade 3','Normal')
from scott.emp
Anonymous PL SQL block with CASE
| CASE can use Logical operators like ( IF > ) | Decode cannot use Logical operators (like > ) but the Decode logic is based on Equality |
Conditional IF THEN ELSE with CASE
Conditional IF THEN ELSE with DECODE
| CASE can handle predicates (like IN) and searchable subqueries | DECODE Cannot handle Predicates and searches. |
See the different results with DECODE vs NULL.
DECODE handling of NULL
CASE handling of NULL
With DECODE
With CASE
This is one aspect of readability where old programmers still prefer DECODE over CASE despite CASE advantages.
The reason is short coding
Simple SQL with decode
With CASE sql is little longer
Summary of Difference between CASE and DECODE in Oracle
Though DECODE can also be used to achieve the IF THEN ELSE constructs but since it is on equality-based condition the decode expressions become complex if we need to write complex logical conditions whereas with CASE it is easier.
So The Top 3 differences
You may also like Interview questions on PL SQL and fusion application technology
References :
Oracle documentation on DECODE
