SQL CASE Statement

What is CASE Statement in SQL?

The Case Statement in SQL returns a value when some condition is specified. This condition will be based on some other cell in the same table.

As in the if-else statement, several conditions are applied, and whichever first gives the result as true is executed. The case statement in SQL also works similarly. The conditions are specified using WHEN and THEN keywords. You can specify as many conditions using these keywords.

However, the CASE, WHEN, THEN, and END keywords are necessary while writing a case statement in SQL. The CASE keyword is used to specify the beginning of the case statement, whereas the END keyword specifies the end of the case statement and the end of the conditions. We can also write an ELSE keyword that would execute when all other conditions specified in the case statement return false.

The END keyword is followed by an alias_name, which is the column name you are adding to the table based on the conditions.

SQL CASE Syntax

The syntax of the Case Statement in SQL is as follows:

However, you can also write the syntax of the case statement in another way. This syntax adds an expression after the CASE statement that is compared with every value specified after the WHEN keyword.

In the former syntax, the entire condition to be checked is written after the CASE statement, and based on it, the data is added to the cells. However, in the latter syntax, the expression and the values to be compared are written separately. We don't need to write the entire condition again and again in each line.

It can be written as :

SQL CASE Parameters

SQL CASE Examples

Example : 1

Let us say we have a table student in which the students' id, Name, and marks are stored.

Now, we have to add one more column containing each student's grade.

Therefore, we will execute the query:

Output:

The output contains a table with the grade column added to it after comparing each student's marks with the given conditions specified in the query executed.

Example : 2

Now, let us see another example with a different syntax :

Consider a table student:

We will now add a Remarks column in the student table based on the students' Grades. Therefore, to achieve this, we have to execute the query :

Output:

The above query compares the grade values and accordingly adds the items in the Remarks column.

Various Formats of SQL CASE Statement

1. Case Statement with Order by Clause

We can also use the ORDER BY clause with the case statement. In SQL, the ORDER BY clause is used to sort the results in an ascending or descending order.

Let us see an example to understand it better.

We will take a table named EmployeeDetails which is shown below :

In the above table, we will sort the rows by their ages, in which the ages of the female employees will be sorted in ascending order. In contrast, the ages of the male employees will be sorted in descending order using the case statement.

We will execute the following query to perform the above task:

Output:

As you can see in the above output, the ages of the male employees are sorted in descending order, whereas the ages of the female employees are sorted in ascending order.

2. Case Statement in SQL with Group by Clause

We can also use the GROUP BY clause with the case statement. However, as we know, we use the GROUP CLAUSE to group the rows of a table. This clause is often used with the aggregate functions such as count(), sum(), min(), max(), etc for performing certain calculations.

Lets us understand with an example how we will use the GROUP BY clause with the CASE statement.

We have a table named People in which there are records of some people around the world with their ages and eligibility to vote. We have to calculate the total number of citizens eligible to vote and the total number of citizens not eligible to vote.

The table is as shown below.

Now, we have to group the citizens to have the count of the eligible and the non-eligible voters.

We will be executing the following query to get our desired result.

Output:

3. Update Statement with a CASE Statement

In SQL, the UPDATE statement is used to update or change the existing records in the table. However, sometimes we only need to change some cells based on some condition. In such situations, we can use the CASE statement along with the UPDATE statement.

Lets us see it with an example.

We have a table named People, which is shown below.

We have to update the values in column State such that the value should not be an abbreviation of the state name but instead should store the full name of the state.

Therefore, we execute the following query:

Output:

As shown above, all the state names are updated to their full form.

4. Insert Statement with a CASE Statement

We can also use the CASE statement with the INSERT statement to insert data into the table. In SQL, the INSERT statement is used to insert records without any condition. However, we use the insert and the case statements together if you want to insert data based on a certain condition.

Let us understand it with an example:

We have a table named Example1 in which there are three columns: id, Name, and the State to which they belong.

The table is as follows:

Now we will insert the records of the columns Name and Stateinto another table using the CASE Statement.

We will execute the following query to understand it more:

Output:

Using the above query, we are inserting the values of the Name and State columns from the table Example1 to Example2. Here we are using the conditions that the abbreviations used in Example1 should be written in their corresponding full forms.

SQL CASE Statement Limitations

1. We have multiple conditions in a CASE statement, but as it works sequentially if a condition is found true, then all other conditions after it will not even be checked.
2. We cannot check NULL values in a table using a CASE statement.
3. A CASE statement cannot be used to control the execution flow of a user-defined function.