MariaDB CASE Expression

In MariaDB, CASE is a built-in expression, which is similar to if-elseif-else, and is used for multi-branch situations in flow control.

You can use the CASE operator to compare lists of conditions and return different results depending on which conditions (if any) match.

MariaDB CASE Syntax

Here is the syntax of the MariaDB CASE statement:

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

or

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

Return value

The CASE expression the result in the THEN clause whose condition or value = compare_value is true.

MariaDB CASE Examples

CASE Statements are used in the case of multiple logical judgment branches. The following example shows how to pass the weekday index (0 to 6) to return the name of the corresponding weekday.

First, let’s create a table named test_case_weekday for demonstration.

DROP TABLE IF EXISTS test_case_weekday;
CREATE TABLE test_case_weekday (
    weekday_index INT NOT NULL
);

Then, let’s insert some rows:

INSERT INTO test_case_weekday
VALUES (0), (1), (2), (3), (4), (5), (6);

Then, let’s look at the rows in the table:

SELECT * FROM test_case_weekday;

Output:

+---------------+
| weekday_index |
+---------------+
|             0 |
|             1 |
|             2 |
|             3 |
|             4 |
|             5 |
|             6 |
+---------------+

If we want to find out weekday indexes and weekday names in one query, we can use the CASE statement, as follows:

SELECT
    weekday_index AS `Weekday Index`,
    CASE weekday_index
        WHEN 0 THEN 'Monday'
        WHEN 1 THEN 'Tuesday'
        WHEN 2 THEN 'Wednesday'
        WHEN 3 THEN 'Thursday'
        WHEN 4 THEN 'Friday'
        WHEN 5 THEN 'Saturday'
        WHEN 6 THEN 'Sunday'
        ELSE 'Error'
    END AS `Weekday Name`
FROM
    test_case_weekday;

+---------------+--------------+
| Weekday Index | Weekday Name |
+---------------+--------------+
|             0 | Monday       |
|             1 | Tuesday      |
|             2 | Wednesday    |
|             3 | Thursday     |
|             4 | Friday       |
|             5 | Saturday     |
|             6 | Sunday       |
+---------------+--------------+

Here we can also use the second syntax to rewrite the above statement:

SELECT
    weekday_index AS `Weekday Index`,
    CASE
        WHEN weekday_index = 0 THEN 'Monday'
        WHEN weekday_index = 1 THEN 'Tuesday'
        WHEN weekday_index = 2 THEN 'Wednesday'
        WHEN weekday_index = 3 THEN 'Thursday'
        WHEN weekday_index = 4 THEN 'Friday'
        WHEN weekday_index = 5 THEN 'Saturday'
        WHEN weekday_index = 6 THEN 'Sunday'
        ELSE 'Error'
    END AS `Weekday Name`
FROM
    test_case_weekday;

The output is exactly the same as above.

Alternatives for NULL values

MariaDB CASE expressions are complex and difficult to write. When working with NULL values , you can use functions like NULLIF() or COALESCE() instead, which are more concise.

The MariaDB COALESCE() function returns the first non-NULL value, or NULL if there is not a non-NULL value.

MariaDB NULLIF() function returns NULL if the two parameters are the same, otherwise returns the first parameter.

Conclusion

In MariaDB, it CASE is a built-in expression, which is similar to if-elseif-else, and is used for multi-branch situations in flow control.