MySQL CASE: Return Values Based on Matches or Conditions

MySQL CASE returns a value based on either an exact match or a logical condition. It is commonly used inside SELECT, ORDER BY, aggregate expressions, and other SQL expressions.

SELECT id,
       name,
       mark,
       CASE
           WHEN mark >= 90 THEN 'A'
           WHEN mark >= 80 THEN 'B'
           WHEN mark >= 70 THEN 'C'
           ELSE 'FAIL'
       END AS grade
FROM student;

Simple CASE: Match a Value Top ↑

Simple CASE evaluates one expression and compares it with each WHEN value.

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

Searched CASE: Test Conditions Top ↑

Searched CASE does not compare one expression with fixed values. Each WHEN contains its own condition.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

This form is appropriate for ranges, inequalities, NULL checks, and combinations of conditions.

CASE Stops at the First Matching WHEN Top ↑

CASE checks WHEN branches from top to bottom and returns the result from the first branch whose value or condition matches.

CASE
    WHEN mark >= 90 THEN 'A'
    WHEN mark >= 80 THEN 'B'
    WHEN mark >= 70 THEN 'C'
    ELSE 'FAIL'
END

A mark of 94 satisfies all three numeric comparisons, but the first true condition is mark >= 90, so the result is A.

Example: Assign a Location by Class Top ↑

Use simple CASE when one column is being matched against several exact values:

SELECT id,
       name,
       class,
       mark,
       gender,
       CASE class
           WHEN 'Four' THEN '1st floor'
           WHEN 'Five' THEN '2nd floor'
           WHEN 'Three' THEN '2nd floor'
           WHEN 'Two' THEN '1st floor'
           ELSE 'Ground floor'
       END AS location
FROM student
ORDER BY id;

Example: Assign Grades by Mark Top ↑

Use searched CASE for numeric ranges:

SELECT id,
       name,
       class,
       mark,
       gender,
       CASE
           WHEN mark >= 90 THEN 'A'
           WHEN mark >= 80 THEN 'B'
           WHEN mark >= 70 THEN 'C'
           ELSE 'FAIL'
       END AS grade
FROM student
ORDER BY id;

CASE with BETWEEN Top ↑

BETWEEN is inclusive at both ends, so non-overlapping ranges must be chosen carefully.

SELECT id,
       name,
       mark,
       CASE
           WHEN mark BETWEEN 90 AND 100 THEN 'A'
           WHEN mark BETWEEN 80 AND 89 THEN 'B'
           WHEN mark BETWEEN 70 AND 79 THEN 'C'
           ELSE 'FAIL'
       END AS grade
FROM student;

For integer marks this is clear. For decimal scores, threshold-style conditions such as mark >= 90, mark >= 80, and mark >= 70 often avoid gaps such as 89.5.

CASE with GROUP BY and SUM Top ↑

CASE is frequently used for conditional aggregation. The following query counts male and female students inside each class:

SELECT class,
       COUNT(*) AS total,
       SUM(
           CASE
               WHEN gender = 'male' THEN 1
               ELSE 0
           END
       ) AS male,
       SUM(
           CASE
               WHEN gender = 'female' THEN 1
               ELSE 0
           END
       ) AS female
FROM student
GROUP BY class
ORDER BY class;

This pattern converts each matching row into 1 and each non-matching row into 0, then SUM() adds those values within each GROUP BY group.

CASE with NULL Values Top ↑

Use IS NULL or IS NOT NULL inside a searched CASE.

SELECT id,
       CASE
           WHEN c_name IS NOT NULL THEN 'checked'
           ELSE 'not_checked'
       END AS my_status
FROM student;

Do not test NULL with = NULL or <> NULL. See SQL NULL values.

Why simple CASE does not match NULL with WHEN NULL Top ↑

This is not a reliable NULL test:

-- Do not use this to test NULL
CASE c_name
    WHEN NULL THEN 'not_checked'
    ELSE 'checked'
END

Use searched CASE with WHEN c_name IS NULL instead, because ordinary equality comparison with NULL does not evaluate to true.

ORDER BY a CASE Result Top ↑

A CASE expression can create a display value and the query can sort by its alias:

SELECT id,
       name,
       class,
       CASE class
           WHEN 'Four' THEN '1st floor'
           WHEN 'Five' THEN '2nd floor'
           WHEN 'Three' THEN '2nd floor'
           ELSE 'Ground floor'
       END AS location
FROM student
ORDER BY location, id;

See ORDER BY for deterministic sorting and tie-breakers.

Custom sort order with CASE Top ↑

If alphabetical order is not the desired business order, CASE can return numeric sort ranks:

SELECT id,
       name,
       class
FROM student
ORDER BY
    CASE class
        WHEN 'Three' THEN 1
        WHEN 'Four' THEN 2
        WHEN 'Five' THEN 3
        ELSE 4
    END,
    id;

What Happens without ELSE? Top ↑

ELSE is optional. If no WHEN branch matches and no ELSE is present, the CASE expression returns NULL.

SELECT id,
       mark,
       CASE
           WHEN mark >= 90 THEN 'A'
       END AS grade
FROM student;

Rows below 90 receive NULL in the grade result column.

Result Datatypes Top ↑

CASE can return numbers, strings, dates, or other expressions. MySQL determines a common result type from the possible branches.

Keep branch results semantically consistent when possible:

CASE
    WHEN mark >= 50 THEN 'Pass'
    ELSE 'Fail'
END

is clearer than mixing unrelated numeric and text meanings in the same CASE result.

CASE vs WHERE Top ↑

CASE usually returns a value; WHERE usually removes rows that do not satisfy a condition.

To label students:

SELECT name,
       CASE
           WHEN mark >= 70 THEN 'High'
           ELSE 'Other'
       END AS mark_group
FROM student;

To return only students with marks of 70 or more:

SELECT id, name, mark
FROM student
WHERE mark >= 70;

CASE vs IF() Top ↑

MySQL also provides the IF() function for a simple true/false choice:

SELECT id,
       name,
       IF(
           mark >= 50,
           'Pass',
           'Fail'
       ) AS result
FROM student;

CASE is usually clearer when several branches are required and is standard SQL syntax supported more broadly across database systems.

Common CASE Mistakes Top ↑

Writing END CASE inside a SELECT expression Top ↑

A CASE expression in SELECT ends with END. END CASE belongs to a different stored-program syntax.

Placing a broad condition before a narrow one Top ↑

WHEN mark >= 70 before WHEN mark >= 90 would classify 94 as the first matching branch. Put higher thresholds first.

Using BETWEEN ranges with gaps or overlaps Top ↑

BETWEEN includes both boundaries. Integer ranges such as 80-89 are fine for integer marks, but decimal values need carefully defined boundaries.

Comparing NULL with = NULL Top ↑

Use IS NULL or IS NOT NULL in a searched CASE.

Forgetting ELSE Top ↑

If no WHEN matches and ELSE is omitted, CASE returns NULL. That may be correct, but it should be intentional.

Using CASE when WHERE is the real requirement Top ↑

CASE labels or calculates values. WHERE filters rows. Choose the construct that expresses the actual intent.

Ignoring inconsistent source values Top ↑

A simple CASE comparing 'Five' will not match 'Fifth'. CASE should not be used to hide avoidable data-quality inconsistencies.

Video Tutorial Top ↑

Related Tutorials and SQL Dump Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: