SQL Essentials Training & Certification
- 11k Enrolled Learners
- Self Paced
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.
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.
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.
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.
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:
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END;
CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END;
Let’s consider a table: STUDENT
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.
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;
The corresponding department names get renamed as follows:
|005||Elicia||Electronics and Communication||F|
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.
SELECT Student ID, Name, Department, CASE Gender WHEN'M' THEN 'Male' WHEN'F' THEN 'Female' END FROM Student;
|005||Elicia||Electronics and Communication||Female|
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;
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’.
SELECT CASE WHEN contact_id < 1000 THEN 'TechOnTheNet.com' 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.