MariaDB CASE Operator

In MariaDB, we can use the CASE operator to compare a list of conditions and return a different result depending on which condition (if any) is matched.

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

MariaDB 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 option 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, MariaDB also provides the CASE statement, which is for use within stored programs. The main difference between the CASE statement and the CASE operator 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 @weather = 'Sunny';

SELECT  
    CASE @weather  
        WHEN 'Rain' THEN 'Bring an umbrella'
        WHEN 'Sunny' THEN 'Bring your sunglasses'
        ELSE 'Bring whatever you want!'  
    END;

Result:

Bring your sunglasses

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 (examples below).

Specifically, I checked the value of the @weather 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. So this CASE expression type doesn’t have an input expression like the simple CASE expression.

Example:

SET @price = 75;

SELECT
    CASE   
        WHEN @price > 100 THEN 'Expensive'
        WHEN @price > 50 AND @price < 100 THEN 'Reasonable'
        ELSE 'Cheap'  
    END;

Result:

Reasonable

Here, the second WHEN condition is met, and so we get the output that applies when that condition is true.

Here’s what happens when none of the conditions are met:

SET @price = 25;

SELECT
    CASE   
        WHEN @price > 100 THEN 'Expensive'
        WHEN @price > 50 AND @price < 100 THEN 'Reasonable'
        ELSE 'Cheap'  
    END;

Result:

Cheap

In this case, none of the WHEN conditions are true, and so it resorts to the ELSE clause.

A Database Example

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

SELECT 
  name,
  salary,
  CASE 
     WHEN salary > 100000 THEN 'Fire this employee!'  
     WHEN salary >= 50000 AND salary < 100000 THEN 'Keep this employee' 
     ELSE 'Give this employee a pay rise!'
  END AS required_action
FROM employees;

Result:

name	salary	required_action
------- ------- ------------------------------
Bree	5755000	Fire this employee!
Zohan	150000	Fire this employee!
Jake	25001	Give this employee a pay rise!
Eve	250000	Fire this employee!
Monish	55000	Keep this employee
Briana	45000	Give this employee a pay rise!
Jen	40000	Give this employee a pay rise!
Ken	75000	Keep this employee

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

Anything that fell outside of the WHEN conditions 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:

SELECT 
  name,
  salary,
  CASE 
     WHEN salary > 100000 THEN 'Fire this employee!'  
     WHEN salary >= 50000 AND salary < 100000 THEN 'Keep this employee'
  END AS required_action
FROM employees;

Result:

name	salary	required_action
------- ------- -------------------
Bree	5755000	Fire this employee!
Zohan	150000	Fire this employee!
Jake	25001	NULL
Eve	250000	Fire this employee!
Monish	55000	Keep this employee
Briana	45000	NULL
Jen	40000	NULL
Ken	75000	Keep this employee

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 employees table from the previous example:

ALTER TABLE employees
ADD COLUMN required_action VARCHAR(50) AFTER salary;

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

SELECT * FROM employees;

Result:

empId	name	salary	required_action
------- ------- ------- ---------------
1	Bree	5755000	NULL
2	Zohan	150000	NULL
3	Jake	25001	NULL
4	Eve	250000	NULL
5	Monish	55000	NULL
6	Briana	45000	NULL
7	Jen	40000	NULL
8	Ken	75000	NULL

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

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

UPDATE employees 
SET required_action = 
    CASE 
      WHEN salary > 100000 THEN 'Fire this employee!'  
      WHEN salary >= 50000 AND salary < 100000 THEN 'Keep this employee' 
      ELSE 'Give this employee a pay rise!'
    END;

Now let’s select data from the updated table:

SELECT * FROM employees;

Result:

empId	name	salary	required_action
------- ------- ------- ------------------------------
1	Bree	5755000	Fire this employee!
2	Zohan	150000	Fire this employee!
3	Jake	25001	Give this employee a pay rise!
4	Eve	250000	Fire this employee!
5	Monish	55000	Keep this employee
6	Briana	45000	Give this employee a pay rise!
7	Jen	40000	Give this employee a pay rise!
8	Ken	75000	Keep this employee

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

Alternatives when Dealing with `NULL` Values

MariaDB 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