How to Write a CASE Statement in SQL

Problem:

You want to use a CASE statement in SQL.

Example:

You have exam results in the exam table. You need to assign each result to one of the following text values: 'bad result', 'average result', or 'good result'. Bad results are those below 40, good results are those above 70, and the rest are average results.

The exam table looks like this:

Solution 1:

SELECT
  name,
  result,
  CASE
    WHEN result < 40 THEN 'bad result'
    WHEN result > 70 THEN 'good result'
    ELSE 'average result'
  END AS category
FROM exam;

The result table looks like this:

Discussion:

To display a value based on your specific condition(s), you need to write a CASE statement. The syntax is:

CASE
  WHEN <condition_1> THEN <value_1>
  WHEN <condition_2> THEN <value_2>
  …
  ELSE <value_n>
END AS <column_name>

If condition_1 is met, then the retrieved value is value_1. If not, then the database checks for condition_2. If condition_2 is true, then the retrieved value is value_2. If neither of these conditions is met, SQL checks for the remaining conditions one by one in order until one of the conditions is met. If none of the conditions is met, the value_n specified after the ELSE is retrieved.

The ELSE part is optional. If you omit it and none of the conditions are met, you get a NULL.

Remember to END the CASE clause when you're done with all of the conditions. Of course, as with any column you create, you can rename it (AS <column_name>).

In our example, 'bad result' is assigned when result < 40, and 'good result' is assigned when result > 70. If neither of these conditions is met, the value is 'average result'. Also, to name the newly created column of the text values, you need to use an alias (AS category). This is how you get:

  CASE
    WHEN result < 40 THEN 'bad result'
    WHEN result > 70 THEN 'good result'
    ELSE 'average result'
  END AS category

Since 'average result' is assigned to results between 40 and 70 (inclusive), you may also write a condition instead of ELSE (see Solution 2 below). However, it is easier to use ELSE.

Solution 2:

SELECT
  name,
  result,
  CASE
    WHEN result < 40 THEN 'bad result'
    WHEN result > 70 THEN 'good result'
    WHEN result >= 40 AND result <= 70 THEN 'average result'
  END AS category
FROM exam;

The result table looks like this:

Discussion:

Since 'average result' is assigned to results between 40 and 70 (inclusive), you may write the following condition instead of an ELSE:

WHEN result >= 40 AND result <= 70 THEN 'average result'

If you do not use an ELSE and do not want any NULLs in the category column, you need to make sure you take care of all possible result values. If there's a result that meets none of the conditions, you get a NULL.

Solution 3:

SELECT
  name,
  result,
  CASE
    WHEN result < 40 THEN 'bad result'
    WHEN result <= 70 THEN 'average result'
    ELSE 'good result'
  END AS category
FROM exam;

The result table looks like this:

Discussion:

The specified conditions in CASE can be non-overlapping as in the previous solutions or overlapping as in this solution. The first condition is the same as before – for result < 40, the category is 'bad result'. All results below 40 are assigned this value at this point and you don't need to do anything with them anymore. This means there is no need to make sure result >= 40 in specifying the conditions for the 'average result' – all results less than 40 already have the value 'bad result' assigned. Finally, for the remaining results the category should be 'good result'; a simple ELSE 'good result' takes care of it. This is how you get:

CASE
    WHEN result < 40 THEN 'bad result'
    WHEN result <= 70 THEN 'average result'
    ELSE 'good result'
END AS category

Note: The order of the conditions in CASE matters. If you reverse the order of the first two conditions (i.e., if you specify WHEN result <= 70 THEN 'average result' first then WHEN result <= 70 THEN 'average result' second), all results less than or equal to 70 end up in the 'average result' category, with no results assigned to the 'bad result' category.