MariaDB CASE: CASE WHEN Syntax and Examples

MariaDB CASE is an SQL expression that evaluates conditions and returns one result.

There are two forms:

simple CASE compares one value against multiple alternatives
searched CASE evaluates independent conditions in order

MariaDB returns the result from the first matching WHEN branch.

Simple CASE Syntax

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

Example:

SELECT CASE status
  WHEN 'N' THEN 'New'
  WHEN 'P' THEN 'Processing'
  WHEN 'D' THEN 'Done'
  ELSE 'Unknown'
END AS status_text
FROM orders;

Searched CASE WHEN Syntax

Use searched CASE when each branch has its own condition:

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

Example:

SELECT
  score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'D'
  END AS grade
FROM exam_results;

Conditions are checked from top to bottom. Once one condition matches, later branches are not processed for the result.

What Happens Without ELSE?

If no WHEN branch matches and ELSE is omitted, the CASE expression returns NULL.

SELECT CASE 3
  WHEN 1 THEN 'one'
  WHEN 2 THEN 'two'
END AS result;

The result is NULL.

CASE in SELECT

CASE is commonly used to create derived values:

SELECT
  name,
  salary,
  CASE
    WHEN salary >= 100000 THEN 'high'
    WHEN salary >= 50000 THEN 'medium'
    ELSE 'low'
  END AS salary_band
FROM employees;

CASE in ORDER BY

You can also use it to implement custom ordering:

SELECT id, priority
FROM tickets
ORDER BY CASE priority
  WHEN 'urgent' THEN 1
  WHEN 'high' THEN 2
  WHEN 'normal' THEN 3
  ELSE 4
END;

CASE and NULL

A simple CASE expression uses equality comparison. This means a branch such as WHEN NULL does not match a SQL NULL value.

Use searched CASE with IS NULL instead:

SELECT CASE
  WHEN value IS NULL THEN 'missing'
  ELSE 'present'
END
FROM t;

CASE Operator vs CASE Statement

The CASE expression described on this page ends with:

END

MariaDB stored programs also support a separate CASE statement, which can execute statement lists and ends with:

END CASE

The two constructs are related but not interchangeable.

CASE vs IF()

For two simple outcomes, IF(condition, value_if_true, value_if_false) can be shorter.

For three or more branches, or when standard SQL portability matters, CASE is usually clearer.

Summary

MariaDB CASE implements conditional logic inside an SQL expression.
Simple CASE compares one expression with multiple values.
Searched CASE evaluates multiple conditions.
The first matching branch wins.
If no branch matches and there is no ELSE, the result is NULL.
The CASE expression is different from the CASE statement used in stored programs.

See MariaDB’s official CASE operator documentation.