MySQL CASE Statement

MySQL has an alternative of IF statement i.e. MySQL CASE statement by which you can check conditional statement.

Besides the IF statement in MySQL, this provides an alternative conditional statement i.e. called CASE. The MySQL CASE statement makes the programming code more readable and efficient.

You can use the simple CASE statement to check the value of an expression against a set of unique values.

Expression of MySQL CASE:

CASE  case_condition
   WHEN exp_1 THEN statement
   WHEN exp_2 THEN statement
   ...
   ELSE statement
END CASE;

The following example shows how to use the simple CASE statement:

DELIMITER $$
CREATE PROCEDURE GetUserType(
 out user_tag varchar(50))
BEGIN
    DECLARE userCountry varchar(50);
    SELECT country INTO userCountry
  FROM users;
 
 CASE userCountry
 WHEN  'USA' THEN
 SET user_tag = 'US-07';
 WHEN 'UK' THEN
 SET user_tag = 'UK-02';
 ELSE
 SET user_tag = 'NO-95';
 END CASE;
END$$

The simple CASE statement only allows you to match a value of an expression against a set of distinct values.

Read Also: Listing stored procedures in MySQL

In order to perform one or more complex matches such as ranges, you can use the searched CASE statement. The searched CASE statement is equivalent to the IF statement, however, its construct is much more readable.

MySQL checks each condition in the WHEN clause. Until it finds a condition whose value is TRUE, then corresponding commands in the THEN clause will get executed.