What is the difference between Decode() and case in Oracle?

They appear to do same task but there are many differences between them: Case can do everything Decode can do and a lot else. Decode is used to substitute code with expression in most of the time. In addition to this decode use only '=' operator and case work with different logical operators. Decode work with expression that are scalar value and case can work with Predicates and sub queries. Case can be also used in PL/SQL in substitute for IF-THEN-ELSE. Case expects data type consistency and decode does not.

decode compares the values based on output where as in case output should be match with input.

decode is more easier to read.

case excepts datatype consitency where as decode does not expect datatype consistency.

We can use decode for complicated logical expressions where as case can be used for other conditions except =.

1)DECODE performs an equality check only. CASE is capable of other logical comparisons such as < > etc. It takes some complex coding – forcing ranges of data into discrete form – to achieve the same effect with DECODE.

2) DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.

Decode was old operator. Oracle introduced case from version 8 or 9.

Decode can change SQL results. So better to use CASE.

DECODE works with expressions that are scalar values only and But CASE can be used any where even as a parameter of a function and procedure

DECODE works with expressions that are scalar values only and The case function is a much better option because case is More flexible than DECODE, Easier to read and ANSI-compatible

DECODE works with expressions which are scalar values.CASE can work with predicates and subqueries in searchable form.Oracle says CASE executes faster in the optimizer than does DECODE.> We cant use for range(like 2000 to 4000 etc.) in decode, but in case we can.> DECODE can be used Only inside SQL statement....> But CASE can be used any where even as a parameter of a function/procedure> CASE handles NULL values differently.

DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form

Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.

DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form.

The difference between decode and case are straightforward. While thedecode operator has been around since the earliest days of Oracle, the caseoperator was introduced in Oracle 8.1.6. While d3code and case can be used interchangeably, the decode is more powerful because decode can change SQL results.

The decode and case functions are used within the Oracle database to transform data values for one value to another and the case expression can do all that decode does along with what IF-THEN analysis does