Term: DECODE

Definition:
The DECODE function is analogous to the "IF THEN ELSE" conditional statement. DECODE works with values, columns, and expressions of all data types.

Example Syntax:

DECODE (expression, search, result [, search, result]... [, default])

The DECODE function compares expression against each search value in order. If a match (equality) is found between the expression and the search argument then it returns the corresponding result. If there is no match, the default value is returned (if defined), else it returns NULL. In case of a type compatibility mismatch, Oracle internally does an implicit conversion (if possible) to return the results.

Interestingly, Oracle considers two NULLs to be equivalent while working with the DECODE function.

SQL> SELECT DECODE(NULL,NULL,'EQUAL','NOT EQUAL') FROM DUAL;



DECODE

-----

EQUAL

If expression is NULL, then Oracle returns the result of the first search that is also NULL. The maximum number of components in the DECODE function, including expression, searches, results, and default, is 255.

SQL> SELECT EMPLOYEE_NAME, SALARY, DECODE (HIRE_DATE, SYSDATE,'NEW JOINEE','EMPLOYEE')

           FROM EMPLOYEES