How to Use the MySQL CASE Statement
In this tutorial, you will learn how to use the MySQL CASE statement, a flow control statement in MySQL that allows you to execute a block of SQL code when one condition is TRUE and another block of SQL code when that condition evaluates to FALSE.
Learn more: MySQL LOOP
MySQL CASE Syntax
The syntax of the CASE statement is as follows:
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]
...
[ELSE statement_list]
END CASE
or:
CASE
WHEN condition THEN statement_list
[WHEN condition THEN statement_list]
...
[ELSE statement_list]
END CASE
Examples
Example 1: Return a message based on the input value
The following SQL shows an example of using the MySQL CASE statement in a stored procedure:
CREATE PROCEDURE SP_CASE(a INT)
BEGIN
DECLARE message VARCHAR(100) DEFAULT "";
CASE a
WHEN 1 THEN
SET message = "Hello!";
WHEN 2 THEN
SET message = "Welcome!";
ELSE
SET message = "Thank you!";
END CASE;
SELECT message AS "Message";
END;
Output:
When a = 1, the stored procedure returns:
Hello!
When a = 2, the stored procedure returns:
Welcome!
When a != 1 and a != 2, the stored procedure returns:
Thank you!
Example 2: Find the largest number
The following SQL creates a stored procedure named SP_CASE_FIND_MAX that finds the largest number among a set of three numbers:
CREATE PROCEDURE SP_CASE_FIND_MAX(a INT, b INT, c INT)
BEGIN
DECLARE max INT DEFAULT 0;
DECLARE message VARCHAR(100) DEFAULT "";
CASE
WHEN b < a && c < a THEN
SET max = a;
WHEN b >= a || c >= a THEN
SET max = a;
CASE
WHEN c >= b THEN
SET max = c;
WHEN b > c THEN
SET max = b;
END CASE;
END CASE;
SET message = CONCAT(max, " is the maximum value.");
SELECT message AS "Message";
END;
Output:
For example, if you enter a=10, b=20, c=18, the stored procedure will return the following output:
20 is the maximum value.
In the example above, I have shown how to find the largest number using the CASE statement, but you can also use the MySQL IF statement to get the same result.
Example 3: Find the smallest number
The following SQL creates a stored procedure named SP_CASE_FIND_MIN that finds the smallest number among a set of three numbers:
CREATE PROCEDURE SP_CASE_FIND_MIN(a INT, b INT, c INT)
BEGIN
DECLARE min INT DEFAULT 0;
DECLARE message VARCHAR(100) DEFAULT "";
CASE
WHEN b > a && c > a THEN
SET min = a;
WHEN b <= a || c <= a THEN
SET min = a;
CASE
WHEN c <= b THEN
SET min = c;
WHEN b < c THEN
SET min = b;
END CASE;
END CASE;
SET message = CONCAT(min, " is the maximum value.");
SELECT message AS "Message";
END;
Output:
For example, if you enter a = 20, b = 10, c = 30, the stored procedure returns a value as follows:
10 is the maximum value.
Summary
In this tutorial, you have learned how to use the CASE statement in MySQL, a flow control statement that executes one block of SQL code when the condition is TRUE and another block of SQL code when the condition evaluates to FALSE.