SQL CASE Statement

In SQL, the CASE statement evaluates a list of conditions and returns one of multiple possible result expressions.

In some ways, the SQL CASE statement is kind of similar to the IF...ELSE statement in that it allows us to check for a given condition and return a different result depending on the outcome.

Is it a `CASE` Statement or `CASE` Expression?

In SQL, sometimes things are referred to as a “statement” when in fact, they’re something else. The SQL “CASE statement” is a case in point (sorry about the pun!).

The CASE statement is referred to in the SQL standard (ISO/IEC 9075) as the CASE expression. Its purpose is to “specify a conditional value”.

However, some DBMSs distinguish between the CASE statement and the CASE expression, and have a slightly different syntax for each. For example, both MySQL and MariaDB provide the CASE statement and the CASE operator as two distinct features, each with slightly different syntax.

`CASE` Formats

In SQL, there are two formats of CASE expression:

Simple CASE expression
Searched CASE expression

Below are examples of each.

The Simple `CASE` Expression

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

Example:

DECLARE @animal VARCHAR(40);
SET @animal = 'Cow';

SELECT  
    CASE @animal  
        WHEN 'Bird' THEN 'Seed'
        WHEN 'Dog' THEN 'Beef'
        WHEN 'Cow' THEN 'Grass'
        ELSE 'Leftovers'  
    END;

Result:

Grass

This example was done in MySQL, but the actual CASE expression should work across most major RDBMSs.

In this example, my CASE expression is part of a SELECT statement. It checks for three conditions and has an ELSE to cater for anything that isn’t covered in the three conditions.

In this case, the animal Cow matches the third WHEN expression, and the expression provided by its THEN is returned.

To be clear, the actual CASE expression is this part:

    CASE @animal  
        WHEN 'Bird' THEN 'Seed'
        WHEN 'Dog' THEN 'Beef'
        WHEN 'Cow' THEN 'Grass'
        ELSE 'Leftovers'  
    END

What CASE does is check the value of each WHEN expression against the input expression. In this example, the @animal variable is the input expression. Therefore, it’s checking the value of each WHEN expression against the @animal variable.

When/if it finds a match, it returns the expression provided by the corresponding THEN.

My example uses three WHEN expressions, but I could have used more and I could have used less, depending on the requirements.

The Searched `CASE` Expression

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

Here’s an example of a searched CASE expression.

DECLARE @score int;
SET @score = 7;

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

Result:

Well done!

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

You’ll recall that in our simple CASE expression, it started off with CASE @animal, and therefore we knew that the WHEN expressions were all evaluating against the value of @animal.

With the searched CASE expression, we don’t provide an input expression at the start like that. Instead, each WHEN expression includes a Boolean expression for which to be evaluated against.

A Database Example

Here’s an example that demonstrates how the CASE expression can be used within 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 = 'USA'
ORDER BY Population DESC
LIMIT 20;

Result:

+---------------+------------+------------+
| Name          | Population | Size       |
+---------------+------------+------------+
| New York      |    8008278 | Huge City  |
| Los Angeles   |    3694820 | Huge City  |
| Chicago       |    2896016 | Huge City  |
| Houston       |    1953631 | Big City   |
| Philadelphia  |    1517550 | Big City   |
| Phoenix       |    1321045 | Big City   |
| San Diego     |    1223400 | Big City   |
| Dallas        |    1188580 | Big City   |
| San Antonio   |    1144646 | Big City   |
| Detroit       |     951270 | Small City |
| San Jose      |     894943 | Small City |
| Indianapolis  |     791926 | Small City |
| San Francisco |     776733 | Small City |
| Jacksonville  |     735167 | Small City |
| Columbus      |     711470 | Small City |
| Austin        |     656562 | Small City |
| Baltimore     |     651154 | Small City |
| Memphis       |     650100 | Small City |
| Milwaukee     |     596974 | Small City |
| Boston        |     589141 | Small City |
+---------------+------------+------------+

This example uses a searched CASE expression to evaluate the results from the Population column of the City table.

`ELSE` is Optional

The ELSE argument is optional. If we omit the ELSE, and none of the conditions are triggered, the result is NULL.

Here’s what happens when we 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 = 'USA'
ORDER BY Population DESC
LIMIT 20;

Result:

+---------------+------------+-----------+
| Name          | Population | Size      |
+---------------+------------+-----------+
| New York      |    8008278 | Huge City |
| Los Angeles   |    3694820 | Huge City |
| Chicago       |    2896016 | Huge City |
| Houston       |    1953631 | Big City  |
| Philadelphia  |    1517550 | Big City  |
| Phoenix       |    1321045 | Big City  |
| San Diego     |    1223400 | Big City  |
| Dallas        |    1188580 | Big City  |
| San Antonio   |    1144646 | Big City  |
| Detroit       |     951270 | NULL      |
| San Jose      |     894943 | NULL      |
| Indianapolis  |     791926 | NULL      |
| San Francisco |     776733 | NULL      |
| Jacksonville  |     735167 | NULL      |
| Columbus      |     711470 | NULL      |
| Austin        |     656562 | NULL      |
| Baltimore     |     651154 | NULL      |
| Memphis       |     650100 | NULL      |
| Milwaukee     |     596974 | NULL      |
| Boston        |     589141 | NULL      |
+---------------+------------+-----------+

`CASE` in an `UPDATE` Statement

Let’s add a column to the City table from the previous example:

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

SELECT * FROM City
LIMIT 10;

Here’s what it looks like now:

+----+----------------+-------------+---------------+------------+------+
| 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 * FROM City
WHERE CountryCode = 'USA'
ORDER BY Population DESC
LIMIT 20;

Result:

+------+---------------+-------------+---------------+------------+------------+
| ID   | Name          | CountryCode | District      | Population | Size       |
+------+---------------+-------------+---------------+------------+------------+
| 3793 | New York      | USA         | New York      |    8008278 | Huge City  |
| 3794 | Los Angeles   | USA         | California    |    3694820 | Huge City  |
| 3795 | Chicago       | USA         | Illinois      |    2896016 | Huge City  |
| 3796 | Houston       | USA         | Texas         |    1953631 | Big City   |
| 3797 | Philadelphia  | USA         | Pennsylvania  |    1517550 | Big City   |
| 3798 | Phoenix       | USA         | Arizona       |    1321045 | Big City   |
| 3799 | San Diego     | USA         | California    |    1223400 | Big City   |
| 3800 | Dallas        | USA         | Texas         |    1188580 | Big City   |
| 3801 | San Antonio   | USA         | Texas         |    1144646 | Big City   |
| 3802 | Detroit       | USA         | Michigan      |     951270 | Small City |
| 3803 | San Jose      | USA         | California    |     894943 | Small City |
| 3804 | Indianapolis  | USA         | Indiana       |     791926 | Small City |
| 3805 | San Francisco | USA         | California    |     776733 | Small City |
| 3806 | Jacksonville  | USA         | Florida       |     735167 | Small City |
| 3807 | Columbus      | USA         | Ohio          |     711470 | Small City |
| 3808 | Austin        | USA         | Texas         |     656562 | Small City |
| 3809 | Baltimore     | USA         | Maryland      |     651154 | Small City |
| 3810 | Memphis       | USA         | Tennessee     |     650100 | Small City |
| 3811 | Milwaukee     | USA         | Wisconsin     |     596974 | Small City |
| 3812 | Boston        | USA         | Massachusetts |     589141 | Small City |
+------+---------------+-------------+---------------+------------+------------+

`CASE` in an `INSERT` Statement

Suppose we have the following table in a SQL Server database:

+---------+-----------+-----------+--------------+
| DogId   | DogName   | GoodDog   | Dinner       |
|---------+-----------+-----------+--------------|
| 1001    | Brian     | 1         | Sunday Roast |
| 1002    | Rambo     | 0         | Airline food |
| 1003    | BamBam    | 1         | Sunday Roast |
+---------+-----------+-----------+--------------+

Let’s insert a new row into that table. But let’s use the CASE expression to insert the appropriate value into the Dinner column, depending on the value in the GoodDog column:

DECLARE @DogName nvarchar(60), @GoodDog bit;
SET @DogName = 'Lazy';
SET @GoodDog = 0;

INSERT INTO Dogs ( DogName, GoodDog, Dinner )
VALUES (
    @DogName,
    @GoodDog,
    CASE @GoodDog
        WHEN 1 THEN 'Sunday Roast'
        ELSE 'Airline food'
    END
    );

Here, the CASE expression evaluated the value of a variable we’d just set, and then inserted the appropriate value into the Dinner column.

Now let’s check the table again:

SELECT * FROM Dogs;

Result:

+---------+-----------+-----------+--------------+
| DogId   | DogName   | GoodDog   | Dinner       |
|---------+-----------+-----------+--------------|
| 1001    | Brian     | 1         | Sunday Roast |
| 1002    | Rambo     | 0         | Airline food |
| 1003    | BamBam    | 1         | Sunday Roast |
| 1004    | Lazy      | 0         | Airline food |
+---------+-----------+-----------+--------------+

We can see that the appropriate value is in the Dinner column.

`CASE` in an `ORDER BY` Clause

The CASE expression can be used in any statement or clause that allows a valid expression. Therefore, you can use it in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as IN, WHERE, ORDER BY, GROUP BY, and HAVING.

Using a CASE expression in a statement’s ORDER BY clause can be handy when you want to make a special exception for certain values when ordering your results.

Suppose we run the following query against a table containing music genres.

SELECT Genre 
FROM Genres
ORDER BY Genre ASC;

Result:

+---------+
| Genre   |
+---------+
| Blues   |
| Country |
| Hip Hop |
| Jazz    |
| Other   |
| Pop     |
| Punk    |
| Rap     |
| Rock    |
+---------+

Here, we order the results by the Genre column, in ascending order.

This is fine except for one thing. The genre called Other. Wouldn’t it be nice if we could move Other to the bottom?

We can achieve this with the CASE expression by taking the above query, and modifying it as follows.

SELECT Genre
FROM Genres
ORDER BY 
    CASE Genre
        WHEN 'Other' THEN 1
        ELSE 0
    END
    ASC, Genre ASC;

Result:

+---------+
| Genre   |
+---------+
| Blues   |
| Country |
| Hip Hop |
| Jazz    |
| Pop     |
| Punk    |
| Rap     |
| Rock    |
| Other   |
+---------+

The `COALESCE()` and `NULLIF()` Functions

Depending on the scenario we can use functions such as COALESCE() and NULLIF() as a shortcut, instead of using the CASE expression.

These two functions are SQL standard, and they work as follows:

NULLIF (V1, V2)

Is equivalent to:

CASE WHEN V1=V2 THEN NULL ELSE V1 END

And:

COALESCE (V1, V2)

Is equivalent to:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

Also:

COALESCE (V1, V2, ..., Vn)

Is equivalent to:

CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, ..., Vn) END

Is it a CASE Statement or CASE Expression?

CASE Formats

The Simple CASE Expression

The Searched CASE Expression

A Database Example

ELSE is Optional

CASE in an UPDATE Statement

CASE in an INSERT Statement

CASE in an ORDER BY Clause

The COALESCE() and NULLIF() Functions

Is it a `CASE` Statement or `CASE` Expression?

`CASE` Formats

The Simple `CASE` Expression

The Searched `CASE` Expression

`ELSE` is Optional

`CASE` in an `UPDATE` Statement

`CASE` in an `INSERT` Statement

`CASE` in an `ORDER BY` Clause

The `COALESCE()` and `NULLIF()` Functions