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:

1. Simple CASE Statement.
2. 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:

1. The employee salaries should come in ascending order where the employee gender is male.
2. 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