7 Key Difference between CASE and DECODE in Oracle

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 common Conditional Block is like

IF x=y then
    do Z
Else if  a<> b 
     do C
else
do M
End if

And this is what Case and DECODE basically do in PL SQL.

CASE can do what DECODE Can and further it can handle more complex requirements.

CASE VS DECODE

CASEDECODE
CASE is a Statement in SQL / PL SQLDECODE is a function in Oracle that can be used in SQL only
CASE is ANSI SQL-compliantDECODE is proprietary to Oracle.
CASE helps in building conditional logicDECODE 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 blocksDECODE can be part of SQL statements only. Though the SQL can be used in PL SQL
CASE can handle predicates (like IN) and searchable subqueriesDECODE Cannot handle Predicates and searches.
You can use Parameter as CASE statements to procedure callsDecode cannot handle parameters as procedure calls
CASE treats NULL statement differentlyDECODE treats as NULL as NULL 🙂
CASE expects datatype consistency,DECODE does not expect datatype consistency

Let’s see the important ones with example.

CASE can work as a PL/SQL construct

CASE is a Statement in SQL / PL SQLDECODE 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-vs-decode

Conditional Operators in CASE and DECODE

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

case-sql-conditional

Conditional IF THEN ELSE with DECODE

decode-conditional

CASE can work with predicates and searchable subqueries

CASE can handle predicates (like IN) and searchable subqueriesDECODE Cannot handle Predicates and searches.
case-with-subqueries

CASE can even work as a parameter to a procedure call, while DECODE cannot

case-for-paramater-procedure

CASE handles NULL differently. Be Aware.

See the different results with DECODE vs NULL.

DECODE handling of NULL

decode-with-null

CASE handling of NULL

case-handling-null

CASE expects datatype consistency, DECODE does not

With DECODE

decode-no-datatype-check

With CASE

case-data-type-check

Readability Preference of DECODE

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

simple-decode-sql

With CASE sql is little longer

case-sql

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

Oracle documentation on CASE

We will be happy to hear your thoughts

      Leave a reply

      Cloud Technologies Blog
      Logo
      Compare items
      • Total (0)
      Compare
      0
      Shopping cart