DECODE Vs. CASE

What is the Difference Between DECODE and CASE? Which one is useful and when?

DECODE and CASE statements in Oracle both provide a conditional construct, of this form:

if A = n1 then A1
else if A = n2 then A2
else X

Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this blog.

Some differences between CASE and DECODE:

•  CASE is a statement while DECODE is a function.
•  CASE can work with logical operators other than ‘=’ :   
•  DECODE performs an equality check only. CASE is capable of other logical comparisons such as < ,> ,BETWEEN , LIKE etc. 

• CASE can work with predicates and searchable sub queries.   
• DECODE works with expressions that are scalar values only. CASE can work with predicates and sub queries in searchable form.

• CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.
• DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN- ELSE in PL/SQL. In below example, if you replace CASE with DECODE then it gives error.

• CASE can be used as parameter of a function/procedure. CASE can even work as a parameter to a procedure call, while DECODE cannot.

                      Exec MyProc(case :A when 'THREE' then 3 else 0 end);

                      Above statement will not give error but below statement gives error.

                      Exec MyProc(decode(:a,'THREE',3,0));

• CASE expects datatype consistency, DECODE does not.

• CASE handles NULL differently.

• CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
• CASE executes faster in the Optimizer than does DECODE. 

Based on above comparison, you can identify which one will be the best use for your requirement.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.