What are the differences between CASE and DECODE in SQL?

Main differences between Case and Decode statements are:

  • Easier to Read: CASE is more flexible and easier to read than DECODE.
  • ANSI Compatible: CASE is an ANSI standard. But DECODE is internal to Oracle.
  • Location: DECODE is used only inside SQL statement. We can use CASE any where in SQL, even as a parameter of a function/procedure.
  • Check: DECODE works on the basis of an equality check. CASE can do many types of logical comparisons like < > etc.
  • Decision Making: We can not use complex decision making statements in a DECODE function. We cannot do decode( price = 100,’cheap’,10000,’expensive’,’ok’ )
  • Different Types; DECODE can take different types of expressions. But CASE has only one type of expression.

Leave a Reply

Your email address will not be published. Required fields are marked *