There is plenty of data getting generated every day, and therefore it’s significant to retrieve data depending on some conditions. In this tutorial, we will learn about the CASE statement in MySQL in depth.

SQL | An Overview

SQL is termed as the domain-specific language used in programming extensively. It’s developed for controlling the data stored in an RDBMS(Relational Database Management Systems) or for the relational data stream management system stream processing.

Why SQL?

SQL is extensively used for communicating the data with the database. The American National Standards Institute states that SQL is termed as the standard language for the RDBMS. The developers make use of the SQL statements to perform different tasks like insert, update, retrieve, or data from the database.

We have learned the basics of SQL, not let’s discuss the CASE Statement in MYSQL.

What do you mean by the CASE Statement in MySQL?

CASE statement in MySQL is considered as the best way to handle the if or else logic. It’s one of the control statements that acts as the cell of programming languages because they are capable of managing the other set of statements and their executions.

The CASE statement in MySQL makes use of the different conditions and then return values when the first requirement is met. Once the condition becomes true, the CASE statement will stop the process of traversing and deliver the result.

In case no requirements are true, then it makes use of the ELSE clause to return the value. In case there is no ELSE clause, and no conditions are true, then the CASE Statements returns NULL. Let’s check out the syntax of the CASE Statement.

CASE
WHEN conditionA THEN resultA
WHEN conditionB THEN resultB
WHEN conditionC THEN resultC
WHEN conditionD THEN resultD
WHEN conditionz THEN resultZ
END;

There are two important ways to achieve CASE-switch statements. Let’s look at this with an example.

I am taking a variable called num_value, and I’m matching it with a state_list. Let’s check out the syntax.

CASE num_value
When when_value THEN state_list
[WHEN when_value THEN state_list]..
[ELSE state_list]
End;

I am not considering a sch_condition replacing the variable equality and implementing the state_list based on it. Let’s consider the syntax.

CASE
WHEN sch_condition THEN state_list
[WHEN sch_condition THEN state_list
[ELSE State_list]
END;

CASE Statement in MySQL Example:

Let me explain the above syntax with a practical example.

I’m creating a table: Employee

Employee Table

Let’s image a scenario now. We are going to modify the area of expertise names to their full forms. If the area of expertise is SW, it should be converted to Software, NW to Network, Mech to Mechanical, and IT to Information Technology.

Sample Query

CASE  aoe_name
WHEN ‘SW’
THEN UPDATE Employee SET
AOE =’Software’;
WHEN ‘NW’
THEN UPDATE Employee SET
AOE=’Network’;
WHEN ‘Mech’ 
THEN UPDATE Employee SET
AOE=’Mechanical’;
WHEN ‘IT’
ELSE UPDATE Employee SET
AOE=’Information Technology’;
END;

Output:

The Area of Expertise names will be replaced as follows.

Let’s consider another example; you have to choose every field based on the Employee table. We can see every value in the Gender field has a single character, i.e., M/F. Now we are going to write a query that is going to change F to Female and M to Male.

Sample Query

SELECT Employee ID, Name, AOE,
CASE Gender
When ‘F’ THEN ‘Female’
When ‘M’ THEN ‘Male’
END
FROM Employee’

The output of the above query will be

CASE Switch in SQL_Custom Sorting:

Let’s consider a sample query below.

CREATE PROCEDURE GetEmployee(@ColToSort varchar(150)) AS 
SELECT EmployeeID, Name, Gender, AOE
FROM Employee
ORDER BY
CASE WHEN @ColToSort= “Gender’ THEN Gender
WHEN @ColToSort=” AOE’ THEN AOE
WHEN @ColTOSort=’Name’ THEN Name
ELSE EmployeeID
END;

The output of the above sample query will get sorted based on the given fields. The above function takes the arguments as the varchar data type variable. Depending on this, you can sort the tuples in the Employee table. Apart from this, you can also make use of the CASE statement for comparing any conditions. Let’s check this out with an example.

Consider a table named MEMBERS, which holds memberID and websiteID. Based on the details, the user can navigate through this link: ‘gangboard.com’ or ‘besanttechnolgoies.com.’

Sample Query:

SELECT
CASE
&nbsp;&nbsp;WHEN member_id < 1000 THEN 'gangboard.com'
&nbsp;&nbsp;WHEN website_id = 2 THEN 'besanttechnolgoies.com'
END
FROM Members;

One important aspect when writing CASE Statements in MySQL is that its ordering in the right way. You need to just keep in mind that the conditions are processed based on the order that is listed by yourself. Once a condition goes true, the CASE statement will return the value as a result and not make use of those conditions further. So you need to be careful when choosing the order that you give your conditions in.

I hope the above tutorial helped you to know about the CASE Statement in MYSQL. Any other queries related to SQL? Let us know through the comment section below.

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL