MySQL CASE Statement with Examples


How to Use the MySQL CASE Statement

In this tutorial, you’ll learn how to use the MySQL CASE statementCASE is a MySQL flow control statement that allows you to execute a block of SQL code when a condition is TRUE and execute another block of SQL code when the condition evaluates to FALSE.

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 illustrates an example of using the 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, which finds the maximum number in a set of 3 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 input a = 10, b = 20, c = 18, the stored procedure returns the value like this:

20 is the maximum value.

The above example illustrated how to find the largest number using the CASE statement. However, you can also use the MySQL IF statement to archive the same result.

Example 3: Find the smallest number

The following SQL creates a stored procedure named SP_CASE_FIND_MIN, which finds the minimum number in 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 input a = 20, b = 10, c = 30, then the stored procedure returns the value like this:

10 is the maximum value.

In this tutorial, you have learned how to use the MySQL CASE statement. In MySQL, CASE is a flow control statement to execute one block of SQL code when a condition is TRUE and execute the different block of SQL code when the condition evaluates to FALSE.

Leave a Reply

Your email address will not be published. Required fields are marked *