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. For 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).

Since 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 searched for or compared with other arguments s1, s2, … sn.
• s1, s2, …, or sn is the expression to search for. The function compares sequentially the first argument with s1, s2, …, sn. If any comparison e = s1, e = s2, …, e = sn returns 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 argument s1, s,2, … or sn.

SQL DECODE() example #

We’ll use the employees table from 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:

• Use the DECODE() function in the ORDER BY clause. The DECODE() function returns the column to sort based on the input argument.
• The DECODE() function returns the salary column, therefore, the result set was sorted by salary column.

Summary #

• Use the SQL DECODE() function to add the if-then-else logic to a query.