ORACLE DECODE

In this article, we will be learning about the DECODE function in Oracle.

DECODE function is used to implement if-then-else logic in your SQL query without using stored procedure.

Syntax

The general syntax for the DECODE function is as follows:

DECODE( expression , search1 , result1 
        [, search2 , result2]

                [, search3 , result3] ...

 [, default] )

Here,

expression – This is the value that we provide to compare with other search values. The data type of this value is converted to the data type of the first search value before comparing.

search – These are the values against which expression is compared. The data types of all subsequent search values are converted to the data type of first search value.

result – If expression value is equal to the search value, then the DECODE function returns result. If expression is equal to search1 is returned, if it equal to search2 then result2 is returned and so on.

default – If expression is not equal to any search value, then the query returns the default value. If we don’t provide default value then query returns NULL.

Returns

The data type of the returned value is the data type of the first result value that is returned. If expression does not match any of the search value, then the query returns the default value. If default value is omitted from the query, the DECODE function returns NULL.

Examples

1.DECODE Function with DUAL Table

Lets understand how a DUAL function works with the help of a simple example:

In this query, our expression is 1 + 1 and 1, 2, 3 are search values while ‘One’, ‘Two’, ‘Three’ are results corresponding to each search value. The DECODE function compares our expression with all the search values and returns the result corresponding to that search value.

‘None of the above’ is our default value, in case our expression is not equal to any search value.

SELECT
DECODE( 1 + 1, 1, 'One',
               2, 'Two',
               3, 'Three',
'None of the above')
FROM dual;

Result:

The query returns ‘Two’ as our expression evaluates to 2 and that is our second search value.

2. Using DECODE Function with Columns

For this example, we will be using Employee table from HR schema:

In this query, we are comparing column department_id in employee table with values 60, 90 and 100. The query returns the result corresponding to these search values, when department_id is equal to any of the search value.

SELECT first_name,last_name, salary,
DECODE (department_id, 60, 'Marketing',
                       90, 'HR',
                      100, 'Management',
'None of the above') Department
FROM employees
WHERE salary > 2000;

Result:

The query returns all the employees and their department name (results) who have salary over 2000 and have department_id equals to either 60, 90 or 100.

Comparison between Oracle CASE and DECODE

We can compare a basic DECODE function with a CASE function by taking a basic query as an example:

In this query, 1 + 0 is our expression, 1,2,3 are the search values and ‘One’, ‘Two’ and ‘Three’ are results. When expression is equal to search value then DECODE function returns the result corresponding to search value, in this case ‘One’.

SELECT
DECODE( 1 + 0, 1, 'One',
               2, 'Two',
               3, 'Three',
'None of the above')
FROM dual;

The logical equivalent of the above DECODE function can be seen using this if-else if-then query which does the same thing.

IF 1 + 0 = 1 THEN
RETURN 'One';
ELSIF 1 + 0 = 2 THEN
RETURN 'Two';
ELSIF 1 + 0 = 3 THEN
RETURN 'Three';
ELSE
RETURN 'None of the above';
END IF;

NULL Value in DECODE Function

NULL is a special value in Oracle that is not even equal to NULL and represents absence of a value rather than its presence. DECODE is a function which can equate NULL with NULL and returns a ‘result’ when NULL equals NULL.

In the following query, the DECODE function returns ‘Values are Equal’ as our expression NULL is equal to the search value NULL.

SELECT
DECODE(NULL,NULL,'Values are Equal','Values are Not equal')
FROM
dual;

In this article, we learned how to use the DECODE function to implement if-else if-then logic.