Skip to main content

Conditional values with CASE WHEN ... ELSE

Premium

You may find yourself in a situation where you need to categorize data based on specific conditions, perform calculations based on certain conditions, or even create custom fields based on complex expressions. For these scenarios, you'll want to harness the power of the CASE WHEN statement in SQL.

What is "CASE WHEN"?

The CASE WHEN statement is a conditional statement that evaluates one or more expressions and returns a corresponding value based on the evaluation. It's like a more sophisticated version of an if-then-else statement. With CASE WHEN, you can perform complex calculations and transformations on your data to make it more actionable and meaningful. Here's a basic example to illustrate the syntax:

SQL
CASE 
   WHEN expression THEN result
   WHEN expression THEN result
   ELSE result
END

The expression can be any valid expression that returns a Boolean value. If the expression evaluates to true, the corresponding result will be returned. If multiple expressions are evaluated and none are true, the ELSE result will be returned.

When to use CASE WHEN

Let's consider a scenario where you work for a university and have been tasked with calculating the letter grades for each student based on their scores. A grade of A is given for scores above 90, B for scores between 80 and 90, C for scores between 70 and 80, and D for scores below 70. You can use the CASE WHEN statement to calculate the letter grades for each student, like so:

SQL
SELECT 
    student_name,
    score,
    CASE
        WHEN score > 90 THEN 'A'
        WHEN score BETWEEN 80 AND 90 THEN 'B'
        WHEN score BETWEEN 70 AND 80 THEN 'C'
        ELSE 'D'
    END AS letter_grade
FROM 
    student_scores;

The above example demonstrates the flexibility of the CASE WHEN statement and its ability to perform complex calculations on your data.

CASE WHEN in Action

Let's take a look at a sample table of student scores:

+--------------+-------+
| student_name | score |
+--------------+-------+
| Alice        | 92    |
| Bob          | 84    |
| Charlie      | 77    |
| Dave         | 60    |
+--------------+-------+

After running the CASE WHEN statement, the table would look like this:

+--------------+-------+--------------+
| student_name | score | letter_grade |
+--------------+-------+--------------+
| Alice        | 92    | A            |
| Bob          | 84    | B            |
| Charlie      | 77    | C            |
| Dave         | 60    | D            |
+--------------+-------+--------------+

Try it yourself

Given a table of orders with quantity, we want to categorize each order into "small" and "large" orders with a new size column. Let's say large orders have quantity >= 2. How would you do this using CASE WHEN?

Syntax Variations

It's worth noting that the syntax for the CASE WHEN statement may vary slightly depending on the specific SQL implementation you are using. For example, in SQL Server there is an alternative way to write the CASE statement using the IF keyword, but in general the basic structure and functionality remains the same:

SQL
IF condition_1 result_1 
ELSE IF condition_2 result_2 
... 
ELSE result_n 
END
Next Lesson