Summary -

In this topic, we described about the CASE Statement with detailed example.

CASE statement is used to check multiple conditions and returns corresponding values to that condition in result set when the condition became true. CASE statement works similar to IF-THEN-ELSE statement. If the first condition itself true it won't go for another conditions in CASE statement. By using CASE statement we can UPDATE specific row values in existing table. In CASE statement not a single condition is satisfied then it returns ELSE statement value in the output. In some scenario it won't contain ELSE statement and it won't match single condition then it returns NULL values in the result set.

Syntax -

CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2 
        WHEN conditionN THEN resultN 
        ELSE result
END;   

Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Getting rows from existing table by using CASE statement.

Requirement – Getting rows from employee_details table by using CASE statement.
The query was as follows –

SELECT  emp_name, salary,
CASE
      WHEN salary > 25000 THEN 'SALARY IS GREATER THAN 25000' 
      WHEN salary = 25000 THEN 'SALARY IS EQUAL TO 25000'
      ELSE 'SALARY IS UNDER 25000'
END AS salarytext
FROM employee_details; 

By executing above query we can get output like as follows –

emp_name salary salarytext
Employee1 45000.00 SALARY IS GREATER THAN 25000
Employee2 40000.00 SALARY IS GREATER THAN 25000
Employee3 27000.00 SALARY IS GREATER THAN 25000
Employee4 25000.00 SALARY IS EQUAL TO 25000
Employee5 20000.00 SALARY IS LESS THAN 25000
Employee6 18000.00 SALARY IS LESS THAN 25000
Employee7 15000.00 SALARY IS LESS THAN 25000
Employee8 14000.00 SALARY IS LESS THAN 25000
Employee9 13000.00 SALARY IS LESS THAN 25000

Scenario – Updating rows in existing table by using CASE statement.

Requirement – Updating salary of employee1 in employee_details table by using CASE statement.
The query was as follows –

UPDATE employee_details
SET salary = CASE salary
WHEN 45000 THEN 50000
ELSE employee_details.salary
END; 

By executing above query we can update the salary of Employee1 from employee_details. The output was as follows –

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 50000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

Scenario – Getting rows of specific columns from existing table by using CASE statement with ORDER BY clause.

Requirement – Getting rows of emp_name, designation, manager_id details from employee_details table by using CASE statement with ORDER BY clause. The query was as follows –

SELECT emp_name,designation,manager_id FROM employee_details ORDER BY 
(CASE
      WHEN manager_id IS NULL THEN emp_name
      ELSE manager_id 
END); 

By executing above query we can get emp_name, designation, manager_id details from employee_details table by using CASE statement with ORDER BY clause. The output was as follows –

emp_name designation manager_id
Employee3 Manager Employee1
Employee1 Director
Employee2 Director
Employee4 Manager Employee2
Employee5 Analyst Employee3
Employee6 Analyst Employee3
Employee7 Clerk Employee3
Employee8 Salesman Employee4
Employee9 Salesman Employee4