This tutorial explains what is MySQL CASE Statement, when to use it, how to use it with update statement, and when statement in programs:
The MySQL CASE statement is a conditional construct and it returns values against a given condition being evaluated as true or false. It’s similar to a nested IF-ELSE construct which is available in a lot of programming languages like Java, C#, etc.
MySQL CASE is generally used when it is desired to evaluate the given column values against given conditions or return a custom value depending on the current column whose values are evaluated against a given condition.
This will be more clear with the examples that would be discussed as a part of this tutorial.
=> Check Here To See A-Z Of MYSQL Training Tutorials
Table of Contents:
MySQL CASE Statement
Test Data
We will be using a test table containing studentMarks with fields – studentId, total_marks, and grade attributes.
-- table creation CREATE TABLE studentMarks (stud_id SMALLINT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, total_marks INT, grade VARCHAR(5)); -- insert sample data INSERT INTO studentMarks(total_marks, grade) VALUES(450, 'A'), (480, 'A+'), (490, 'A++'), (440, 'B+'),(400, 'C+'),(380,'C') ,(250, 'D'),(200,'E'),(100,'F'),(150,'F'),(220, 'E');
MySQL CASE Syntax
CASE Statement can be used in 2 different ways.
#1) CASE comparator provided inline.
CASE case_value WHEN expression THEN statement_list [WHEN expression THEN statement_list] ... [ELSE statement_list] END
This form of the CASE statement is used when we want to compare the expression values in the WHEN statements to equate the case_value specified along with the CASE command.
For example, based on different case values, you can write different WHEN conditions. This is similar to switch-case statements provided by different programming languages like JAVA, C#.
#2) CASE comparator provided with the individual WHEN statements.
CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END
This form of the CASE statement is used when you want to evaluate different complex expressions with the WHEN statements. Here search_condition can range from equality check to complex expressions.
Both the above syntaxes can be used with the CASE function depending on the column value being evaluated against.
It’s important to note here, to mark the end of the CASE statement, and the END CASE should be specified when you are done specifying all the WHEN blocks.
MySQL CASE Statement Examples
#1) With Inline COMPARATOR
In this case, we would use GRADE as an inline value to be switched and compared against.
We would set another column named class depending on the values of grade as below.
A++ – DISTINCTION
A+ – FIRST CLASS
A – SECOND CLASS
B+ – SECOND CLASS
C+ – THIRD CLASS
ALL OTHERS – FAIL
Let’s see how we can use a CASE statement to achieve this.
SELECT total_marks, grade, CASE grade WHEN 'A++' THEN 'DISTINCTION' WHEN 'A+' THEN 'FIRST CLASS' WHEN 'A' THEN 'FIRST CLASS' WHEN 'B' THEN 'SECOND CLASS' WHEN 'B+' THEN 'SECOND CLASS' WHEN 'C+' THEN 'THIRD CLASS' ELSE 'FAIL' END AS class FROM studentMarks
Here you can see that we’ve used ‘grade’ as a comparator along with the CASE keyword and with the individual WHEN statements, we have specified the value of GRADE to be compared against.
After ENDING CASE – we have specified the new column name as a class.
Let’s have a look at the output returned by the above query.
#2) With Expression in WHEN Statements
In this case, we would use CASE without any comparator value and specify the expressions/conditions to be evaluated in WHEN statements.
We would be using the total_marks and depending on the range, the class would be assigned.
- Total_marks > 450 – ‘FIRST CLASS WITH DISTINCTION’
- Total_marks between 400 and 450 – ‘FIRST CLASS’
- Total_marks between 350 and 400 – ‘SECOND CLASS’
- Total_marks between 300 and 350 – ‘THIRD CLASS’
- Else – FAIL
Let’s have a look at the query.
SELECT total_marks, grade, CASE WHEN total_marks >= 450 THEN 'FIRST CLASS WITH DISTINCTION' WHEN total_marks >= 400 AND total_marks < 450 THEN 'FIRST CLASS' WHEN total_marks >= 350 AND total_marks < 400 THEN 'SECOND CLASS' WHEN total_marks >= 300 AND total_marks < 350 THEN 'THIRD CLASS' ELSE 'FAIL' END as class FROM studentMarks
In the above query, we have used the expressions evaluating the given conditions. E.g. checking the value of the total_marks column in a range and then assigning the value to the result column.
#3) With UPDATE Statements
MySQL CASE can also be used while updating an existing column in the table.
Let’s try to understand this with the help of an example with the test data we have.
Suppose, there is a new grading system, which depending on the value of the total_marks column, the grade needs to be derived – Ex
Total_marks >= 450 – Grade ‘A’
Total_marks >=350 AND total_marks<450 – Grade ‘B’
Total_marks >=300 AND total_marks<350 – Grade ‘C’
For all other cases – Grade ‘D’
We can use the below query to achieve such updates without having to write UPDATE queries to have multiple WHERE or IF clauses.
UPDATE studentMarks SET grade = CASE WHEN total_marks >=450 THEN 'A' WHEN total_marks >=350 AND total_marks < 450 THEN 'B' WHEN total_marks >=300 AND total_marks < 350 THEN 'C' ELSE 'D' END
In the above query, we are setting the grade column to a CASE statement which derives its values through different expressions based on the value in the total_marks field.
Let’s look at the table data after the UPDATE statement is executed.
Frequently Asked Questions
Q #1) What is MySQL CASE?
Answer: MySQL provides a CASE Statement that can be used to retrieve data against a column value based on conditions mentioned as a part of the WHEN blocks of the CASE statement.
MySQL CASE can also be used for conditional table updates. For example, in scenarios where you want to update data in an existing column based on some conditions or existing column values, you can assign the column to be updated against the CASE statement which can be evaluated against different conditions and expressions.
Q #2) How do you write a CASE statement in MySQL?
Answer: The CASE statement consists of 2 parts:
- Expression: Conditions to be validated – these are used with WHEN clause.
- Column name: Which would appear in the display result.
CASE can be used in 2 ways – having the comparator column specified after the CASE clause or for scenarios where complex conditions need to be evaluated, the comparator can be skipped and the expressions can be used along with the WHEN clause.
Let’s see an example of using MySQL CASE:
Suppose there’s a table with employee data and we want to SELECT records with a new column with the value assigned based on department name, E.g. if department name is HR & Marketing, then set the value to be SUPPORT, and if the department name is ENGINEERING set the value to CORE.
We can use the below SELECT query to fetch such data.
SELECT emp_name, emp_dept, CASE emp_dept WHEN 'HR' THEN 'SUPPORT' WHEN 'MARKETING' THEN 'SUPPORT' WHEN 'ENGINEERING' THEN 'CORE' END as dept_type FROM employees
In the above query, we have used emp_dept along with CASE which would mean that all WHEN blocks would be evaluated against values of emp_dept column for the current row.
Conclusion
In this tutorial, we learned about CASE Statement in MySQL which is used to evaluate a given condition and set the resultant value to be displayed along with the query results.
CASE is usually used with SELECT commands to fetch the required result set.
We also learned how MySQL CASE can be used along with UPDATE commands to update an existing column in a table depending upon the values of any other existing column.
=> Visit Here To See The MYSQL Training Series For All