MySQL CASE WHEN – Quick Scoop

MySQL’s CASE WHEN is a conditional expression that lets you return different values based on conditions, similar to IF/ELSE or switch in programming.

What is CASE WHEN in MySQL?

MySQL’s CASE lets you embed conditional logic directly inside SQL queries, most commonly in SELECT, WHERE, ORDER BY, and UPDATE clauses.

There are two main forms:

  1. Searched CASE (most common in day‑to‑day work):

    sql

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

Here, each condition is a Boolean expression (like salary > 50000), and MySQL returns the result for the first true condition.

  1. Simple CASE (compares one expression against several values):

    sql

CASE case_value
    WHEN when_value1 THEN result1
    WHEN when_value2 THEN result2
    ...
    ELSE default_result
END

This behaves like “switch on case_value, then match WHEN values.”

Core syntax and how it works

Generic syntax (searched form)

sql

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE default_result
END
  • MySQL evaluates conditions from top to bottom and stops at the first true condition.
  • ELSE is optional; if omitted and no condition is true, the result is NULL.

Example: Categorizing salaries

sql

SELECT
    employee_id,
    employee_name,
    salary,
    CASE
        WHEN salary > 100000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

This query labels each employee as “High”, “Medium”, or “Low” based on their salary band.

Simple vs searched CASE (common confusion)

Many people mix up the two forms and get unexpected results.

Simple CASE – compares equality

sql

CASE course_enrollment_settings.base_price
    WHEN 0 THEN 1
    WHEN 100 THEN 2
    ELSE 6
END

Internally this is like:

pseudo

if base_price = 0 then 1
else if base_price = 100 then 2
else 6

You must not put full conditions (like base_price = 0) in the WHEN part of simple CASE, because MySQL will actually evaluate the condition first and then compare the CASE value to that Boolean result, which is rarely what you intend.

Searched CASE – full conditions

Correct version when you want ranges or complex logic:

sql

CASE
    WHEN base_price = 0 THEN 1
    WHEN base_price > 0 AND base_price <= 100 THEN 2
    WHEN base_price > 100 AND base_price < 201 THEN 3
    ELSE 6
END AS calc_base_price

Here there is no case_value after CASE, so each WHEN is an independent condition.

Practical usage patterns

1. In SELECT for computed labels

sql

SELECT
    order_no,
    price,
    CASE
        WHEN price > 1000 THEN 'HIGH'
        WHEN price BETWEEN 500 AND 1000 THEN 'Medium'
        ELSE 'Low'
    END AS order_priority
FROM order_details;

This is a typical way to bucket numeric values into human‑friendly categories (priority, tiers, risk levels).

2. In UPDATE to change many rows differently

sql

UPDATE table_name
SET uid = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
END;

This lets you update many rows with one statement instead of multiple separate UPDATEs.

3. In ORDER BY for custom sorting

Example: put “High” priority rows first, then “Medium”, then “Low”:

sql

SELECT *
FROM tickets
ORDER BY
    CASE priority
        WHEN 'High' THEN 1
        WHEN 'Medium' THEN 2
        ELSE 3
    END;

This is a neat trick for applying business‑specific ordering instead of pure alphabetical or numeric sorting.

Nested CASE WHEN

You can nest CASE expressions to build more nuanced logic. Example: split “High” salaries into “Very High” vs “High” based on bonus:

sql

SELECT
    employee_id,
    salary,
    bonus,
    CASE
        WHEN salary > 100000 THEN
            CASE
                WHEN bonus > 20000 THEN 'Very High'
                ELSE 'High'
            END
        WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
        ELSE 'Low'
    END AS final_category
FROM employees;

Nested CASEs are useful when you have a hierarchy of rules (like high‑salary employees further broken into sub‑groups).

Typical mistakes and how to avoid them

  1. Mixing simple and searched CASE

    • Mistake:

      sql

CASE base_price
    WHEN base_price = 0 THEN 1
    ...
END

    • Fix: remove the expression after CASE and use full conditions:

      sql

CASE
    WHEN base_price = 0 THEN 1
    ...
END
```[1]

  2. Forgetting that only the first true WHEN runs
    Conditions are checked in order and only the first true condition is used, so you must put more specific conditions before more general ones.

  1. Not handling the default case
    If you don’t write ELSE, unmatched rows get NULL, which can break reports or aggregations.

Mini FAQ and current “forum energy”

  • Developers still heavily use CASE WHEN in MySQL for reporting, A/B-style segmentation, and cleaning data on the fly.
  • Popular Q&A threads from years ago are still referenced today, especially where people tripped over the difference between the two CASE syntaxes.
  • Modern tutorials (2023–2025) continue to feature CASE WHEN prominently, often together with analytics patterns and tools that sit on top of MySQL.

Quick reference (HTML table)

[3][4] [1][4] [7][9] [8][9] [9][4] [2][7]
Use case Example snippet Notes
Searched CASE CASE WHEN x > 10 THEN 'A' ELSE 'B' END Full Boolean conditions; most flexible form.
Simple CASE CASE status WHEN 'A' THEN 'Active' END Compares one expression to several values (like switch).
Labeling in SELECT CASE WHEN salary > 100000 THEN 'High' Human‑friendly categories in result sets.
Bulk UPDATE UPDATE t SET uid = CASE WHEN id = 1 THEN 2952 ... END Single query updates many rows differently.
Custom ORDER BY ORDER BY CASE priority WHEN 'High' THEN 1 ... END Business-specific sort order.
Nested CASE CASE WHEN salary > 100000 THEN CASE WHEN bonus > 20000 THEN ... END Multi‑level categorization.

TL;DR

  • Use searched CASE for conditions (ranges, AND/OR, comparisons).
  • Use simple CASE when comparing one value to many fixed options.
  • Remember: first true WHEN wins, and ELSE acts as your safety net.

Information gathered from public forums or data available on the internet and portrayed here.