SQL CASE

The CASE is a statement that operates if-then-else type of logical queries. This statement returns the value when the specified condition evaluates to True. When no condition evaluates to True, it returns the value of the ELSE part.

When there is no ELSE part and no condition evaluates to True, it returns a NULL value.

In Structured Query Language, CASE statement is used in SELECT, INSERT, and DELETE statements with the following three clauses:

1. WHERE Clause
2. ORDER BY Clause
3. GROUP BY Clause

This statement in SQL is always followed by at least one pair of WHEN and THEN statements and always finished with the END keyword.

The CASE statement is of two types in relational databases:

1. Simple CASE statement
2. Searched CASE statement

Syntax of CASE statement in SQL

Here, the CASE statement evaluates each condition one by one.

If the expression matches the condition of the first WHEN clause, it skips all the further WHEN and THEN conditions and returns the statement_1 in the result.

If the expression does not match the first WHEN condition, it compares with the seconds WHEN condition. This process of matching will continue until the expression is matched with any WHEN condition.

If no condition is matched with the expression, the control automatically goes to the ELSE part and returns its result. In the CASE syntax, the ELSE part is optional.

In Syntax, CASE and END are the most important keywords which show the beginning and closing of the CASE statement.

Examples of CASE statement in SQL

Let's take the Student_Details table, which contains roll_no, name, marks, subject, and city of students.

Example 1: The following SQL statement uses single WHEN and THEN condition to the CASE statement:

Explanation of above query:

Here, the CASE statement checks that if the Stu_Marks is greater than and equals 50, it returns Student_Passed otherwise moves to the ELSE part and returns Student_Failed in the Student_Result column.

Output:

Example 2: The following SQL statement adds more than one WHEN and THEN condition to the CASE statement:

Explanation of above query:

Here, the CASE statement checks multiple WHEN and THEN conditions one by one. If the value of Stu_Marks column is greater than or equals to 90, it returns Outstanding otherwise moves to the further WHEN and THEN conditions.

If none of the conditions is matched with the Student_Details table, CASE returns the NULL value in the Stu_Remarks column because there is no ELSE part in the query.

Output:

Example 3:

Let's take another Employee_Details table which contains Emp_ID, Emp_Name, Emp_Dept, and Emp_Salary.

The following SQL query uses GROUP BY clause with CASE statement:

Output:

Example 4: In this example, we use the ORDER BY clause with a CASE statement in SQL:

Let's take another Employee_Details table which contains Emp_ID, Emp_Name, Emp_Dept, and Emp_Age.

We can check the data of Employee_Details by using the following query in SQL:

Output:

The following SQL query shows all the details of employees in the ascending order of employee names:

Output:

If you want to show those employees at the top who work in the Coding Department, then for this operation, you have to use single WHEN and THEN statement in the CASE statement as shown in the following query:

Output: