MySQL - CASE Statement

---

---

MySQL CASE Statement

The case statement in MySQL is used to test a value for equality against a list of values/conditions.

Syntax

Following is the syntax of the CASE statement −

CASE value
   WHEN [compare_value] THEN result
   [WHEN [compare_value] THEN result ...]
   [ELSE result]
END CASE
Or,
CASE
   WHEN [condition] THEN result
   [WHEN [condition] THEN result ...]
   [ELSE result]
END CASE

Example

Following query demonstrates the usage of the MySQL CASE statement in a procedure −

DELIMITER //
CREATE PROCEDURE case_example(IN degree VARCHAR(20), 
OUT full_form Varchar(50))
   BEGIN
      CASE degree WHEN 'B-Tech' THEN
         SET full_form = 'Bachelor of Technology';
         WHEN 'M-Tech' THEN SET full_form = 'Master of Technology';
         WHEN 'BSC' THEN SET full_form = 'Bachelor of Science';
         WHEN 'MSC' THEN
         SET full_form = 'Master of Science';
         ELSE
         SET full_form = 'Irrelevant';
      END CASE ; 
   END //
DELIMITER ;

You can call the above procedure using various input values as follows −

DELIMITER ;
CALL case_example ('B-Tech', @test);
SELECT @test;

Output

Following is the output of the above query −

@test
Bachelor of Technology

Let's call the above procedure by using different input value as follows −

CALL case_example ('M-Tech', @test);
SELECT @test;

Output

The above query produces the following output −

@test
Master of Technology

In the following query, we have passed different input values to call the above procedure −

CALL case_example ('BSC', @test);
SELECT @test;

Output

The above mysql query generates the following output −

@test
Bachelor of Science

Let's call the above created procedure using the different input values shown below −

CALL case_example ('MSC', @test);
SELECT @test;

Output

Following is the output of the above query −

@test
Master of Science

The CASE Operator

MySQL also have a CASE operator which is similar to the CASE statement the only difference is that the ELSE NULL clause is not allowed in the CASE statement and it is terminated by END CASE. Whereas, the operator is terminated by END.

Example

Assume we have created a table named student_info as follows −

CREATE TABLE student_info (ID INT, NAME VARCHAR(25), SAL INT, 
EDUCATION VARCHAR(25));

Now, let's insert some records into the student_info table −

INSERT INTO student_info VALUES 
(101, 'Raju', 2254, 'B-Tech'),
(101, 'Raju', 2254, 'M-Tech'),
(101, 'Raju', 2254, 'BSC'),
(101, 'Raju', 2254, 'MSC');

If you verify the contents of the EMP table you can observe its contents as −

select * from student_info;

Output

The above query generates the following output −

ID	NAME	SAL	EDUCATION
101	Raju	2254	B-Tech
101	Raju	2254	M-Tech
101	Raju	2254	BSC
101	Raju	2254	MSC

Following query displays the contents of the above table along with the full forms of the degrees using the CASE Operator −

SELECT ID, NAME, SAL, EDUCATION,
CASE EDUCATION
   WHEN 'B-Tech' THEN 'Bachelor of Technology' 
   WHEN 'M-Tech' THEN 'Master of Technology' 
   WHEN 'BSC' THEN 'Bachelor of Science'
   WHEN 'MSC' THEN 'Master of Science'
   ELSE 'Irrelevant'
END AS FullFORM FROM student_info;

Output

This will produce the following result −

ID	NAME	SAL	EDUCATION	FullForm
101	Raju	2254	B - Tech	Bachelor of Technology
101	Raju	2254	M - Tech	Master of Technology
101	Raju	2254	BSC	Bachelor of Science
101	Raju	2254	MSC	Master of Science