SQL Case Statement Tutorial – With When-Then Clause Example Queries

By Veronica Stork

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.

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:

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.

Conclusion

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!