How to write a CASE statement in SQL

Problem

You want to learn how to write a CASE statement in SQL. In particular, you want to understand how it can be used to perform conditional logic in your SQL queries.

Sample Data

Let's consider a simple table named Students with the following data:

MySQL Solution

SELECT Name, Grade,
CASE
    WHEN Grade >= 90 THEN 'A'
    WHEN Grade >= 80 THEN 'B'
    WHEN Grade >= 70 THEN 'C'
    ELSE 'D'
END as LetterGrade
FROM Students;

PostgreSQL Solution

SELECT Name, Grade,
CASE
    WHEN Grade >= 90 THEN 'A'
    WHEN Grade >= 80 THEN 'B'
    WHEN Grade >= 70 THEN 'C'
    ELSE 'D'
END as LetterGrade
FROM Students;

Explanation

In both MySQL and PostgreSQL, the CASE statement allows you to perform conditional logic in SQL. It works just like a series of IF/ELSE statements in other programming languages.

The CASE statement begins with CASE and ends with END. In between, there are one or more WHEN conditions followed by THEN results. If a condition is true, then the respective result is returned. If none of the conditions are true, the ELSE result is returned (if specified).

In the given examples, we're assigning a letter grade to each student based on their numerical grade. If a student's grade is greater than or equal to 90, they get an 'A'. If it's between 80 and 89, they get a 'B', and so on. If a student's grade is less than 70, they get a 'D'. The result of the CASE statement is being aliased as LetterGrade for clarity in the result set.