CASE vs. DECODE

Question

Referring to a previous question, i was wondering if its always possible to replace DECODE by CASE and which one is better for performance?

Answer 1

There is one big difference between DECODE and CASE and it has to do with how NULLs are compared. DECODE will return "true" if you compare NULL to NULL. CASE will not. For example:

DECODE(NULL, NULL, 1, 0) 

will return '1'.

CASE NULL     WHEN NULL THEN 1     ELSE 0 END 

will return '0'. You would have to write it as:

CASE     WHEN NULL IS NULL THEN 1     ELSE 0 END 

Answer 2

As always with Oracle ... AskTom...

From this post...

Decode is somewhat obscure -- CASE is very very clear. Things that are easy to do in decode are easy to do in CASE, things that are hard or near impossible to do with decode are easy to do in CASE. CASE, logic wise, wins hands down.

From a performance point of view seems they are about the same, again above article mentions some speed differences but without benchmarking the particular statements it's hard to say.