SQL CASE WHEN: How to Use

The CASE WHEN statement is a type of conditional statement in SQL that is used to return different values based on different conditions.

The basic syntax is as follows:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE result
END

condition1, condition2, and so on are conditional expressions that can be any boolean expression. result1, result2, and so on are the results obtained based on the conditions calculated.

Here is an example, providing corresponding comments based on different grade levels.

SELECT grade,
       CASE
           WHEN grade >= 90 THEN '优秀'
           WHEN grade >= 80 THEN '良好'
           WHEN grade >= 70 THEN '中等'
           WHEN grade >= 60 THEN '及格'
           ELSE '不及格'
       END AS comment
FROM scores;

In this example, based on the different ranges of grades, the corresponding comments are returned. If the grade is greater than or equal to 90, the comment is ‘excellent’, if the grade is between 80 and 89, the comment is ‘good’, and so on.

It’s important to note that the CASE WHEN statement is evaluated in order, and only the result of the first condition that is met will be returned. If no conditions are met, the result after ELSE will be returned.

The CASE WHEN statement can also be nested to achieve more complex condition evaluation.