Regarding DECODE FUNCTION VS CASE and EXISTS VS IN

DECODE FUNCTION VS CASE:

Decode Function and Case Statement in Oracle: Decode Function and Case Statement are used to transform data values at retrieval time. DECODE and CASE are both analogous to the "IF THEN ELSE" conditional statement.

History of DECODE and CASE:

Before version 8.1, the DECODE was the only thing providing IF-THEN-ELSE functionality in Oracle SQL. Because 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). In version 9.0, Oracle introduced the simple CASE statement, that reduces some of the verbosity of the CASE statement, but reduces its power to that of DECODE.

Decode Function and Case Statement Example: 

Example with DECODE function

Say we have a column named REGION, with values of N, S, W and E. When we run SQL queries, we want to transform these values into North, South, East and West. Here is how we do this with the decode function:

select

decode (

region,

‘N’,’North’,

‘S’,’South’,

‘E’,’East’,

‘W’,’West’,

‘UNKNOWN’

)

from

customer;

Note that Oracle decode starts by specifying the column name, followed by set of matched-pairs of transformation values. At the end of the decode statement we find a default value. The default value tells decode what to display if a column values is not in the paired list.

Example with CASE statement

select

case

region

when ‘N’ then ’North’

when ‘S’ then ’South’

when ‘E’ then ’East’,

when ‘W’ then ’West’

else ‘UNKNOWN’

end

from

customer;

Difference between DECODE and CASE:

Everything DECODE can do, CASE can. There is a lot more that you can do with CASE, though, which DECODE cannot. Differences between them are listed below:

1. DECODE can work with only scaler values but CASE can work with logical oprators, predicates and searchable subqueries.

2. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.

3. CASE expects datatype consistency, DECODE does not.

4. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

5. CASE executes faster in the optimizer than does DECODE.

6. CASE is a statement while DECODE is a fucntion.

CASE can work with logical operators other than ‘=’

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.

An example of putting employees in grade brackets based on their salaries. This can be done elegantly with CASE.

SQL> select ename

  2       , case

  3           when sal < 1000

  4                then 'Grade I'

  5           when (sal >=1000 and sal < 2000)

  6                then 'Grade II'

  7           when (sal >= 2000 and sal < 3000)

  8                then 'Grade III'

  9           else 'Grade IV'

 10         end sal_grade

 11  from emp

 12  where rownum < 4;

ENAME      SAL_GRADE

---------- ---------

SMITH      Grade I

ALLEN      Grade II

WARD       Grade II

CASE can work with predicates and searchable subqueries

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

An example of categorizing employees based on reporting relationship, showing these two uses of CASE.

SQL> select e.ename,

  2         case

  3           -- predicate with "in"

  4           -- mark the category based on ename list

  5           when e.ename in ('KING','SMITH','WARD')

  6                then 'Top Bosses'

  7           -- searchable subquery

  8           -- identify if this emp has a reportee

  9           when exists (select 1 from emp emp1

 10                        where emp1.mgr = e.empno)

 11                then 'Managers'

 12           else

 13               'General Employees'

 14         end emp_category

 15  from emp e

 16  where rownum < 5;

ENAME      EMP_CATEGORY

---------- -----------------

SMITH      Top Bosses

ALLEN      General Employees

WARD       Top Bosses

JONES      Managers

CASE can work as a PL/SQL construct

DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL.

SQL> declare

  2    grade char(1);

  3  begin

  4    grade := 'b';

  5    case grade

  6      when 'a' then dbms_output.put_line('excellent');

  7      when 'b' then dbms_output.put_line('very good');

  8      when 'c' then dbms_output.put_line('good');

  9      when 'd' then dbms_output.put_line('fair');

 10      when 'f' then dbms_output.put_line('poor');

 11      else dbms_output.put_line('no such grade');

 12    end case;

 13  end;

 14  /

PL/SQL procedure successfully completed.

CASE can even work as a parameter to a procedure call, while DECODE cannot.

SQL> var a varchar2(5);

SQL> exec :a := 'THREE';

PL/SQL procedure successfully completed.

SQL>

SQL> create or replace procedure proc_test (i number)

  2  as

  3  begin

  4    dbms_output.put_line('output = '||i);

  5  end;

  6  /

Procedure created.

SQL> exec proc_test(decode(:a,'THREE',3,0));

BEGIN proc_test(decode(:a,'THREE',3,0)); END;

                *

ERROR at line 1:

ORA-06550: line 1, column 17:

PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL

statement only

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> exec proc_test(case :a when 'THREE' then 3 else 0 end);

output = 3

PL/SQL procedure successfully completed.

Careful! CASE handles NULL differently

Check out the different results with DECODE vs NULL.

SQL> select decode(null

  2              , null, 'NULL'

  3                    , 'NOT NULL'

  4               ) null_test

  5  from dual;

NULL

----

NULL

SQL> select case null

  2         when null

  3         then 'NULL'

  4         else 'NOT NULL'

  5         end null_test

  6  from dual;

NULL_TES

--------

NOT NULL

The “searched CASE” works as does DECODE.

SQL>  select case

  2         when null is null

  3         then 'NULL'

  4         else 'NOT NULL'

  5         end null_test

  6* from dual

SQL> /

NULL_TES

--------

NULL

CASE expects datatype consistency, DECODE does not

Compare the two examples below- DECODE gives you a result, CASE gives a datatype mismatch error.

SQL> select decode(2,1,1,

  2                 '2','2',

  3                 '3') t

  4  from dual; 

         T

----------

         2 

SQL> select case 2 when 1 then '1'

  2              when '2' then '2'

  3              else '3'

  4         end

  5  from dual;

            when '2' then '2'

                 *

ERROR at line 2:

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

CASE is ANSI SQL-compliant

CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

7. The difference in readability

In very simple situations, DECODE is shorter and easier to understand than CASE.

SQL> -- An example where DECODE and CASE

SQL> -- can work equally well, and 

SQL> -- DECODE is cleaner

SQL> select ename

  2       , decode (deptno, 10, 'Accounting',

  3                         20, 'Research',

  4                         30, 'Sales',

  5                             'Unknown') as department

  6  from   emp

  7  where rownum < 4;

ENAME      DEPARTMENT

---------- ----------

SMITH      Research

ALLEN      Sales

WARD       Sales

SQL> select ename

  2       , case deptno

  3           when 10 then 'Accounting'

  4           when 20 then 'Research'

  5           when 30 then 'Sales'

  6           else         'Unknown'

  7           end as department

  8  from emp

  9  where rownum < 4;

ENAME      DEPARTMENT

---------- ----------

SMITH      Research

ALLEN      Sales

WARD       Sales

IN VS EXISTS

Sample example at which situation

IN is better than exist, and vice versa. 

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select * 

  from t1, ( select distinct y from t2 ) t2

 where t1.x = t2.y;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to 

the original table -- typically.

As opposed to 

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:

   for x in ( select * from t1 )

   loop

      if ( exists ( select null from t2 where y = x.x )

      then 

         OUTPUT THE RECORD

      end if

   end loop

It always results in a full scan of T1 whereas the first query can make use of an index 

on T1(x).

ABOUT EXISTS:- 

Lets say the result of the subquery

    ( select y from T2 )

is "huge" and takes a long time.  But the table T1 is relatively small and executing ( 

select null from t2 where y = x.x ) is very very fast (nice index on t2(y)).  Then the 

exists will be faster as the time to full scan T1 and do the index probe into T2 could be 

less then the time to simply full scan T2 to build the subquery we need to distinct on.

Lets say the result of the subquery is small -- then IN is typicaly more appropriate.

If both the subquery and the outer table are huge -- either might work as well as the 

other -- depends on the indexes and other factors.