SQL Server CASE Expression

The CASE expression is used to show another column which can be evaluated based on the conditions depending on the existing columns. The CASE expression consists of WHEN and THEN statements. WHEN is used when the condition satisfies and THEN executes the operation or assignment of value of that column based on the evaluation of the expression. The CASE statement can contain multiple WHEN and THEN statements based on the conditions. The THEN can also contain the ELSE statement if the condition in THEN is not satisfied then the ELSE statement will be assigned.

Types of CASE Expressions

Simple CASE Expression

This CASE expression matches for the perfect equality for the particular condition on evaluation matches the particular value then a value is assigned to the column.

Syntax:

SELECT col1, col2,
              CASE  column_name 
             WHEN equality_condition THEN 'val1'
              WHEN equality_condition THEN 'val2'
               ELSE 'val3' 
               END AS column_name
  FROM table_name

Searched CASE Expression

In this CASE expression the boolean expressions are evaluated in a specified order and then based on the whether condition returns True or False, it moves to the next WHEN clause.

Syntax:

 SELECT col1, col2,
              CASE WHEN CONDITION THEN 'val1'
               ELSE 'val2' END AS column_name,
               CASE WHEN CONDITION THEN 'val1'
               ELSE 'val2' END AS column_name
  FROM table_name

Here the conditions are boolean expressions.

How to Use CASE Expression?

For performing the operations, We should have one table on which operations will be performed. If you don’t know How to Create a table in SQL Server then refer this.

We have created a table called studentsMarkList which contains studentId, studentName, studentName and marksObtained as Columns. Also, we have inserted some data into the studentsMarkList table.

After Inserting some data our table studentsMarkList Looks Like:

studentsMarkList Table

Let’s see how to assign R grade for students of particular studentId of 19104067 and 19104068 using the simple CASE expression

SELECT 
    studentName,
    CASE 
        WHEN studentId = 19104067 THEN 'R'
        WHEN studentId = 19104068 THEN 'R'
    END AS 'gradeObtained'
FROM  
    studentsMarkList;

Output:

Result

Explanation: In this query, it will return a result set with columns ‘studentName‘ and ‘gradeObtained‘, where ‘gradeObtained‘ contains ‘R‘ for students with ‘studentId‘ 19104067 or 19104068 and NULL for all other students.

Let’s see the use of Searched CASE to assign the grades of the students based on the marks with the following 90-100 -> A, 80-90->B,70-80->C,60-70->D,50-60->E, 40-50->F , <40 -> R

SELECT *, CASE 
WHEN marksObtained>=91 AND marksObtained<=100 THEN 'A'
WHEN marksObtained>=81 AND marksObtained<=90 THEN  'B'
WHEN marksObtained>=71 AND marksObtained<=80 THEN 'C'
WHEN marksObtained>=61 AND marksObtained<=70 THEN 'D'
WHEN marksObtained>=51 AND marksObtained<=60 THEN 'E'
WHEN marksObtained>=41 AND marksObtained<=50 THEN 'F'
ELSE  'R' 
END AS gradeObtained
FROM  studentsMarkList

Output:

Result

Explanation: This Query is based on Searched Case Expression which return a result set with all columns from ‘studentsMarkList‘ and an additional column ‘gradeObtained‘ indicating the grade based on the specified conditions.

Let’ see the CASE expression with the UPDATE statement to update the marks of students from studentIds till 19104065:

UPDATE studentsMarkList
SET marksObtained = (
        CASE
            WHEN studentId < 19104065 THEN marksObtained+1
            ELSE marksObtained
        END
        ) 

Output:

Result

Explanation: In the query we have used the UPDATE statement to update the marks of all the students for the studentIds <19104065 by 1 using CASE expression.

CASE Expression for Aggregate Function:

Using case expression we can apply the aggregate functions like COUNT, SUM , AVG etc, for the rows belonging to that particular case in the table.

Aggregate Function COUNT with CASE Expression

SELECT  CASE         
     WHEN marksObtained > 80 THEN 'B'
        WHEN marksObtained > 70 THEN 'C'
        WHEN marksObtained > 60 THEN 'D'
        WHEN marksObtained > 50 THEN 'E'
        WHEN marksObtained > 40 THEN 'F'
        ELSE 'R' END AS distribution,
      COUNT(1) AS count
  FROM studentsMarkList
  GROUP BY  CASE 
       WHEN marksObtained > 90 THEN 'A'
       WHEN marksObtained > 80 THEN 'B'
       WHEN marksObtained > 70 THEN 'C'
       WHEN marksObtained > 60 THEN 'D'
       WHEN marksObtained > 50 THEN 'E'
       WHEN marksObtained > 40 THEN 'F'
 ELSE 'R' END

Output:

Result

Explanation: In this query, it will return the distribution of students across different grade categories and the count of students in each category.

Using Nested CASE Expression

The Nested CASE expression is used in the scenarios where we have more underlining conditions for a particular condition.

SELECT 
CASE         
    WHEN marksObtained > 40 THEN 
      CASE
        WHEN marksObtained > 90 THEN'A'
        WHEN marksObtained > 80 THEN'B'
        WHEN marksObtained > 70 THEN 'C'
        WHEN marksObtained > 60 THEN 'D'
        WHEN marksObtained > 50 THEN 'E'
        WHEN marksObtained > 40 THEN 'F'
       END
 ELSE 'R' END AS distribution,
 COUNT(1) AS count
  FROM studentsMarkList
  GROUP BY
 CASE         
    WHEN marksObtained > 40 THEN 
      CASE 
         WHEN marksObtained > 90 THEN'A'
         WHEN marksObtained > 80 THEN'B'
         WHEN marksObtained > 70 THEN 'C'
         WHEN marksObtained > 60 THEN 'D'
         WHEN marksObtained > 50 THEN 'E'
         WHEN marksObtained > 40 THEN 'F'
       END
 ELSE 'R' 
 END

Output:

Result

Explanation: In the query, We have used the nested CASE which will return the distribution of students across different grade categories and the count of students in each category.

Using ORDER BY with CASE Expression

The CASE expression can be used to order the records based on the CASE expression they belong to.

Let’s see and example to sort the students of Ids<=19104065 in the descending order of their marks and the students of Ids > 19104065 in ascending order of marks.

SELECT * FROM studentsMarkList
     ORDER BY
        CASE
            WHEN studentId > 19104065 THEN marksObtained
            END ASC,
         CASE 
          WHEN studentId <= 19104065 THEN marksObtained
            END DESC

Output:

Result

Explanation: In the query, We can see that we have applied the CASE expression for ORDER BY with students<=19104065 in DESC order and studentIds>19104065 in ASC order. In the result we can see top 6 are in DESC order of their marks and studentIds<=19104065 and from row no 7 to row no 10 we have in ASC order of marks.

Difference Between the Simple CASE and Searched CASE

Simple Case	Searched Case
It performs equality test for one values against multiple values	In the searched we can customize the expression we want to search by adding multiple condition
Syntax is different	Syntax is different
Not much versatile	Very versatile as we can apply the cutting edges cases
Only equality operators are allowed	Combination of logical operators can be used

Usecases of CASE expression

• When there are complex conditions, where result is depending on multiple columns.

• When we have a specific order of condition to get executed.

• When we want to create a dummy table with transformed values for analysis purposes.

• When we apply aggregation on different cases.

Conclusion

We can use the CASE statement when we have multiple conditions and based on the condition we should assign a particular value to a column and also in the case where we have to aggregate on the basis of a particular condition, the CASE expression becomes very useful.