SQL Functions Part6 (Conditional Functions)

Conditional functions are very useful and very important in writing real time SQL queries. They will provide the ability to get the different results in the same query based on the evaluating conditions. This is similar to the IF - THEN - ELSE logic present in other programming languages.

It is not possible to write the IF - THEN - ELSE logic directly in a SELECT statement. So, to achieve the conditional logic in SQL statements, we can use the conditional functions. We will get clear picture on what conditional functions are and how they will work, in below paragraphs.

There are two conditional functions available in SQL. They are,

                                          1.Decode

                                          2.Case

Let us see what each function can do and how we can use them.

Decode Function ::

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

This Decode function is the exact replica / replacement for the simple IF statement in other programming languages which involves single equality comparison operation like following.

if(a=b) then

 result1;

if (a=c) then

 result 2;

else

result 3;

end if;

end if;

To be clear we have added multiple if's , but basically here we are comparing (using = operator)

value of a with all other values using these if conditions. If we have any requirement to get this

type of data we can go for Decode function.

Using this function we can evaluate single condition only and also we can perform equality

operation only.

Syntax for this function is as below.

Decode (column or expression , value1 , result1, value2, result2, .... default result);

Here column or expression corresponds to the table column or expression whose value we want to compare with other values while giving the result (In the above example it is a).

value1, value2,... are the values which we are comparing to the column / expression value (In the above example b, c are those values).These values again can be either columns, expressions or general values.

result1, result2 ... are the results which we want to return when condition matches. These results again can be either columns, expressions or general values.

Default result value is the one which we want to use when all the conditions fail.

Let us see on example on how this function works.

Let us say we have Emp table which has empno, ename, deptno columns in it.

Let us say we know names of all the departments based on their deptno.

Now we want to display all the data from the emp table by including department name also

in the result.

For this we need to compare our values with deptno column.

Query to get this data can be written as the following.

Select empno,

           ename,

           deptno,

           Decode(deptno,10,'Accounting',20,'Sales','Marketing') Dname

From Emp;

So, Here in decode statement we have use he deptno as the comparison column and

10, 20 as the comparison values and 'Accounting' , 'Sales' as the results corresponding to

the comparison evaluation and 'Marketing' as the default result.

Now, let us say if data in Emp table is as below

1234  Ram   10

2345  Raj     20

1256  Balu   30

2367  Somu  20

4567  Suri    10

Result of the above query will be as shown below.

1234  Ram   10   'Accounting'

2345  Raj     20   'Sales'

1256  Balu   30   'Marketing'

2367  Somu  20  'Sales'

4567  Suri    10  'Accounting'

So, basically following if condition is evaluated using this.

If deptno = 10 then 'Accounting'

If deptno = 20 then 'Sales'

else 'Marketing'

So, like this using Decode function we can evaluate equality conditions on single columns in the SQL queries.

Case Function ::

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

Case function is more generic function without any limitations. This means that, we can evaluate

any number of conditions involving any number of columns / expressions using this function. Also we can use any operator in this function. So, this function is exact replica / replacement for the IF - THEN - ELSE logic of other programming languages as below.

IF (a=b and b=c) Then

result1;

elsif (f=o and h=y) then

result2;

else

result3;

End;

Like this if we wan to evaluate multiple conditions on multiple columns / expression to give the

results in SQL queries then the function available is Case Function.

Syntax for this function is as shown below.

(Case When condition1 Then Value1

          When condition2 Then Value2

           ........

          Else  default_value

          End)

Here Condition1, Condition2... are the conditions (can be on single column or multiple

columns and can be combination of multiple conditions also) which we want to evaluate or

check for producing the result.

Here Value1, value2, ... are the results which we want to give when condition is met. These

values again can be either columns, expressions or general values.

default_value present in the Else clause is the one which we want to give as a result when all the conditions failed.

Let us see one example on how this function works. Let us use same example as that of Decode and see how we can write the same using case.

Select empno,

           ename,

           deptno,

           (Case when deptno=10 then 'Accounting'

                     when deptno=20 then 'Sales'

                     else 'Marketing'

                     End)

From Emp;

This query will produce the same result as that of the Decode query. Here as you can see we have the flexibility of writing complete condition (like deptno=10) which ensures that we can have complex conditions evaluated.

So, like this we can use Case function for implementing the IF-THEN-ELSE logic in the SQL statements.

So, these are the different Conditional Functions which are available in SQL.