SQLite CASE Expression

In SQLite, the CASE expression compares a list of conditions and returns one of multiple possible expressions.

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

Syntax

The CASE expression can take two forms. The first form is known as the simple CASE expression:

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

When using this form, the value part is known as the “base” expression. The base expression is compared to a set of simple expressions to determine the result.

The other form is the 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. No base expression is supplied.

Example

Here’s an example to demonstrate the simple CASE expression (i.e. the one with the base expression):

SELECT
    PetName,
    TypeId,
    CASE TypeId
        WHEN 1 THEN 'Dog'
        WHEN 2 THEN 'Cat'
        WHEN 3 THEN 'Buffalo'
        ELSE 'Other'
    END AS Type
FROM Pets;

Result:

PetName  TypeId  Type   
-------  ------  -------
Homer    3       Buffalo
Yelp     1       Dog    
Fluff    2       Cat    
Brush    4       Other  

We could rewrite this to a searched CASE expression. Doing this means we would remove the base expression, but then include it in the WHEN part of the statement:

SELECT
    PetName,
    TypeId,
    CASE 
        WHEN TypeId = 1 THEN 'Dog'
        WHEN TypeId = 2 THEN 'Cat'
        WHEN TypeId = 3 THEN 'Buffalo'
        ELSE 'Other'
    END AS Type
FROM Pets;

Result:

PetName  TypeId  Type   
-------  ------  -------
Homer    3       Buffalo
Yelp     1       Dog    
Fluff    2       Cat    
Brush    4       Other  

We get the same result. However, there’s a difference in the processing. When we use the simple CASE expression, TypeId is only evaluated once. But when we use the searched CASE expression, TypeId is evaluated multiple times.

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 first set our .nullvalue to a value other than the default empty space:

.nullvalue <null>

That ensures that any NULL values will appear as <null>.

Now let’s run the first example again, but this time we’ll omit the ELSE clause:

SELECT
    PetName,
    TypeId,
    CASE TypeId
        WHEN 1 THEN 'Dog'
        WHEN 2 THEN 'Cat'
        WHEN 3 THEN 'Buffalo'
    END AS Type
FROM Pets;

Result:

PetName  TypeId  Type   
-------  ------  -------
Homer    3       Buffalo
Yelp     1       Dog    
Fluff    2       Cat    
Brush    4       <null> 

This time, instead of getting Other in the last row, we got <null>, which indicates that it’s a NULL value.

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. We can also use it in an INSERT statement, and even in an ORDER BY clause. See SQL CASE Statement for examples.

The IIF() Function – A Shortcut

SQLite also has the IIF() function that can be used as a shortcut for certain CASE expressions.

Therefore, the following:

CASE WHEN x THEN y ELSE z END

Can also be written as this:

IIF(x, y, z)