Learning Objective
The objective of this SQL Server tutorial is to teach you how to use the CASE expression to apply if-then-else logic in a SQL statement.
What is CASE in SQL Server?
The CASE expression in SQL server allows us to apply if-then-else logic in a SQL statement. A CASE consists of a number of conditions with an accompanying custom result value in a case body followed by an optional ELSE clause. An expression value is checked by the conditions and if it matches any of the condition then the corresponding result value is retained as a part of the final resultset. If the expression value does not match any of the conditions then it picks up the result value from the ELSE clause if present or returns NULL. The purpose of using CASE in a SQL query is twofold. They are the following.
- It allows us to subject a value to multiple checks.
- It allows us to substitute that value with a custom defined result value in the resultset.
CASE expressions are most commonly used in output formatting and to update separate column values in multiple rows.
Operation
As CASE is an expression it can be used with any SQL clause that supports an expression like SELECT, WHERE, FROM, HAVING etc. There are 2 types of CASE expressions – SIMPLE CASE expression and SEARCHED CASE expression. The differences between the two are as below.
| SIMPLE CASE expression | SEARCHED CASE expression |
| The expression is specified at the beginning of the CASE. | The expression is specified in the CASE body. |
| The WHEN statement contains a value against which the expression value is checked. | The WHEN statement contains an expression which generates a value. |
| The WHEN statement can only check for equality. | The WHEN statement can check for more than just equality with the help of a Boolean expression. |
| It does not allow the use of logical operators and aggregate functions | It allows us to use logical operators and aggregate functions |
Syntax
The basic syntax of the two types of CASE expressions are given below.
SIMPLE CASE syntax
CASE expression WHEN value_1 THEN result_1 WHEN value_2 THEN result_2 … WHEN value_n THEN result_n ELSE result END
SEARCHED CASE syntax
CASE WHEN expression_1 THEN result_1 WHEN expression_2 THEN result_2 … WHEN expression_n THEN result_n ELSE result END
In this syntax,
- CASE – SQL keyword to indicate the beginning of a CASE statement.
- expression – something that returns a unique value (can be a literal value itself or a column, parameter, variable, subquery, mathematical operation, function etc.).
- WHEN – SQL keyword to indicate an instance.
- value – a literal value (string or numeric).
- THEN – SQL keyword to indicate the consequence or consequential value for an instance specified by WHEN.
- result – the corresponding value that goes with an instance specified by WHEN.
- ELSE – SQL keyword to escape a CASE and specify a result if no case conditions are met.
- END – SQL keyword to indicate the end of case conditions.
SQL Server CASE Examples
Let us try to understand both types of CASE expressions with the help of examples. Suppose we have a table called student_result which contains the final result data of the students with major in different subjects in the college or university. The table is represented below. We will use this sample table as the reference table for the examples that follow.
| roll_no | student_name | major_subject | aggregate_percentage |
| 2010 | Bikram Chowdhury | Economics | 77 |
| 2021 | Sanghamitra Sharma | Zoology | 68 |
| 2022 | Abdul Shaikh | Zoology | 79 |
| 2033 | Siddique Rehman | Geology | 70 |
| 2035 | Jasmine Mathew | Sociology | 80 |
| 2039 | Prerana Chakraborty | Economics | 93 |
| 2040 | Rohit Bhandari | Sociology | 72 |
| 2044 | Chanchal Mishra | Physics | 96 |
| 2050 | Pankaj Das | Physics | 88 |
| 2061 | Chinmoy Nath | Sociology | 59 |
| 2067 | Nandini Sarkar | Geology | 55 |
| 2068 | Krishna Kishore | Economics | 88 |
| 2077 | Amit Tripathi | Geology | 90 |
| 2090 | Gulshan Singh | Zoology | 91 |
| 2099 | Samnvay Patekar | Sociology | 90 |
1) SQL Server CASE – SIMPLE CASE vs SEARCHED CASE example
Suppose we want to categorize the students majoring in the different subjects into the two broad streams of Humanities and Science. We can do so with the following query using SIMPLE CASE expression.
SELECT CASE major_subject WHEN 'Economics' THEN 'Humanities' WHEN 'Sociology' THEN 'Humanities' WHEN 'Zoology' THEN 'Science' WHEN 'Geology' THEN 'Science' WHEN 'Physics' THEN 'Science' END stream, roll_no, student_name FROM student_result ORDER by stream;
It will produce the following output as per our requirement with the students mapped to either ‘Humanities’ or ‘Science’ depending on their Major subject.
| stream | roll_no | student_name |
| Humanities | 2010 | Bikram Chowdhury |
| Humanities | 2035 | Jasmine Mathew |
| Humanities | 2039 | Prerana Chakraborty |
| Humanities | 2040 | Rohit Bhandari |
| Humanities | 2061 | Chinmoy Nath |
| Humanities | 2068 | Krishna Kishore |
| Humanities | 2099 | Samnvay Patekar |
| Science | 2077 | Amit Tripathi |
| Science | 2090 | Gulshan Singh |
| Science | 2067 | Nandini Sarkar |
| Science | 2044 | Chanchal Mishra |
| Science | 2050 | Pankaj Das |
| Science | 2021 | Sanghamitra Sharma |
| Science | 2022 | Abdul Shaikh |
| Science | 2033 | Siddique Rehman |
Explanation: The above SELECT query is pretty straightforward and selects a list of columns from the table for the resultset. But one of the columns aliased as ‘stream’ is a CASE expression. The CASE expression contains 5 case conditions against which the major_subject column value from every row in the table is compared one by one and the appropriate result picked up from the CASE expression. Then it is patched with the remaining column values from the other columns in the select list (i.e. roll_no and student_name) and the final resultset is generated and presented. (Please note that we have not used the ELSE clause yet in any of the above examples. We will do so in the upcoming examples.)
However, the above query is a bit long and clumsy. It can be bettered with a SEARCHED CASE expression as below which reduces the number of WHEN statements and makes the query neater and more comprehensible.
| SELECT case WHEN major_subject IN (‘Economics’,’Sociology’) THEN ‘Humanities’ WHEN major_subject IN (‘Zoology’, ‘Geology’, ‘Physics’) THEN ‘Science’ END stream, roll_no, student_name FROM student_result ORDER BY stream; |
It will produce exactly the same output as above.
2) SQL Server CASE – SEARCHED CASE with Aggregate Function example
Now let us explore a little more complicated example using aggregate function. Suppose we want to find out the number of students enrolled in the two broad streams. We can do so with the following query using the COUNT aggregate function.
SELECT
SUM ((CASE WHEN major_subject IN ('Economics','Sociology') THEN 1 ELSE 0 END)) AS 'Humanities',
SUM ((CASE WHEN major_subject IN ('Zoology', 'Geology', 'Physics') THEN 1 ELSE 0 END))
AS 'Science',
COUNT (*) AS ’Total Enrollments’
FROM student_result;
It will produce the following output as per our requirement.
| Humanities | Science | Total Enrollments |
| 7 | 8 | 15 |
Explanation: The above SELECT query has a SELECT list with 3 expressions. The first two expressions use the aggregate function SUM and the third expression uses the aggregate function COUNT. The SUM function adds the Boolean output of a SEARCHED CASE expression which evaluates the column value of major_subject from each row of the table. The first SUM function is aliased Humanities and the second SUM function Science. The third expression counts the value of the previous two expressions (i.e. Humanities and Science) with the help of the COUNT function which is aliased as Total Enrollments. Finally, all 3 expression values are patched with the column aliases as the column headings to form the final resultset.
3) SQL Server CASE – SEARCHED CASE with logical operators
Now in this last example we will use a comparison operator and a logical operator in the CASE expression to achieve intended outcome. Suppose we want to grade the performance of the students on the aggregate percentage they have secured. We can do so with the following query which uses the >= comparison operator and the BETWEEN logical operator in the WHEN conditions.
SELECT roll_no, student_name, CASE WHEN aggregate_percentage >= 90 THEN 'A+' WHEN aggregate_percentage BETWEEN 80 AND 91 THEN 'A' WHEN aggregate_percentage BETWEEN 70 AND 81 THEN 'B' WHEN aggregate_percentage BETWEEN 60 AND 71 THEN 'C' Else 'REEXAMNATION' END AS grade FROM student_result ORDER BY grade;
It will produce the following output as per our requirement.
| roll_no | student_name | grade |
| 2035 | Jasmine Mathew | A |
| 2050 | Pankaj Das | A |
| 2068 | Krishna Kishore | A |
| 2077 | Amit Tripathi | A+ |
| 2090 | Gulshan Singh | A+ |
| 2099 | Samnvay Patekar | A+ |
| 2044 | Chanchal Mishra | A+ |
| 2039 | Prerana Chakraborty | A+ |
| 2040 | Rohit Bhandari | B |
| 2010 | Bikram Chowdhury | B |
| 2022 | Abdul Shaikh | B |
| 2033 | Siddique Rehman | B |
| 2021 | Sanghamitra Sharma | C |
| 2061 | Chinmoy Nath | REEXAMNATION |
| 2067 | Nandini Sarkar | REEXAMNATION |
Explanation: The above SELECT query has a SELECT list with 3 expressions – roll_no, student_name and the case expression aliased as grade. There are 4 WHEN conditions within the CASE expression. The first WHEN condition uses the >= comparison operator to evaluate the aggregate_percentage value fetched from a row in the table. the other 3 WHEN conditions use the BETWEEN logical operator to whether the aggregate_percentage value falls within the specified range. The CASE expression end with an ELSE clause that specifies a result value REEXAMINATION if the aggregate percentage of a student is below 60. Finally all 3 expression values roll_no, student_name and grade are patched to generate the rows of the resultset.