MySQL: CASE Statement

This tutorial explains how to use the CASE statement in MySQL with syntax and examples.

Description

In MySQL, the CASE statement has the functionality of an IF-THEN-ELSE statement and has 2 syntaxes that we will explore.

Syntax

The syntax for the CASE statement in MySQL is:

CASE expression

   WHEN value_1 THEN 
     {...statements to execute when expression equals value_1...}
  [ WHEN value_2 THEN
     {...statements to execute when expression equals value_2...} ]
  [ WHEN value_n THEN result_n
     {...statements to execute when expression equals value_n...} ]

  [ ELSE 
     {...statements to execute when no values matched...} ]

END CASE;

OR

CASE

   WHEN condition_1 THEN 
     {...statements to execute when condition_1 is TRUE...}
 [ WHEN condition_2 THEN
     {...statements to execute when condition_2 is TRUE...} ]
 [ WHEN condition_n THEN
     {...statements to execute when condition_n is TRUE...} ]

 [  ELSE
     {...statements to execute when all conditions were FALSE...} ]

END CASE;

Parameters or Arguments

expression

The value that you are comparing to the list of values. (ie: value_1, value_2, ... value_n).

value_1, value_2, ... value_n

Evaluated in the order listed. Once a value matches expression, the CASE statement will execute the corresponding statements and not evaluate any further.

condition_1, condition2, ... condition_n

Evaluated in the order listed. Once a condition is found to be true, the CASE statement will execute the corresponding statements and not evaluate the conditions any further.

Note

• The CASE statement will execute the ELSE clause if none of the WHEN clauses were executed.

Example

Let's look at an example that shows how to use the CASE statement in MySQL. First, we'll look at the first syntax for the CASE statement. For example:

DELIMITER //

CREATE FUNCTION IncomeLevel ( monthly_value INT )
RETURNS varchar(20)

BEGIN

   DECLARE income_level varchar(20);

   CASE monthly_value
      WHEN 4000 THEN
        SET income_level = 'Low Income';

      WHEN 5000 THEN
        SET income_level = 'Avg Income';

      ELSE
        SET income_level = 'High Income';
   END CASE;

   RETURN income_level;

END; //

DELIMITER ;

In this CASE statement example, we have used the first syntax for the CASE statement. In this example, if monthly_value is equal to 4000, then income_level will be set to 'Low Income'. If monthly_value is equal to 5000, then income_level will be set to 'Avg Income'. Otherwise, income_level will be set to 'High Income'.

Let's look at how to use the CASE statement with the second syntax.

DELIMITER //

CREATE FUNCTION IncomeLevel ( monthly_value INT )
RETURNS varchar(20)

BEGIN

   DECLARE income_level varchar(20);

   CASE
      WHEN monthly_value = 4000 THEN
        SET income_level = 'Low Income';

      WHEN monthly_value > 4000 AND monthly_value = 7000 THEN
        SET income_level = 'Avg Income';

      ELSE
        SET income_level = 'High Income';
   END CASE;

   RETURN income_level;

END; //

DELIMITER ;

In this CASE example, we are using the second syntax for the CASE statement. In this example, if monthly_value is less than or equal to 4000, then income_level will be set to 'Low Income'. If monthly_value is greater than 4000 and less than or equal to 7000, then income_level will be set to 'Avg Income'. Otherwise, income level will be set to 'High Income'.