Oracle DECODE Function

https://www.databasestar.com/oracle-decode-function/
The Oracle DECODE function lets you perform IF-THEN-ELSE functionality in your queries. It’s similar to a CASE statement.

The syntax of the DECODE function is:

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

The parameters of the Oracle DECODE function are:

- expression (mandatory): This is the value to compare.
- search (mandatory): This is the value to compare against the expression.
- result (mandatory): This is the return value if the search value matches the expression value. There can be multiple combinations of search and result values, and the result value is attached to the previous search value.
- default (optional): If none of the search values match, then this value is returned. If this is not provided, the DECODE function will return NULL if no matches are found.

If you compare this to an IF-THEN-ELSE statement, it would look like this:

IF (expression = search) THEN result
[ELSE IF (expression = search) THEN result]
ELSE default
END IF

The parameters can be one of many different data types, and the return type is calculated from them.

Earlier I mentioned it’s similar to CASE. So why would you use one and not the other?

- DECODE is an older function, and CASE was introduced as a replacement for DECODE.
- CASE offers more flexibility then DECODE
- CASE is also easier to read and debug (in my opinion)

The performance of these functions is the same, so if you’re considering using DECODE, I would suggest using CASE instead.

For more information about the Oracle DECODE function, including all of the SQL shown in this video and the examples, read the related article here:
https://www.databasestar.com/oracle-decode-function/