DECODE, COALESCE and CASE functions in SQL

DECODE Function:

Ø Decode functions decodes the expression in a similar way of using IF-THEN-ELSE conditional expression.

Syntax:

1.     Decode (Expression, Search1, Result1

                     Search2, Result2

                      Search3, Result3,….. Default)

Explanation:

Ø Decode function decodes the expression by search value.

Ø If the expression is same as the search then, result is returned.

Ø If the expression is does not same to the search then, Default value is returned.

Ø If default is omitted, a null value is returned where a search value does not match any of the result values.

Example:

1.  Select ename,sal,

      Decode(sal,10000,’Sal + 5000’

                   20000, ‘Sal + 10000’

                   30000, ‘Sal + 15000’) Increment

   From emp;

Output:

ENAME	SAL	INCREMENT
Tae	10000	15000
Malavika	5000	5000
Jk	30000	45000

COALESCE() Function:

   Ø COALESCE() function returns the first non-null expression in the expression list.

   Ø User must specify at least two expressions in the expression list.

   Ø If all occurrences of expression evaluate to null, then the function returns null.

  Syntax:

1. COALESCE(Exp1,Exp2,Exp3,…Expn)

Is equivalent to:

1. If exp1 is not null then

        Result := exp1;

   If exp2 is not null then

        Result := exp2;

   If exp3 is not null then

       Result := exp3;

   Else

       Result := NULL;

   End if;

Example:1

1. Select COALESCE(‘A’,’B’,’C’) from dual;

Output:

COALESCE(‘A’,’B’,’C’)

A

Example:2

1. Select COALESCE(‘Null’,’B’,’C’) from dual;

Output:

COALESCE(‘NULL’,’B’,’C’)

B

Example:3

1. Select COALESCE(‘Null’,’Null’,Null’) from dual;

Output:

COALESCE(‘NULL’,’NULL’,NULL’)

Null

For Our clear clarification:

Table: Books

Book_Name	Pages	Price	Discount
SQL	500	500	200
PL/SQL	600		300
DBMS	800	1000

Example:

1. Select COALESCE( Discount, Price, ‘Null’) Price

   from books

   Where Book_name=’SQL’;

Output:

Price

200

 CASE function:

   Ø Evaluates a list of conditions and returns results (like  IF-THEN-ELSE).

   Ø If the condition gets true Case stop the evaluation and shows the result.

   Ø If all the condition gets false it executes the Else statement. There is no Else then return null.

Syntax:

1. Case
     When condition1 Then Result1

     When condition2 Then Result2

     …………

     When conditionN Then ResultN

     Else Result
   End;

Table:

Customers

Customer_id	Customer_Name	Gender
20000	Steven	M
20001	Markov	M
20002	Lisa	F
20003	Riya	F
20004	Vena	F

Example:

1. Select Customer_name ,

    Case

     When Gender = ‘M’ Then ‘Male’

     When Gender = ‘F’ Then ‘Female’

    End as Gender

 Output:

Customer_name	Gender
Steven	Male
Markov	Male
Lisa	Female
Riya	Female
Vena	Female

 

Share: