What is a SQL Case Statement?

A case statement is basically SQL's version of conditional logic. It can be used in the same way as if statements in programming languages like JavaScript, but it is structured slightly differently.

Sample Data

Imagine that you are teaching a literature course. Your students must write an essay in order to meet the course requirements.

You have created the table below to track which students have submitted their essay, along with their grade. If they have not yet submitted their essay, their grade is listed as NULL.

student_id name submitted_essay grade
1 John TRUE 86
2 Said TRUE 90
4 Noah TRUE 68
5 Eleanor TRUE 95
7 Otto TRUE 76
8 Jamal TRUE 85
9 Kiara TRUE 88
10 Clement FALSE NULL

How to Write a Case Statement in SQL

Maybe you would like to give your students a message regarding the status of their assignment. To get the status, you could just select the submitted_essay column, but a message that just says TRUE or FALSE is not especially human-readable.

Instead, you could use a CASE statement and print out different messages depending on whether submitted_essay is true or false.

The basic structure of the CASE statement is CASE WHEN... THEN... END. CASE WHEN, THEN, and END are all required. ELSE and AS are optional. The CASE statement must go in the SELECT clause.

SELECT name,
	CASE WHEN submitted_essay IS TRUE THEN 'essay submitted!'
	ELSE 'finish that essay!' END  AS status
FROM students;

In the above example, we are selecting our students' names and then displaying different messages in the status column depending on whether submitted_essay is true or not. The resulting table looks like the following:

name status
Akiko finish that essay!
Clement finish that essay!
Alyssa finish that essay!
Said essay submitted!
Eleanor essay submitted!
Otto essay submitted!
Noah essay submitted!
Kiara essay submitted!
John essay submitted!
Jamal essay submitted!

Now, say you wanted to include a little more info. You want to comment on the students' grades if they have submitted their essay, and tell them to finish their essay if they have not yet submitted it. This is where multiple WHEN/THEN statements can be helpful.

SELECT name, essay_grade,
CASE WHEN essay_grade >= 80 THEN 'great job'
	 WHEN essay_grade < 80 THEN 'try harder'
	 ELSE 'finish that essay!' END  AS teacher_comment
FROM students;

In the code sample above, we are displaying the students' names and essay grades, along with comments that differ depending on their grades.

After the first WHEN/THEN statement, you can add as many other WHEN/THEN statements as you need, along with an ELSE statement to catch other possible cases. This is analogous to if... else if... else style logic in JavaScript (or if... elif... else in Python, and so on).

Note that in this case, ELSE is intended to catch the essays with grades of NULL (meaning those that have not yet been submitted,) but in other situations, you could use IS NULL to check if a selected value is null.

Don't forget to end your case statement with END! Below, you can see the results of this query.

name essay_grade teacher_comment
Akiko NULL finish that essay!
Clement NULL finish that essay!
Alyssa NULL finish that essay!
Said 90 great job
Eleanor 95 great job
Otto 76 try harder
Noah 68 try harder
Kiara 88 great job
John 86 great job
Jamal 85 great job


Case statements are a clear, concise way to make sense of your queries in SQL, and they are easy to learn and understand. Happy querying!