SQL CASE
This page contains all the information about SQL CASE. The CASE is an If-Else type of logical query used in the statement. The CASE in Structured Query Language is similar to nested If-Else in programming Languages.
The CASE Statement goes through a condition if the first condition is true. It returns a value; otherwise, jump to another condition. If that condition matches, it returns the value; otherwise, jump to the else part.
CASE statement can have one condition or can have multiple conditions.
If there is no Else part in the CASE statement, it returns NULL directly.
The CASE statement always has one WHEN and THEN pair or can have more than one pair.
The CASE statement starts with CASE keywords and ends with END keywords.
We can use the WHERE clause, ORDER BY clause, and GROUP BY clause in the SQL query with the CASE statement.
There are two types of CASE statements in RDBMS are as follows:
- Simple CASE Statement.
- Searched CASE Statement.
CASE Statement Syntax in Structured Query Language:
CASE <case-expression>
WHEN conditions THEN statement
ELSE result
END [as Alias Name];
The CASE statement can have multiple conditions.
In the syntax, CASE will start from the first condition. If conditions match what is in the WHEN clause, the THEN clause statement will get a return or be executed, and the rest of the conditions will be get ignored.
If the first condition didn't match the first WHEN conditions, then it will jump to the next WHEN conditions, and if that condition matches, THEN part will get a return or be executed.
If the second condition also didn't match, then it will move to another, and if it matches the conditions, return the statement, and if no conditions match, then else part will get executed.
Example of CASE statement in Structured Query Language:
Consider the already existing table with the following data:
Table Name: Employee_Details
E_Id | E_Name | E_Salary | E_City | Designation | Age | Gender |
1001 | Kirti Kirtane | 60000 | Mumbai | Project Manager | 26 | F |
1002 | Akash Vani | 40000 | Pune | System Engineer | 22 | M |
1003 | Anupam Mishra | 55000 | Hyderabad | Project Manager | 25 | M |
1004 | Anuj Rawat | 45000 | Hyderabad | Software Tester | 25 | M |
1005 | Akanksha Yadav | 42000 | Pune | Associate Software Developer | 23 | F |
1006 | Bhavesh Wani | 50000 | Mumbai | Associate Software Developer | 24 | M |
2001 | Sakshi Sharma | 40000 | Bangalore | System Engineer | 23 | F |
2002 | Megha Ghatole | 45000 | Bangalore | Software Tester | 24 | F |
2003 | Surbhi Nahar | 60000 | Pune | Project Manager | 26 | F |
2004 | Supriya Shende | 55000 | Mumbai | Software Developer | 25 | F |
2005 | Prachi Sharma | 52000 | Hyderabad | Software Developer | 24 | F |
2006 | Purva Dhandekar | 50000 | Bangalore | Software Tester | 23 | F |
3001 | Shruti Deshpande | 60000 | Pune | Project Manager | 26 | F |
3002 | Rohit Nikam | 40000 | Hyderabad | System Engineer | 23 | M |
3003 | Sahil Jain | 50000 | Mumbai | Software Developer | 24 | M |
Consider the already existing table with the following data:
Table Name: Comp
Comp_Id | Make | E_Id |
101 | Dell | 1006 |
102 | Dell | 1003 |
103 | Lenovo | 1001 |
104 | Lenovo | 1005 |
105 | HP | 2002 |
106 | HP | 2004 |
107 | Asus | 2006 |
108 | Asus | 2001 |
109 | Acer | 3001 |
110 | Acer | 3002 |
Example 1: We will first see the single-use of WHEN and THEN condition to the CASE Statement:
SELECT E_ID, E_NAME, E_SALARY, E_CITY, CASE WHEN E_SALARY > 50000 THEN 'SALARY INCREMENT BY 5%' ELSE 'SALARY INCREMENT BY 10%' END AS SALARY_INCREMENT FROM EMPLOYEE_DETAILS;
The CASE statement checks the first condition in the above query if the employee salary is greater than 50000. If yes, it will write "Salary increment by 5%". Else it will write "Salary increment by 10%" in the Salary_Increement column.
The output in the above query is as follows:
E_ID | E_NAME | E_SALARY | E_CITY | SALARY_INCREEMENT |
1001 | Kirti Kirtane | 60000 | Mumbai | SALARY INCREMENT BY 5% |
1002 | Akash Vani | 40000 | Pune | SALARY INCREMENT BY 10% |
1003 | Anupam Mishra | 55000 | Hyderabad | SALARY INCREMENT BY 5% |
1004 | Anuj Rawat | 45000 | Hyderabad | SALARY INCREMENT BY 10% |
1005 | Akanksha Yadav | 42000 | Pune | SALARY INCREMENT BY 10% |
1006 | Bhavesh Wani | 50000 | Mumbai | SALARY INCREMENT BY 10% |
2001 | Sakshi Sharma | 40000 | Bangalore | SALARY INCREMENT BY 10% |
2002 | Megha Ghatole | 45000 | Bangalore | SALARY INCREMENT BY 10% |
2003 | Surbhi Nahar | 60000 | Pune | SALARY INCREMENT BY 5% |
2004 | Supriya Shende | 55000 | Mumbai | SALARY INCREMENT BY 5% |
2005 | Prachi Sharma | 52000 | Hyderabad | SALARY INCREMENT BY 5% |
2006 | Purva Dhandekar | 50000 | Bangalore | SALARY INCREMENT BY 10% |
3001 | Shruti Deshpande | 60000 | Pune | SALARY INCREMENT BY 5% |
3002 | Rohit Nikam | 40000 | Hyderabad | SALARY INCREMENT BY 10% |
3003 | Sahil Jain | 50000 | Mumbai | SALARY INCREMENT BY 10% |

Example 2: We will use the GROUP BY clause with the CASE Statement
SELECT E_ID, E_NAME, E_SALARY, E_CITY, CASE WHEN E_SALARY < 42000 THEN E_SALARY * 1.5 WHEN E_SALARY >= 42000 AND E_SALARY < 55000 THEN E_SALARY * 1.2 ELSE E_SALARY * 1 END AS EMPLOYEE_INC FROM EMPLOYEE_DETAILS GROUP BY E_NAME;
In the above query, we increment the salary of employees. If employee salary is less than 42000, then employee salary * 1.5, else if employee salary is greater than equal to 42000 and less than 55000, then employee salary increments by 1.2, else no increment of the employee in this query, we used GROUP BY Employee Name column.
The output of the above query is as follows:
E_ID | E_NAME | E_SALARY | E_CITY | EMPLOYEE_INC |
1005 | Akanksha Yadav | 42000 | Pune | 50400.0 |
1002 | Akash Vani | 40000 | Pune | 60000.0 |
1004 | Anuj Rawat | 45000 | Hyderabad | 54000.0 |
1003 | Anupam Mishra | 55000 | Hyderabad | 55000.0 |
1006 | Bhavesh Wani | 50000 | Mumbai | 60000.0 |
1001 | Kirti Kirtane | 60000 | Mumbai | 60000.0 |
2002 | Megha Ghatole | 45000 | Bangalore | 54000.0 |
2005 | Prachi Sharma | 52000 | Hyderabad | 62400.0 |
2006 | Purva Dhandekar | 50000 | Bangalore | 60000.0 |
3002 | Rohit Nikam | 40000 | Hyderabad | 60000.0 |
3003 | Sahil Jain | 50000 | Mumbai | 60000.0 |
2001 | Sakshi Sharma | 40000 | Bangalore | 60000.0 |
3001 | Shruti Deshpande | 60000 | Pune | 60000.0 |
2004 | Supriya Shende | 55000 | Mumbai | 55000.0 |
2003 | Surbhi Nahar | 60000 | Pune | 60000.0 |

Example 3: Use of ORDER BY Clause with CASE Statement:
SELECT * FROM COMP ORDER BY CASE WHEN MAKE = 'DELL' THEN 0 ELSE 1 END, E_ID;
In the above query, we used the ORDER BY clause and displayed the data in order by making the name. Still, they also used one condition that employees' names are "Dell" should be displayed first and then the rest of the data order by make as well employee id.
The output of the above query is as follows:
Comp_Id | Make | E_Id |
102 | Dell | 1003 |
101 | Dell | 1006 |
103 | Lenovo | 1001 |
104 | Lenovo | 1005 |
108 | Asus | 2001 |
105 | HP | 2002 |
106 | HP | 2004 |
107 | Asus | 2006 |
109 | Acer | 3001 |
110 | Acer | 3002 |

Example 4: In the given an example, we want to sort the result in the following methods:
- The employee salaries should come in ascending order where the employee gender is male.
- The employee salaries should come in descending order where employee gender is female.
SELECT E_ID, E_NAME, E_SALARY, E_CITY, GENDER FROM EMPLOYEE_DETAILS ORDER BY CASE GENDER WHEN 'M' THEN E_SALARY END ASC, CASE WHEN GENDER = 'F' THEN E_SALARY END DESC;
In the above, we sort the employee salary in ascending order if an employee is male; else employee salary will be descending where an employee is female.
The output of the above query is as follows:
E_Id | E_Name | E_Salary | E_City | Gender |
1001 | Kirti Kirtane | 60000 | Mumbai | F |
3001 | Shruti Deshpande | 60000 | Pune | F |
2003 | Surbhi Nahar | 60000 | Pune | F |
2004 | Supriya Shende | 55000 | Mumbai | F |
2005 | Prachi Sharma | 52000 | Hyderabad | F |
2006 | Purva Dhandekar | 50000 | Bangalore | F |
2002 | Megha Ghatole | 45000 | Bangalore | F |
1005 | Akanksha Yadav | 42000 | Pune | F |
2001 | Sakshi Sharma | 40000 | Bangalore | F |
1002 | Akash Vani | 40000 | Pune | M |
3002 | Rohit Nikam | 40000 | Hyderabad | M |
1004 | Anuj Rawat | 45000 | Hyderabad | M |
1006 | Bhavesh Wani | 50000 | Mumbai | M |
3003 | Sahil Jain | 50000 | Mumbai | M |
1003 | Anupam Mishra | 55000 | Hyderabad | M |
