MySQL CASE Operator

In MySQL, the CASE operator compares a list of conditions and returns one of multiple possible result expressions.

The CASE expression is included in the SQL standard (ISO/IEC 9075), and most major RDBMSs support it.

MySQL also has the CASE statement, which is slightly different to the CASE operator. This article is about the CASE operator.

Syntax

We can use the CASE operator in one of two ways.

One way is to use it as a simple CASE expression:

CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...] [ELSE result] END

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

The other option is to use it as a searched CASE expression:

CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

As mentioned, MySQL also has the CASE statement (as opposed to the CASE operator that’s being discussed here). The CASE statement is for use within stored programs. The main difference between the two is that the CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

The examples on this page are for the CASE operator.

Example – Simple `CASE` Expression

As mentioned, the simple CASE expression compares an expression to a set of simple expressions to determine the result.

Here’s an example to demonstrate:

SET @cuisine = 'Thai';

SELECT  
    CASE @cuisine  
        WHEN 'Italian' THEN 'Pasta'
        WHEN 'Thai' THEN 'Pad Thai'
        WHEN 'Greek' THEN 'Souvlaki'
        ELSE 'Salad'  
    END;

Result:

Pad Thai

The actual CASE expression in this example is everything that follows SELECT. So for this example I used the CASE expression in a SELECT statement, but it could easily have been used in another context.

Specifically, I checked the value of the @cuisine variable and returned a different result depending on the value of that variable.

Example – Searched `CASE` Expression

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Example:

SET @score = 6;

SELECT
    CASE   
        WHEN @score > 8 THEN 'Congratulations!'
        WHEN @score > 5 AND @score < 8 THEN 'Not too bad'
        ELSE 'Try harder next time'  
    END;

Result:

Not too bad

The searched CASE expression doesn’t have an input expression like the simple CASE expression.

A Database Example

Here’s an example that demonstrates how the MySQL CASE operator can be used in a database query:

USE World;
SELECT
    Name,
    Population,
      CASE 
         WHEN Population > 2000000 THEN 'Huge City'  
         WHEN Population >= 1000000 AND Population < 2000000 THEN 'Big City' 
         ELSE 'Small City'
      END AS Size
FROM City
WHERE CountryCode = 'AUS'
ORDER BY Population DESC
LIMIT 10;

Result:

+---------------+------------+------------+
| Name          | Population | Size       |
+---------------+------------+------------+
| Sydney        |    3276207 | Huge City  |
| Melbourne     |    2865329 | Huge City  |
| Brisbane      |    1291117 | Big City   |
| Perth         |    1096829 | Big City   |
| Adelaide      |     978100 | Small City |
| Canberra      |     322723 | Small City |
| Gold Coast    |     311932 | Small City |
| Newcastle     |     270324 | Small City |
| Central Coast |     227657 | Small City |
| Wollongong    |     219761 | Small City |
+---------------+------------+------------+

This example uses a searched CASE expression to evaluate the results from the Population column of the City table. We were able to output a different value that depended on the outcome of the Boolean expressions.

Anything that fell outside of the Boolean expressions was captured in the ELSE clause.

The Optional `ELSE` Clause

The above examples use the ELSE clause to determine what happens if none of the conditions are met, however, the ELSE clause is optional.

If we omit the ELSE clause, and none of the conditions are met, the result is NULL.

Let’s omit the ELSE clause from the previous example:

USE World;
SELECT
    Name,
    Population,
      CASE 
         WHEN Population > 2000000 THEN 'Huge City'  
         WHEN Population >= 1000000 AND Population < 2000000 THEN 'Big City'
      END AS Size
FROM City
WHERE CountryCode = 'AUS'
ORDER BY Population DESC
LIMIT 10;

Result:

+---------------+------------+-----------+
| Name          | Population | Size      |
+---------------+------------+-----------+
| Sydney        |    3276207 | Huge City |
| Melbourne     |    2865329 | Huge City |
| Brisbane      |    1291117 | Big City  |
| Perth         |    1096829 | Big City  |
| Adelaide      |     978100 | NULL      |
| Canberra      |     322723 | NULL      |
| Gold Coast    |     311932 | NULL      |
| Newcastle     |     270324 | NULL      |
| Central Coast |     227657 | NULL      |
| Wollongong    |     219761 | NULL      |
+---------------+------------+-----------+

Now any value that doesn’t meet the conditional criteria returns NULL.

Using the `CASE` Operator in Other Contexts

The CASE operator isn’t limited to just SELECT statements. We can use it pretty much anywhere a valid expression can be used.

For example, we can use the CASE operator as part of an UPDATE statement when updating data in a database.

Suppose we add a column to the City table from the previous example:

ALTER TABLE City
ADD COLUMN Size VARCHAR(30) AFTER Population;

Now let’s include that new column in our SELECT list:

SELECT * FROM City
LIMIT 10;

Result:

+----+----------------+-------------+---------------+------------+------+
| ID | Name           | CountryCode | District      | Population | Size |
+----+----------------+-------------+---------------+------------+------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 | NULL |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 | NULL |
|  3 | Herat          | AFG         | Herat         |     186800 | NULL |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 | NULL |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 | NULL |
|  6 | Rotterdam      | NLD         | Zuid-Holland  |     593321 | NULL |
|  7 | Haag           | NLD         | Zuid-Holland  |     440900 | NULL |
|  8 | Utrecht        | NLD         | Utrecht       |     234323 | NULL |
|  9 | Eindhoven      | NLD         | Noord-Brabant |     201843 | NULL |
| 10 | Tilburg        | NLD         | Noord-Brabant |     193238 | NULL |
+----+----------------+-------------+---------------+------------+------+

We haven’t inserted any data into the new Size column, so it returns NULL in every row.

We can now use a CASE expression to update the Size column with a value that depends on the value in the Population column:

UPDATE City 
SET Size = 
    CASE 
        WHEN Population > 2000000 THEN 'Huge City'  
        WHEN Population >= 1000000 AND Population < 2000000 THEN 'Big City'
        ELSE 'Small City'
    END;

Now let’s select data from the table:

SELECT
    Name,
    Population,
    Size
FROM City
WHERE CountryCode = 'AUS'
ORDER BY Population DESC
LIMIT 10;

Result:

+---------------+------------+------------+
| Name          | Population | Size       |
+---------------+------------+------------+
| Sydney        |    3276207 | Huge City  |
| Melbourne     |    2865329 | Huge City  |
| Brisbane      |    1291117 | Big City   |
| Perth         |    1096829 | Big City   |
| Adelaide      |     978100 | Small City |
| Canberra      |     322723 | Small City |
| Gold Coast    |     311932 | Small City |
| Newcastle     |     270324 | Small City |
| Central Coast |     227657 | Small City |
| Wollongong    |     219761 | Small City |
+---------------+------------+------------+

The same concept can be applied to INSERT statements, ORDER BY clauses, and much more.

Alternatives when Dealing with `NULL` Values

MySQL includes functions such as COALESCE() and NULLIF() which provide a more concise alternative to dealing with NULL values than writing code with the CASE operator.

Basically, you have the choice between writing a CASE expression or using one of those functions.

The COALESCE() function returns the first non-NULL value from its list of arguments. NULLIF() returns NULL if both of its arguments are equivalent, and the first argument if they’re different.

Syntax

Example – Simple CASE Expression

Example – Searched CASE Expression

A Database Example

The Optional ELSE Clause

Using the CASE Operator in Other Contexts

Alternatives when Dealing with NULL Values

Example – Simple `CASE` Expression

Example – Searched `CASE` Expression

The Optional `ELSE` Clause

Using the `CASE` Operator in Other Contexts

Alternatives when Dealing with `NULL` Values