MariaDB: CASE Function

This MariaDB tutorial explains how to use the MariaDB CASE function with syntax and examples.

Description

The MariaDB CASE function has the functionality of an IF-THEN-ELSE statement by allowing you to evaluate conditions and return a value when the first condition is met.

Syntax

The syntax for the CASE function in MariaDB is:

CASE expression

   WHEN value_1 THEN result_1
   WHEN value_2 THEN result_2
   ...
   WHEN value_n THEN result_n

   ELSE result

END

OR

CASE

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Parameters or Arguments

expression

The value that you are comparing to the list of values. (ie: value_1, value_2, ... value_n).

value_1 to value_n

Values are evaluated in the order listed. Once a value matches expression, the CASE function will return the corresponding result and not evaluate any further.

condition_1 to condition_n

Values are evaluated in the order listed. Once a condition is found to be true, the CASE function will return the result and not evaluate the conditions any further.

result_1 to result_n

The value returned once a condition is found to be true.

Note

• If no value/condition is found to be TRUE, then the CASE function will return the value in the ELSE clause.
• If the ELSE clause is omitted and no condition is found to be true, then the CASE function will return NULL.

Applies To

The CASE function can be used in the following versions of MariaDB:

• MariaDB 10

Example

Let's look at how to use the CASE function in MariaDB.

You could use the CASE function in a SQL statement where the expression is included.

SELECT site_id, site_name, 
CASE server_name
  WHEN 'MyServer' THEN 'Upgrade Now'
  WHEN 'YourServer' THEN 'Upgrade Later'
  ELSE 'Do Nothing'
END
FROM sites
WHERE site_name in ('TechOnTheNet.com', 'CheckYourMath.com');

Or you could write the SQL statement using the CASE function like this: (omits the expression clause)

SELECT site_id, site_name,
CASE
  WHEN server_name = 'MyServer' THEN 'Upgrade Now'
  WHEN server_name = 'YourServer' THEN 'Upgrade Later'
  ELSE 'Do Nothing'
END
FROM sites
WHERE site_name in ('TechOnTheNet.com', 'CheckYourMath.com');

One thing to note is that the ELSE condition within the CASE function is optional. It could have been omitted. Let's modify our examples with the ELSE condition omitted.

SELECT site_id, site_name, 
CASE server_name
  WHEN 'MyServer' THEN 'Upgrade Now'
  WHEN 'YourServer' THEN 'Upgrade Later'
END
FROM sites
WHERE site_name in ('TechOnTheNet.com', 'CheckYourMath.com');

Or

SELECT site_id, site_name,
CASE
  WHEN server_name = 'MyServer' THEN 'Upgrade Now'
  WHEN server_name = 'YourServer' THEN 'Upgrade Later'
END
FROM sites
WHERE site_name in ('TechOnTheNet.com', 'CheckYourMath.com');

With the ELSE clause omitted, if no condition was found to be true, the CASE function would return NULL.