DECODE can be used Only inside SQL statement, But CASE can be used any where even as a parameter of a function/procedure

DECODE can only compare discrete values (not ranges), continuous data had to be contorted into discreet values using functions like FLOOR and SIGN. In version 8.1, Oracle introduced the searched CASE statement, which allowed the use of operators like > and BETWEEN (eliminating most of the contortions) and allowing different values to be compared in different branches of the statement (eliminating most nesting).

CASE is almost always easier to read and understand, and therefore it's easier to debug and maintain.

Another difference is, CASE is an ANSI standard, where as Decode is proprietary for Oracle.

Performance wise there is not much differences. But Case is more powerful than Decode.

The main problem with DECODE is that we can compare only discrete values and not ranged values. So they introduced the CASE statements from oracle 8i onwards. This CASE was able to handle ranged values by using >, <, BETWEEN.

Hence the difference between the DECODE and CASE is that DECODE cannot be used handle Ranged values, whereas CASE is capleable of that.

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.