SQL Case

SQL Case

This page covers SQL Case.

The concept of “case” is very similar to the “if/else” statements in many other programming languages.

The expression evaluates specific conditions. It then outputs an outcome if one of the conditions is met.

Let’s use the table “student_info” from the previous tutorials.

id	subjectName	grade	passed	studentID
1	History	C	1	1
2	Physics	A	1	2
3	History	F	0	3
4	Biology	A	1	NULL
5	Biology	E	1	5

Case

The “case” expression has several keywords.

It uses case/end to start and end the expression. The conditions are expressed with a when/then statements.

When refers to the actual condition we need to meet. Then specifies what to return if the particular condition is met.

In addition, there is an “else” statement. Else executes if non of the conditions meet the criteria.

The following is an example of the “case” expression.

SELECT studentID, grade,
CASE
	WHEN grade = 'A' THEN 'The student has an excellent grade!'
	WHEN grade = 'B' THEN 'The student has done very well!'
	WHEN grade = 'C' THEN 'The student has done well!'
	WHEN grade = 'D' THEN 'The student has passed.'
	WHEN grade = 'E' THEN 'The student has barely passed.'
	ELSE 'The student has failed'
END
FROM grade;

studentID	grade	No Column Name
1	C	The student has done well!
2	A	The student has an excellent grade!
3	F	The student has failed
NULL	A	The student has an excellent grade!
5	E	The student has barely passed.

To rename the new column name, we can add an alias after the “end” statement (e.g. END AS column_name).

The comprehension of the result should be quite straightforward.

When the grade is “A”, then the expression outputs “The student has done well!”. The same logic follows the rest of the conditions.

The last (else) condition executes if non of the other conditions meet the criteria. The 3rd student has an “F”, which is not in the conditions when/then. Therefore, the query produces the “else” statement.

Let’s look at another example.

SELECT studentID, grade,
CASE
	WHEN passed = 1 THEN 'Pass'
	ELSE 'Fail'
END AS 'Status'
FROM grade;

studentID	grade	Status
1	C	Pass
2	A	Pass
3	F	Fail
NULL	A	Pass
5	E	Pass

---

Next: SQL Procedure