SQL CASE Statement

Introduction to SQL CASE Statement

CASE is an expression statement in Standard Query Language(SQL) used primarily for handling conditional statements similar to IF-THEN-ELSE in other programming languages. A case statement evaluates the when conditions if found true, returns the THEN part of the statement and ends. If no condition is satisfied or found FALSE, then it evaluates the ELSE part of the statement and ends.

A CASE statement is always followed by a WHEN and THEN parts. It can also contain ELSE parts but they are not compulsory. It is always closed with an END keyword. A CASE statement is generally used as a part of a SELECT clause, but we can also use it as a part of other clauses like WHERE, ORDER BY, GROUP BY when working with aggregate functions.

A CASE statement can be of two types. The first one is simple CASE statements where we compare the WHEN conditional expression to a static value. The second type is searched CASE statements where we compare the WHEN conditional expression to multiple logical conditions. It is more like nested if-else statements.

Syntax and Parameters

The basic syntax of a CASE statement in SQL is as follows :

CASE
WHEN when_condition_1 THEN result_expression_1
WHEN when_condition_2 THEN  result_expression_2
.
.
.
WHEN when_condition_n THEN result_expression_n
ELSE result_expression
END AS case_name;

The parameters used in the above-mentioned syntax are as follows :

• CASE: It is an essential keyword that is always used to mark the beginning of a CASE statement.
• WHEN when_condition_1: It is a simple conditional expression like IF condition which is evaluated for TRUE or FALSE, based on that result_expression_1 is returned if WHEN is evaluated to TRUE or ELSE statement is evaluated if WHEN is evaluated to FALSE.
• ELSE result_expression: Simple conditional expression which is evaluated WHEN condition is evaluated to FALSE.
• END: It is an essential keyword that is always used to mark the ending of a CASE statement. We can provide an alias to the CASE using AS.

Out of the above-mentioned parameters, all parameters except ELSE are compulsory.

Example

Here are a few examples to illustrate the syntax and functions of CASE statements in SQL.

To illustrate the functions and applications of the CASE  statement in SQL, let’s create an imaginary ‘students’ table. The table will contain student details such as their student id, first name, last name, marks, subject, etc. The ‘students’ table can be created in the following manner.

Code:

CREATE TABLE students(
student_id int,
first_name varchar(255),
last_name varchar(255),
subject varchar(255),
marks numeric
);

Let’s insert some values in the table to populate the dataset using the INSERT statements.

INSERT INTO Students VALUES
(1,'Rohit','Sharma','Maths', 472),
(2,'Michael','Douglas','Science', 430),
(3,'Aliya','K','Science', 320),
(4,'April', 'Howard','Maths',400),
(5,'Karthik','Narayan', 'Science', 240),
(1,'Rohit','Sharma','Science', 328),
(2,'Michael','Douglas','Maths', 470),
(3,'Aliya','K','Maths', 220),
(4,'April', 'Howard','Science',500),
(5,'Karthik','Narayan', 'Maths', 340);

The data in the ‘students’ table after insertion looks something like this:

select * from Students;

Output:

Examples to Implement SQL CASE Statement

Let us discuss some examples to understand better:

Example #1

Assuming that one should get at least 250 out of 500 marks to pass a subject,  based on the marks obtained by a student in a particular subject, mention if he has passed or failed the subject.

Code:

SELECT student_id, first_name, last_name, subject, marks,
CASE
WHEN marks > 250 THEN 'PASSED'
ELSE 'FAILED'
END AS result
FROM students;

Output:

Explanation: In the above example, the CASE statement checks the WHEN part, i.e if marks > 250 are TRUE. If it is TRUE then it returns ‘PASSED’ otherwise moves to the ELSE part of the statement and returns ‘FAILED’.

Example #2

Allot a grade to each student based on marks obtained by him/her in a subject. The following grading scale can be used for the purpose.  More than 450  : A+, 400 – 450 : A , 350 – 400 : B+, 300-350 : B, 250-300 : C, Below 250 : ‘Fail’

Code:

SELECT student_id, first_name, last_name, subject, marks,
CASE
WHEN marks >450 THEN 'A+'
WHEN marks > 400 AND marks <= 450 THEN 'A'
WHEN marks > 350 AND marks <= 400 THEN 'B+'
WHEN marks > 300 AND marks <= 350 THEN 'B'
WHEN marks > 250 AND marks <= 300 THEN 'C'
WHEN marks < 250 THEN 'Fail'
END AS result
FROM students;

Output:

Example #3

Declare each student if he/she has passed or failed overall based on the total marks obtained by him or her in all the subjects. Assume that one should secure 500 marks in total to pass.

Code:

SELECT student_id, first_name, last_name, sum(marks) as tot_marks,
CASE
WHEN SUM(marks) > '500' THEN 'PASSED'
ELSE 'FAILED'
END AS result
FROM students
GROUP BY student_id, first_name, last_name;

Output:

Explanation: This example is to illustrate the use of aggregate functions in CASE conditions in SQL.

Example #4

SQL query to illustrate nested CASE statements.

Code:

SELECT student_id, first_name, last_name, sum(marks),
CASE
WHEN SUM(marks) > 500 THEN
CASE
WHEN sum(marks) > 800 THEN 'PASSED with distinction'
ELSE 'PASSED'
END
ELSE 'FAILED'
END AS result
FROM students
GROUP BY student_id, first_name, last_name;

Output:

Explanation: In the above example, we have just extended example no. 3. Here, first, we are trying to check if the first WHEN condition i.e. sum(marks) > 500 is true or not. If it is true, we have another WHEN condition, i.e. sum(marks) > 800. If the second condition is TRUE, the student is assigned ‘PASSED with Distinction’, else just ‘PASSED’.

Conclusion

CASE statements in SQL are like IF-THEN-ELSE conditional statements. They help us in performing conditional operations while performing selection, grouping and ordering tasks in SQL.

Recommended Articles

This is a guide to the SQL CASE Statement. Here we discuss syntax and parameters of SQL CASE Statement and examples to implement. You can also go through our other related articles to learn more –

1. SQL SELECT RANDOM
2. SQL RANK()
3. SQL Server Substring
4. BETWEEN in SQL
5. Guide to SQL While Loop