CASE OPERATOR

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

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

The first CASE syntax returns the result for the first
value=compare_value comparison that is true. The second syntax returns
the result for the first condition that is true. If no comparison or
condition is true, the result after ELSE is returned, or NULL if there
is no ELSE part.

*Note*:

The syntax of the CASE expr described here differs slightly from that
of the SQL CASE statement described in [HELP CASE statement], for use
inside stored programs. The CASE statement cannot have an ELSE NULL
clause, and it is terminated with END CASE instead of END.

The return type of a CASE expression result is the aggregated type of
all result values:

o If all types are numeric, the aggregated type is also numeric:

o If at least one argument is double precision, the result is double
precision.

o Otherwise, if at least one argument is DECIMAL, the result is
DECIMAL.

o Otherwise, the result is an integer type (with one exception):

o If all integer types are all signed or all unsigned, the result
is the same sign and the precision is the highest of all
specified integer types (that is, TINYINT, SMALLINT, MEDIUMINT,
INT, or BIGINT).

o If there is a combination of signed and unsigned integer types,
the result is signed and the precision may be higher. For
example, if the types are signed INT and unsigned INT, the result
is signed BIGINT.

o The exception is unsigned BIGINT combined with any signed integer
type. The result is DECIMAL with sufficient precision and scale
0.

o If all types are BIT, the result is BIT. Otherwise, BIT arguments are
treated similar to BIGINT.

o If all types are YEAR, the result is YEAR. Otherwise, YEAR arguments
are treated similar to INT.

o If all types are character string (CHAR or VARCHAR), the result is
VARCHAR with maximum length determined by the longest character
length of the operands.

o If all types are character or binary string, the result is VARBINARY.

o SET and ENUM are treated similar to VARCHAR; the result is VARCHAR.

o If all types are JSON, the result is JSON.

o If all types are temporal, the result is temporal:

o If all temporal types are DATE, TIME, or TIMESTAMP, the result is
DATE, TIME, or TIMESTAMP, respectively.

o Otherwise, for a mix of temporal types, the result is DATETIME.

o If all types are GEOMETRY, the result is GEOMETRY.

o If any type is BLOB, the result is BLOB.

o For all other type combinations, the result is VARCHAR.

o Literal NULL operands are ignored for type aggregation.