SQL Server CASE

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.

  1. It allows us to subject a value to multiple checks.
  2. 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 expressionSEARCHED 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 functionsIt 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_nostudent_namemajor_subjectaggregate_percentage
2010Bikram ChowdhuryEconomics77
2021Sanghamitra SharmaZoology68
2022Abdul ShaikhZoology79
2033Siddique RehmanGeology70
2035Jasmine MathewSociology80
2039Prerana ChakrabortyEconomics93
2040Rohit BhandariSociology72
2044Chanchal MishraPhysics96
2050Pankaj DasPhysics88
2061Chinmoy NathSociology59
2067Nandini SarkarGeology55
2068Krishna KishoreEconomics88
2077Amit TripathiGeology90
2090Gulshan SinghZoology91
2099Samnvay PatekarSociology90
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.

streamroll_nostudent_name
Humanities2010Bikram Chowdhury
Humanities2035Jasmine Mathew
Humanities2039Prerana Chakraborty
Humanities2040Rohit Bhandari
Humanities2061Chinmoy Nath
Humanities2068Krishna Kishore
Humanities2099Samnvay Patekar
Science2077Amit Tripathi
Science2090Gulshan Singh
Science2067Nandini Sarkar
Science2044Chanchal Mishra
Science2050Pankaj Das
Science2021Sanghamitra Sharma
Science2022Abdul Shaikh
Science2033Siddique 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.

HumanitiesScienceTotal Enrollments
7815

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_nostudent_namegrade
2035Jasmine MathewA
2050Pankaj DasA
2068Krishna KishoreA
2077Amit TripathiA+
2090Gulshan SinghA+
2099Samnvay PatekarA+
2044Chanchal MishraA+
2039Prerana ChakrabortyA+
2040Rohit BhandariB
2010Bikram ChowdhuryB
2022Abdul ShaikhB
2033Siddique RehmanB
2021Sanghamitra SharmaC
2061Chinmoy NathREEXAMNATION
2067Nandini SarkarREEXAMNATION

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.

Advertisement