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

Consider the already existing table with the following data:

Table Name: Comp

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:

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:

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:

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: