CASE Statement in MySQL
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.
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
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.
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;
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.
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.’
SELECT CASE WHEN member_id < 1000 THEN 'gangboard.com' 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.