DECODE Function ( IF..THEN..ELSE) in SQL ORACLE Query With Example

ORACLE/PLSQL: DECODE FUNCTION

The Oracle/PLSQL DECODE function has the functionality of an IF-THEN-ELSE statement.

The syntax for the Oracle/PLSQL DECODE function is:

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

ARGUMENTS:

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the DECODE function will return default. If default is omitted, then the DECODE function will return null (if no matches are found).

Lets apply this function on emp table.
Emp table has 3 dept numbers like 10,20 and 30.
So if I want to display the different dept names based on ID, I have to use IF THEN ELSE condition.

IF deptno=10
THEN
"DEPT1"
ELSE deptno=20
THEN
"DEPT2"
ELSE deptno=30
THEN
"DEPT3"

This entire IF block can be achived using single DECODE().
DECODE(deptno,10,'DEPT1',20,'DEPT2',30,'DEPT3')

Query used in Video:
select empno,ename,deptno,DECODE(deptno,10,'DEPT1',20,'DEPT2',30,'DEPT3') from emp;