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

ORACLE/PLSQL: CASE STATEMENT

The Oracle/PLSQL CASE statement has the functionality of an IF-THEN-ELSE statement. Starting in Oracle 9i, you can use the CASE statement within a SQL statement.

The syntax for the Oracle/PLSQL CASE statement is:

CASE [ expression ]

WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n

ELSE result

END
---------
ARGUMENTS:

expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further.

result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

NOTE:

If no condition is found to be true, then the CASE statement will return the value in the ELSE clause.

If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

You can have up to 255 comparisons in a CASE statement. Each WHEN ... THEN clause is considered 2 comparisons.

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 CASE.

CASE deptno
WHEN 10
THEN 'DEPT1'
WHEN 20
THEN 'DEPT2'
WHEN 30
THEN 'DEPT3'
ELSE 'NO DEPT'
END;

Query used in Video:
select empno,ename,deptno,CASE deptno WHEN 10 THEN 'DEPT1' WHEN 20 THEN 'DEPT2' WHEN 30 THEN 'DEPT3' ELSE 'No Dept' END from emp;