What are case statements in MySQL
In MySQL, the case statements are used to implement if-else logic. We will understand the use of case statements by creating different types of scenarios, but before going to it first open the MySQL in the terminal of Ubuntu.
Open a particular table in any database and show the list of databases:
Open the “shopping_mart_data” database:
To list down all the tables present in this database.
Open the table Grocery_bill and view its contents:
Now we will understand the use of case statements by applying different cases to this table.
How to use case statements in MySQL
We can use the case statements for conducting any type of survey. Let’s say we want to do a survey that many customers like to eat vegetables. On the basis of shopping for vegetables, we simply make a case that those who buy vegetables should be called vegetarians. For this purpose, we make a case like this:
CASE
WHEN Vegetables > 0 THEN "Customer is a vegetarian"
ELSE "Customer is a non-vegetarian"
END
FROM Grocery_bill;
We will save the code in the file, named file.sql.
Now login to MySQL and open the file.sql using the source command:
In this output, we can see that it has shown the results of customers with the name of customers, the number of vegetables, and with the results of the case created whether they are vegetarian or not.
If we conclude this scenario and create a general syntax of the case which can be used in the scenarios related to the one discussed above then it will be like
CASE
WHEN (condition) THEN (action)
ELSE (action)
END
FROM (TABLE_NAME);
We can represent the denoting values of the tables by using the case statements. For example, we have a column of gender in the table whose values are “m” and “f” denoting male and female gender respectively. To change these letters to the actual names rather than the denotation using case statements, we can execute the following code.
CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END
FROM Grocery_bill;
Modification in a table using case statements
We can also modify the values of the columns of the table by using the case statements. To understand it we will make modifications to the table and will replace the denotations used in the column ‘Gender’ with their actual words meaning “Male” and “Female” instead of “M” and “F”. for this purpose the code will be
SET Gender = CASE Gender
WHEN 'M' THEN 'Male'
WHEN 'F' THEN 'Female'
END;
To see the changes in the table we will display the table by using the command:
The output should display the table with the changes in the Gender column.
CASE statements with comparison operators
We can also use the case statements for comparison purposes using the mathematical operators. To understand this, let’s assume that we want to give discount vouchers to the customers who do shopping for more than 12 items. Let’s write a code for it.
CASE
WHEN Chips + SoftDrinks + Vegetables > 12 THEN "Congrats you won a voucher"
ELSE "Sorry, Try next time"
END
FROM Grocery_bill;
Conclusion
Case statements help us a lot in retrieving the specific results from a table, consisting of a large number of entries, we simply make the case and the results can be sorted on the basis of specified conditions of the case. Though there are some limitations of using the case statements like case statements are not used to check the NULL values of the table and likewise, it is a sequential model means if the case becomes true it will not execute the further conditions and will end the case. Though the case statements provide ease in writing different codes of T-SQL(Transact – SQL) for DML (Data Manipulation Language). In this write-up, we tried to explain the use of case statements by considering different scenarios and applying case statements to them so it will be easy to understand the case statements in MySQL.
