How To Use CASE Statement in MySQL?

With a humongous amount of data getting generated every day, it is important to retrieve data based on a few conditions. In this article on the CASE statement in MySQL, I will discuss how to use this statement, to retrieve data on a single condition or multiple conditions.

The following topics will be covered in this article:

• Introduction To SQL
• What is the purpose of SQL?
• CASE in MySQL
• Syntax of CASE in MySQL
• Example of CASE in MySQL

What is SQL?

SQL is a domain-specific language which is extensively used in programming. It is designed for managing data contained in a relational database management system(RDBMS), or for stream processing in a relational data stream management system. I general, SQL is a standard language which helps to store, manipulate and retrieve data in databases.

What is the purpose of SQL?

SQL is basically used to communicate with a database. According to ANSI (American National Standards Institute), it is considered as the standard language for relational database management systems (RDBMS). We use SQL statements to perform tasks such as update, insert, delete or retrieve data from a database.

What is the CASE statement in MySQL?

CASE statement in MySQL is a way of handling the if/else logic. It is a kind of control statement which forms the cell of programming languages as they control the execution of other sets of statements.

The CASE statement goes through various conditions and returns values as and when the first condition is met (like an IF-THEN-ELSE statement in low-level languages). Once a condition is true, it will stop traversing and return the result. If none of the conditions are true, it returns the value in the ELSE clause.

If there is no ELSE clause and none of the conditions are true, it simply returns NULL.

Syntax of CASE statement in MySQL

Basic syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionx THEN resultx
    ELSE result
END;

There can be two ways to achieve CASE-Switch statements:

• Takes a variable called case_value and matches it with some statement_list.

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END;

• Consider a search_condition instead of variable equality and execute the statement_list accordingly. 

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END;

Example of CASE statement in MySQL

Let’s consider a table: STUDENT

Student Table

Example: Consider a scenario, where you have to modify the department names to their full forms.  If the dept name is CS, It should be converted to Computer Science; Likewise, Mech should be converted to Mechanical.and EC to Electronic and Communication.

Sample Query

CASE department_name
WHEN 'CS'
  THEN UPDATE Student SET
  department='Computer Science';
 WHEN 'EC'
  THEN UPDATE Student SET
  department='Electronics and Communication';
 ELSE UPDATE Student SET
 department=’Mechanical';
END;

Output:

The corresponding department names get renamed as follows:

Example: Consider a scenario, where you have to select all the fields corresponding to the Student table. Since the values written in the Gender field are single character values (M/F),  let us write a query to change M to Male and F to Female.

Sample Query

SELECT Student ID, Name, Department,
CASE Gender
 WHEN'M' THEN 'Male'
 WHEN'F' THEN 'Female'
END
FROM Student;

Output:

CASE Switch in SQL- Custom Sorting

Sample Query

CREATE PROCEDURE GetStudent(@ColToSort varchar(150)) AS
SELECT StudentID, Name, Gender, Department
FROM Student
ORDER BY
  CASE WHEN @ColToSort='Department' THEN Department
       WHEN @ColToSort='Name' THEN Name
       WHEN @ColToSort='Gender' THEN Gender
       ElSE StudentID
  END;

Output:

The output gets sorted according to the provided field.

The above procedure (function) takes a variable of varchar data type as its argument, and on that basis, sorts the tuples in the Student table. Moreover, we can also use CASE statements to compare conditions.

Example:  Consider a table named CONTACTS which contains contactID and a websiteID. According to these details, a user can navigate through these links: ‘TechOnTheNet.com’ or ‘CheckYourMath.com’.

Sample Query

SELECT
CASE
&nbsp;&nbsp;WHEN contact_id < 1000 THEN 'TechOnTheNet.com'
&nbsp;&nbsp;WHEN website_id = 2 THEN 'CheckYourMath.com'
END
FROM contacts;

It must be clear by now that while writing Case statements in MySQL, one most important factor is ‘ordering them in the right manner’. Just remember that conditions are evaluated in the order listed by you. Once any condition becomes true, the CASE statement will return the result and not evaluate the conditions any further. So be careful while selecting the order that you list your conditions in.

If you wish to learn more about MySQL and get to know this open-source relational database, check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this “CASE Statement in MySQL” and we will get back to you.