CASE Expression in MySQL

What is CASE

The CASE expression adds if-else logic inside a query. It checks conditions in order and returns a value for the first true one, otherwise a default. It is perfect for turning raw data into meaningful labels — like converting marks into grades — right in the SELECT.

The CASE syntax

CASE
    WHEN condition1 THEN value1
    WHEN condition2 THEN value2
    ELSE default_value
END

Each WHEN is checked top to bottom; the first true one gives its value. If none match, ELSE is used. You usually place it in the SELECT list with AS to name the column.

Example: assign grades

SELECT name, marks,
    CASE
        WHEN marks >= 90 THEN 'A'
        WHEN marks >= 75 THEN 'B'
        WHEN marks >= 40 THEN 'C'
        ELSE 'Fail'
    END AS grade
FROM students;

Each student's marks are turned into a grade by the CASE, without changing the stored data.

Categorising data

SELECT name, salary,
    CASE
        WHEN salary >= 50000 THEN 'High'
        WHEN salary >= 25000 THEN 'Medium'
        ELSE 'Low'
    END AS pay_band
FROM employees;

CASE is great for grouping continuous values into named bands — salary into High/Medium/Low, age into ranges, and so on.

CASE in ORDER BY

SELECT name, status
FROM orders
ORDER BY
    CASE status
        WHEN 'urgent' THEN 1
        WHEN 'normal' THEN 2
        ELSE 3
    END;

Here CASE gives each status a sort rank, so urgent orders come first, then normal, then others — a custom order you cannot get from plain ASC/DESC.

Simple CASE form

SELECT name,
    CASE grade
        WHEN 'A' THEN 'Excellent'
        WHEN 'B' THEN 'Good'
        ELSE 'Average'
    END AS remark
FROM students;

The simple CASE compares one column to values. It is shorter when you are checking a single column against fixed values, rather than writing full conditions.

Common mistakes

• Forgetting END. Every CASE must finish with END.
• Wrong condition order. Put stricter conditions first, or a looser one catches everything.
• Missing ELSE. Without it, unmatched rows return NULL.
• Expecting CASE to change data. It only affects the query result.

Summary

• CASE adds if-else logic inside a query.
• Syntax: CASE WHEN ... THEN ... ELSE ... END.
• Conditions are checked in order; the first true one wins.
• Use it for grades, categories, and custom ORDER BY.
• It computes values for display, not stored changes.

Next, handle nulls with IFNULL and COALESCE, or build a report in the report card query.