The CASE statement evaluates specified conditions and returns results once a condition has been met. There are two versions of the statement. One version uses a single expression that is compared to the values of each case, similar to switch statement in C-like languages and Java. The other version while the other compares the expression at each WHEN clause.
Note: If no ELSE clause is specified, an exception will be thrown if no conditions are met.

The following syntax demonstrates a statement with a single expression: 

CASE <expression>
    WHEN <when_condition> THEN <procedural_statement>;
    [...;]
    ELSE <procedural_statement>;
END CASE

The following syntax demonstrates a statement that compares the expression at each WHEN clause: 

CASE
    WHEN <boolean_expression> THEN <procedural_statement>;
    [...;]
    ELSE <procedural_statement>; 
END CASE

where:

expression
is the expression to be evaluated against when conditions.
when_condition
is a condition that is compared to the expression.
procedural_statement
is the procedural statement that is executed when a condition is met.
boolean_expression
is a boolean expression used to determine whether the specified procedural statement is executed.
ELSE
specifies that if the preceding condition was not met, the specified statement should be executed.

The following examples produce the same results using the different versions of syntax.

Statement with a single expression: 

CASE state
    WHEN 'create', 'insert' THEN INSERT INTO t_one ...;
    WHEN IN ('drop', 'delete', 'remove') THEN DELETE FROM t_one WHERE ...;
    WHEN IS NULL THEN SIGNAL 'HY000' SET MESSAGE = 'This program is befuddled';
    ELSE UPDATE t_one ...;
END CASE

Statement that compares the expression at each WHEN clause: 

CASE
    WHEN state = 'create' OR state = 'insert' THEN INSERT INTO t_one ...;
    WHEN state IN ('drop', 'delete', 'remove') THEN DELETE FROM t_one WHERE ...;
    WHEN state IS NULL THEN SIGNAL 'HY000' SET MESSAGE = 'This program is befuddled';
    ELSE UPDATE t_one ...;
END CASE