I am wondering why we have decode in addition to Case statement. Is there any performance difference? The case statement does whatever decode does. What are the differences between case and code?
I don't know if it's the only reason but CASE is in the ANSI Standard. DECODE is not but DECODE was Oracle's original solution for a CASE-type situation.
Don Burleson offers up this opinion, "As we can see, the decode function is convoluted and hard to write. Oracle added the case function to SQL starting in Oracle9i to simplify this type of data transformation." Here's a link:
Personally, I think CASE is a bit easier to read (unless you don't trouble to format it at all) and it might be easier to find errors in CASE than in the DECODE function.
Also, the CASE expression mirrors the CASE control statement of PL/SQL, so you can learn one syntax instead of two.
As a "non-native Oracle developer," I'm more comfortable with CASE.
1 Person is following this question.