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.
