MySQL CASE Statement Tutorial [Multiple Programming Examples]

By Sruthy

By Sruthy

Sruthy, with her 10+ years of experience, is a dynamic professional who seamlessly blends her creative soul with technical prowess. With a Technical Degree in Graphics Design and Communications and a Bachelor’s Degree in Electronics and Communication, she brings a unique combination of artistic flair…

Learn about our editorial policies.
Updated January 8, 2025

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

MySQL CASE Statement

MySQL CASE

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.

ENDING CASE

#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.

MySQL CASE With Expression in WHEN Statements

#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.

UPDATE statement

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

Was this helpful?

Thanks for your feedback!

Leave a Comment