What is the difference between CASE and DECODE?

Oracle Apps Interview QuestionsCategory: PL/SQLWhat is the difference between CASE and DECODE?
Questions Master asked 5 years ago
2 Answers
Shailender Thallam Staff answered 5 years ago

Below are the major differences between CASE and DECODE:
 

  1. DECODE is Oracle standard and CASE is ANSI standard.
  2. DECODE is a function where CASE is a Expression.
  3. CASE is Faster when compared to DECODE since DECODE is a function which takes time to load and run but the cost difference of DECODE and CASE is very very minimal.
  4. Both CASE and DECODE can be used in WHERE clause.
  5. CASE requires all return expressions to be of same base type. DECODE doesn’t. DECODE result type is first decoded expression type, all others are implicitly converted (if needed). DECODE considers two nulls to be equivalent while CASE doesn’t.
  6. Relational operators can’t be used in DECODE. like  decode(  sal >1000,’high’,10000,’good’,’ok’).
  7. CASE can be directly used in PL/SQL but DECODE can be used in PL/SQL through SQL statements only.
  8. CASE can work with predicates and searchable sub queries
Shailender Thallam Staff answered 5 years ago

Below are the major differences between CASE and DECODE:

  1. DECODE is Oracle standard and CASE is ANSI standard.
  2. DECODE is a function where CASE is a Expression.
  3. CASE is Faster when compared to DECODE since DECODE is a function which takes time to load and run but the cost difference of DECODE and CASE is very very minimal.
  4. Both CASE and DECODE can be used in WHERE clause.
  5. CASE requires all return expressions to be of same base type. DECODE doesn’t. DECODE result type is first decoded expression type, all others are implicitly converted (if needed). DECODE considers two nulls to be equivalent while CASE doesn’t.
  6. Relational operators can’t be used in DECODE. like  decode(  sal >1000,’high’,10000,’good’,’ok’).
  7. CASE can be directly used in PL/SQL but DECODE can be used in PL/SQL through SQL statements only.
  8. CASE can work with predicates and searchable sub queries