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
Table: student_result
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.