OracleBlog

Here is another handy Oracle-only SQL tool: DECODE.

Decode allows you to do if/elseif/else logic within an SQL query, similar to a CASE WHEN statement in a PL/SQL procedure.

DECODE (expression, if_equal_to_this_1, give_me_this_1, else_if_equal_to_this_2, give_me_this_2, ..., default)

Dan Morgan's Reference on DECODE:
http://www.psoug.org/reference/decode_case.html

The default value is optional, if it is left out, it defaults to NULL. And the maximum number of comparisons is 255.

It works very much like a CASE WHEN statement, so you can already think of uses, especially if you nest your DECODEs.

Remember the article I wrote on NULL vs Nothing and on NULLs in general?

http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

Well, you can actually use DECODE like NVL. Don't believe me? I don't blame you, because I told you that NULL is not equal to NULL. But here is a quote right from the Oracle 9i SQL Reference:

"In a DECODE function, Oracle considers two nulls to be equivalent. If expr is null, then Oracle returns the result of the first search that is also null."

And here is the example because like me you don't believe anything without proof:

SQL> CREATE TABLE atable (avalue VARCHAR2(32));

Table created.

SQL> INSERT INTO atable (avalue) VALUES (NULL);

1 row created.

SQL> INSERT INTO atable (avalue) VALUES ('This is not NULL');

1 row created.

SQL> SELECT DECODE(avalue, NULL, 'This is NULL', avalue) avalue
2 FROM atable;

AVALUE
--------------------------------
This is NULL
This is not NULL

If you don't believe how useful it can be, here is a FAQ that shows how to use DECODE for (among other things) avoiding divide-by-zero errors:

http://www.db.cs.ucdavis.edu/public/oracle/faq/decode.html

In practise, I have seen DECODE used for the ORDER BY clause.

If you are still not convinced, you can "Ask Tom" how DECODE can be used to have a conditional foreign key. That is, a foreign key that only applies to certain rows!

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1249800833250