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
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
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
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.
END are all required.
AS are optional. The
CASE statement must go in the
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:
|Akiko||finish that essay!|
|Clement||finish that essay!|
|Alyssa||finish that essay!|
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... 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.
|Akiko||NULL||finish that essay!|
|Clement||NULL||finish that essay!|
|Alyssa||NULL||finish that essay!|
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!