+91-9142290450

Hajipur, Bihar, 844101

☰

❮

❯

MySQL CASE

← Previous Next →

In MySQL, the CASE statement allows you to perform conditional logic directly in SQL queries. It acts like an IF-ELSE statement in programming, enabling you to return different values based on specified conditions.

CASE is useful when you want to categorize data, assign labels, or create computed columns without modifying the actual table. It works with SELECT, UPDATE, and ORDER BY statements.

Basic Syntax

MySQL supports two types of CASE statements: Simple CASE and Searched CASE.

1. Simple CASE

The simple CASE compares a column or expression to a list of values:

SELECT column_name,
       CASE column_name
           WHEN value1 THEN result1
           WHEN value2 THEN result2
           ELSE result_default
       END AS alias_name
FROM table_name;

column_name is the value being compared.
value1, value2 are possible values.
result1, result2 are returned when the column matches the value.
ELSE defines the value returned when no match is found.
AS alias_name gives a temporary name to the output column.

Example – Simple CASE

SELECT name, score,
       CASE score
           WHEN 100 THEN 'Excellent'
           WHEN 90 THEN 'Very Good'
           WHEN 80 THEN 'Good'
           ELSE 'Needs Improvement'
       END AS Performance
FROM students;

Assigns labels based on the score.
The output column is named Performance.

2. Searched CASE

The searched CASE allows complex conditions using logical expressions:

SELECT name, score,
       CASE
           WHEN score >= 90 THEN 'Excellent'
           WHEN score >= 80 THEN 'Very Good'
           WHEN score >= 70 THEN 'Good'
           ELSE 'Needs Improvement'
       END AS Performance
FROM students;

Each WHEN clause contains a condition, not just a value.
More flexible than simple CASE and suitable for ranges.

Using CASE in SELECT Queries

CASE is most commonly used in SELECT queries to compute new columns:

SELECT name, age,
       CASE
           WHEN age < 18 THEN 'Minor'
           WHEN age BETWEEN 18 AND 25 THEN 'Young Adult'
           ELSE 'Adult'
       END AS AgeGroup
FROM students;

Categorizes students based on age groups.
The column alias AgeGroup improves readability.

Using CASE in ORDER BY

You can use CASE to customize sorting:

SELECT name, score
FROM students
ORDER BY
    CASE
        WHEN score >= 90 THEN 1
        WHEN score >= 80 THEN 2
        ELSE 3
    END;

Sorts students based on performance categories instead of numeric values.
1 comes first, followed by 2, then 3.

Using CASE in UPDATE

CASE can also be used in UPDATE queries to conditionally update records:

UPDATE students
SET grade =
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
    END;

Updates the grade column based on each student’s score.
Eliminates the need for multiple UPDATE statements.

Using CASE with Aggregates

CASE works well with aggregate functions to calculate conditional sums or counts:

SELECT
    SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) AS ExcellentCount,
    SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) AS GoodCount,
    SUM(CASE WHEN score < 80 THEN 1 ELSE 0 END) AS NeedsImprovementCount
FROM students;

Counts the number of students in each performance category.
CASE inside SUM allows conditional aggregation.

Nested CASE Statements

You can nest CASE statements for more complex conditions:

SELECT name, score,
       CASE
           WHEN score >= 90 THEN
               CASE
                   WHEN score = 100 THEN 'Perfect'
                   ELSE 'Excellent'
               END
           WHEN score >= 80 THEN 'Very Good'
           ELSE 'Needs Improvement'
       END AS Performance
FROM students;

Allows sub-categories within main categories.
Useful for grading systems or multi-level classifications.

Important Points

CASE statements are evaluated in order; the first matching WHEN clause is used.
ELSE is optional, but it ensures a value is returned if no conditions match.
CASE can be used with SELECT, UPDATE, ORDER BY, GROUP BY, and aggregate functions.
CASE does not change the underlying data unless used in an UPDATE query.
Using CASE improves query readability, especially with complex logic.

Practical Scenarios

Assigning performance labels to students:

SELECT name, score,
       CASE
           WHEN score >= 90 THEN 'Excellent'
           WHEN score >= 80 THEN 'Very Good'
           ELSE 'Needs Improvement'
       END AS Performance
FROM students;

Categorizing ages:

SELECT name, age,
       CASE
           WHEN age < 18 THEN 'Minor'
           WHEN age BETWEEN 18 AND 25 THEN 'Young Adult'
           ELSE 'Adult'
       END AS AgeGroup
FROM students;

Conditional sorting of employees:

SELECT name, salary
FROM employees
ORDER BY
    CASE
        WHEN salary > 100000 THEN 1
        WHEN salary > 50000 THEN 2
        ELSE 3
    END;

Updating grades based on score:

UPDATE students
SET grade =
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        ELSE 'C'
    END;

Counting categories using aggregates:

SELECT
    SUM(CASE WHEN score >= 90 THEN 1 ELSE 0 END) AS ExcellentCount,
    SUM(CASE WHEN score >= 80 AND score < 90 THEN 1 ELSE 0 END) AS GoodCount
FROM students;

Summary of the Tutorial

The CASE statement allows you to implement conditional logic in SQL queries.
Use simple CASE for direct value comparisons and searched CASE for conditions.
CASE works in SELECT, UPDATE, ORDER BY, and aggregate functions.
It helps categorize data, compute conditional results, and simplify complex queries.
Nested CASE statements allow multi-level classifications.

Mastering CASE enables you to perform advanced conditional transformations directly in MySQL without creating additional columns or tables.

Practice Questions

Select student names and assign Performance labels: 'Excellent' for scores ≥ 90, 'Good' for scores ≥ 80, else 'Needs Improvement'.
Categorize students into AgeGroup: 'Minor' if age < 18, 'Young Adult' if 18–25, else 'Adult'.
Update the grade column for students based on their scores using CASE.
Select employee names and JobLevel: 'High' if salary > 100000, 'Medium' if salary > 50000, else 'Low'.
Count the number of students in each Performance category using SUM with CASE.
Sort students by Performance: 'Excellent' first, 'Good' next, others last.
Select names and assign Status: 'Pass' if score ≥ 50, else 'Fail'.
Create a nested CASE to label scores: 'Perfect' if 100, 'Excellent' if 90–99, 'Good' if 80–89, else 'Needs Improvement'.
Select products and categorize PriceRange: 'Expensive' if price > 5000, 'Moderate' if price > 2000, else 'Budget'.
Select employees and assign ExperienceLevel: 'Senior' if years > 10, 'Mid' if 5–10, else 'Junior'.

← Previous Next →

Try a Short Quiz.

Programming Language

Web Development

Other Languages

Programming Language

Web Development

Other Languages

MySQL

MySQL CASE

Basic Syntax

1. Simple CASE

Example – Simple CASE

2. Searched CASE

Using CASE in SELECT Queries

Using CASE in ORDER BY

Using CASE in UPDATE

Using CASE with Aggregates

Nested CASE Statements

Important Points

Practical Scenarios

Summary of the Tutorial

Practice Questions

Try a Short Quiz.

No quizzes available.

MySQL

Quick Links

Our Tutorials

Programming Language

Web Development

Other Languages

Programming Language

Web Development

Other Languages

MySQL

MySQL CASE

Basic Syntax

1. Simple CASE

Example – Simple CASE

2. Searched CASE

Using CASE in SELECT Queries

Using CASE in ORDER BY

Using CASE in UPDATE

Using CASE with Aggregates

Nested CASE Statements

Important Points

Practical Scenarios

Summary of the Tutorial

Practice Questions

Try a Short Quiz.

No quizzes available.

MySQL

Follow Us:

Quick Links

Our Tutorials