using CASE Statement in oracle database query

A CASE Statement in Oracle SQL / PLSQL is having the functionality of IF-THEN-ELSE Statement.

Syntax of the CASE Statement in Oracle SQL / PLSQL is:
CASE [expression]
WHEN condition_1 THEN result_1
WHEN consition_2 THEN result_2
WHEN condition_3 THEN result_3
.
.
WHEN condition_N THEN result_N
ELSE default_result
END;

• expression is an optional value, if provided; it is used to compare with various conditions (e.g. condition_1, condition_2, . . condition_N)
• condtion_1 to condition_N must have the same data type, also the conditions are evaluated in the order in which they are listed, hence once a condition evaluates to true the CASE statement returns the result and does not evaluate further conditions.
• result_1 to result_N must also have the same data type, these are the values that will be returned once the condition evaluates to true.
• If no condition evaluates to true then the ‘default_result’ from the ELSE clause will be returned by CASE statement
• If the ELSE clause is omitted and none of the condition evaluates to true then NULL will be returned by CASE Statement.

Let’s see an example to understand how to use CASE in Oracle SQL / PLSQL SELECT Statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support	5
105	Emp E	32000	Sales	10
106	Emp F	20000	Sales	5
107	Emp G	12000	Sales
108	Emp H	12000	Support

Now, if we write our query using CASE Statement in Oracle SQL / PLSQL as:

SELECT employee_id
       ,employee_name
       ,salary
       ,CASE
         WHEN salary = 30000 THEN 'Salary Between 20000 and 30000'
         ELSE 'Salary More Than 30000'
         END SALARY_STATUS
FROM employee;

We will get the following result:

EMPLOYEE_ID	EMPLOYEE_NAME	SALARY	SALARY_STATUS
101	Emp A	10000	Salary Less than 20000
102	Emp B	20000	Salary Less than 20000
103	Emp C	28000	Salary More Than 30000
104	Emp D	30000	Salary Between 20000 and 30000
105	Emp E	32000	Salary Between 20000 and 30000
106	Emp F	20000	Salary Less than 20000
107	Emp G	12000	Salary Less than 20000
108	Emp H	12000	Salary Less than 20000

Here we can observe, that the CASE statement returns the text literals ‘Salary less than 20000’, ‘Salary between 20000 and 30000’ and ‘Salary More Than 30000’ for each record based on the salary amount as specified in the CASE Statement.