Case When Statement in MySQL

Yusuf SEZER

5 years ago

The article contains information about CASE WHEN Statement in MySQL as an alternative to the if condition.

With the CASE statement used in MySQL as an alternative to the if function and if statement, desired commands can be executed according to a condition or value.

MYSQL Case When Statement

MySQL CASE WHEN statement is used to control situations where a value can take different values. It is also similar to the MySQL IF statement I mentioned in the MySQL IF Statement and MySQL IF() Function article.

CASE CONDITION_VALUE
WHEN CASE-1 THEN
-- commands- 1
WHEN CASE-2 THEN
--  command- 2
ELSE
-- command- N
END CASE;

CASE CONDITION_VALUE

WHEN CASE-1 THEN

-- commands- 1

WHEN CASE-2 THEN

-- command- 2

ELSE

-- command- N

END CASE;

Let’s perform the operations in article MySQL IF Statement and MySQL IF() Function using CASE WHEN.

SELECT (CASE 5 > 6
WHEN FALSE THEN "6 is bigger"
WHEN TRUE THEN "5 is bigger"
END) AS result;

SELECT (CASE 5 > 6

WHEN FALSE THEN "6 is bigger"

WHEN TRUE THEN "5 is bigger"

END) AS result;

Let’s use MySQL CASE WHEN statement according to the id column in the products table.

SELECT *, (CASE id
WHEN 1 THEN "Computers"
WHEN 2 THEN "Mobile Devices"
WHEN 3 THEN "Elektronics"
ELSE "No Category"
END) AS resut FROM products;

SELECT *, (CASE id

WHEN 1 THEN "Computers"

WHEN 2 THEN "Mobile Devices"

WHEN 3 THEN "Elektronics"

ELSE "No Category"

END) AS resut FROM products;

In the example, if the id value is 1, the result will be Computer, if it is 2, the result will be Mobile Devices, if it is 3, result will be Elektronics, and if it is another expression, it will return No Category.

Using CASE WHEN Statement in MySQL Stored Procedures

DELIMITER //
CREATE PROCEDURE FindNameSurname(IN mynumber INT, OUT myresult VARCHAR(50))
BEGIN

CASE mynumber
WHEN 1 THEN
  SET myresult = 'Yusuf';
WHEN 2 THEN
  SET myresult = 'SEZER';
WHEN 3 THEN
  SET myresult = 'Yusuf SEZER';
ELSE
  SET myresult = 'Yusuf Sefa SEZER';
END CASE;

END//
DELIMITER ;

DELIMITER //

CREATE PROCEDURE FindNameSurname(IN mynumber INT, OUT myresult VARCHAR(50))

BEGIN

CASE mynumber

WHEN 1 THEN

SET myresult = 'Yusuf';

WHEN 2 THEN

SET myresult = 'SEZER';

WHEN 3 THEN

SET myresult = 'Yusuf SEZER';

ELSE

SET myresult = 'Yusuf Sefa SEZER';

END CASE;

END//

DELIMITER ;

Lets execute the sp;

SET @result= 0;
CALL FindNameSurname(3, @result);
SELECT @result;

SET @result= 0;

CALL FindNameSurname(3, @result);

SELECT @result;

When the stored procedure is executed, the value Yusuf Sefa SEZER will be assigned to the @result .

The MySQL CASE WHEN statement also can be used as follows;

SELECT *, (CASE
WHEN id = 1 THEN "Computers" 
WHEN kat_id = 2 THEN "Mobil Devices"
WHEN kat_id = 3 THEN "Elektronics"
ELSE "No Category"
END) AS result FROM prodcuts;

SELECT *, (CASE

WHEN id = 1 THEN "Computers"

WHEN kat_id = 2 THEN "Mobil Devices"

WHEN kat_id = 3 THEN "Elektronics"

ELSE "No Category"

END) AS result FROM prodcuts;

Have a good day.