SQL DECODE

Summary: in this tutorial, you will learn how to use the SQL DECODE() function to add if-then-else logic to queries.

Introduction to SQL DECODE() function

The SQL DECODE() function allows you to add procedure if-then-else logic to queries. Let’s see the following example:

SELECT DECODE(1,1,'Equal');
Code language: SQL (Structured Query Language) (sql)

In this example, the DECODE() function compares the first argument (one) with the second argument (also one). Because the first argument equals the second one, the function returns the third argument which is the string Equal.

The DECODE() function above works the same as the following IF statement:

IF 1 = 1 THEN
    RETURN 'Equal';
END IF;
Code language: SQL (Structured Query Language) (sql)

The following example returns NULL because the first argument is not equal to the second one.

SELECT DECODE(1,2, 'Equal');
Code language: SQL (Structured Query Language) (sql)

It works like the following statement:

IF 1 = 2 THEN
    RETURN 'Equal';
END IF;
Code language: SQL (Structured Query Language) (sql)

If you want to specify the value when the first argument is not equal to the second one, you use the following form of the DECODE() function:

SELECT DECODE(1,2, 'Equal', 'Not Equal');
Code language: SQL (Structured Query Language) (sql)

It works like the following IF-THEN-ELSE statement:

IF 1 = 2 THEN
    RETURN 'Equal';
ELSE
    RETURN 'Not Equal';
END IF;
Code language: SQL (Structured Query Language) (sql)

In general, you can compare the first argument of the DECODE() function with a list of arguments as shown in the following example:

SELECT DECODE (2, 1, 'Equal 1', 2, 'Equal 2');
Code language: SQL (Structured Query Language) (sql)

This example works as the following IF-THEN-ELSE IF statement:

IF 2 = 1 THEN
     RETURN 'Equal 1';
ELSE IF 2 = 2 
    RETURN 'Equal 2';
END IF;
Code language: SQL (Structured Query Language) (sql)

See the following example:

SELECT DECODE(3,1, 'Equal 1,', 2, 'Equal 2', 'Not Equal 1 or 2');
Code language: SQL (Structured Query Language) (sql)

This example works like the following IF-THEN-ELSEIF-ELSE statement:

IF 3 = 1 THEN 
    RETURN 'Equal 1';
ELSE IF 3 =2 THEN
    RETURN 'Equal 2';
ELSE
    RETURN 'Not Equal 1 or 2';
END IF;
Code language: SQL (Structured Query Language) (sql)

SQL DECODE() function syntax

The following illustrates the syntax of the SQL DECODE() function:

DECODE (e , s1, r1[, s2, r2], ...,[,sn,rn] [, d]);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

e is the argument that to be searched for or compared with other argument s1, s2, … sn.
s1, s2, …, or sn is the expression to search for. The function compares the first argument with s1, s2, …, sn sequentially. If any comparison e = s1, e = s2, …, e = sn return true, the DECODE() function terminates the evaluation and returns the result.
r1, r2, …, or rn is the returned result when ei = si.
d is an expression to return when e does not equal to any argument s1, s,2, … or sn.

SQL DECODE() example

We will use the employees table in the sample database for demonstration.

See the following query:

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
ORDER BY DECODE('S',
        'F',
        first_name,
        'L',
        last_name,
        'S',
        salary);
Code language: SQL (Structured Query Language) (sql)

In this example, we used the DECODE() function in the ORDER BY clause. The DECODE() function returned the column to sort based on the input argument. In this case, the DECODE() function returned the salary column, therefore, the result set was sorted by salary column.

In this tutorial, you have learned how to use the SQL DECODE() function to add the if-then-else logic to a query.

Was this tutorial helpful ?