MySQL CASE Statement

Master the MySQL CASE statement — Simple CASE for equality matching and Searched CASE for range conditions. Covers the difference vs the CASE expression in SELECTs, the NULL gotcha, the unmatched-case error, when to use CASE over IF, and best practices for clean conditional logic.

The CASE statement is MySQL's structured branching alternative to chained IF ... ELSEIF ... ELSE. It tests a value or condition against multiple possibilities and runs the matching branch. For more than two or three branches, CASE is usually clearer and more readable than IF.

The CASE statement comes in two forms:

All examples use the same employees table from the IF statement page. Re-run that setup if you don't have it loaded.

Compares one value against fixed alternatives — a structured switch:

How it works:

• case_value is evaluated once.
• Each WHEN when_valueN is tested in order using equality (=).
• The first match runs its statements; the rest are skipped.
• If nothing matches and there's no ELSE, MySQL raises error 1339: Case not found for CASE statement.

Map the numeric department_id to a friendly department name. Departments are equality matches — perfect fit for simple CASE:

DROP PROCEDURE IF EXISTS dept_name;

DELIMITER $$

CREATE PROCEDURE dept_name(IN  emp_id INT,
                           OUT dept   VARCHAR(20))
BEGIN
    DECLARE dept_id INT;

    SELECT department_id INTO dept_id
    FROM   employees
    WHERE  employee_id = emp_id;

    CASE dept_id
        WHEN  6 THEN SET dept = 'Engineering';
        WHEN  9 THEN SET dept = 'Executive';
        WHEN 10 THEN SET dept = 'Finance';
        ELSE         SET dept = 'Unknown';
    END CASE;
END$$

DELIMITER ;

CALL dept_name(103, @d);     -- Alexander Hunold, dept 6
SELECT @d;

Each branch has its own boolean condition — much more flexible than equality matching. This form is a direct replacement for chained IF/ELSEIF/ELSE:

Note there's no value after CASE — the conditions are full boolean expressions like sal_range >= 17000 or name LIKE 'A%'. Each condition is tested in order until one is true.

Salary tiers are ranges, not equality — so this calls for the searched form:

DROP PROCEDURE IF EXISTS emplevel;

DELIMITER $$

CREATE PROCEDURE emplevel(IN  emp_id INT,
                          OUT total  VARCHAR(20))
BEGIN
    DECLARE sal_range DECIMAL(10, 2);

    SELECT salary INTO sal_range
    FROM   employees
    WHERE  employee_id = emp_id;

    CASE
        WHEN sal_range >= 24000 THEN SET total = 'High';
        WHEN sal_range >= 17000 THEN SET total = 'Moderate';
        WHEN sal_range >= 10000 THEN SET total = 'Average';
        ELSE                         SET total = 'Low';
    END CASE;
END$$

DELIMITER ;

CALL emplevel(101, @level);    -- Neena Kochhar, salary 17000
SELECT @level;

17,000 fails the first >= 24000, matches the second >= 17000, so we get 'Moderate'.

When in doubt, searched CASE is more general — anything simple CASE can do, searched CASE can also do (with a slightly more verbose WHEN x = 1 THEN ...). The simple form is just sugar for the equality case.

You can express the same logic with either CASE or chained IF/ELSEIF. Stylistic guidance:

• Two branches: Use IF ... THEN ... ELSE. CASE is overkill.
• Three or more branches: Use CASE. It's flatter, easier to scan, and harder to get wrong.
• Pure equality matching: Use simple CASE for clarity.
• Mixed types of conditions: Use searched CASE.

1. Always include an ELSE branch — even an empty ELSE BEGIN END; — to prevent error 1339 on unmatched input.
2. Order branches from most-specific to least-specific in searched CASE. First match wins.
3. Don't use simple CASE for NULL handling — equality with NULL is always unknown. Use searched CASE with IS NULL.
4. Each WHEN body must contain at least one statement. An empty branch is a syntax error; use BEGIN END; if you really want to do nothing.
5. End every branch's statement with a semicolon — and don't forget the END CASE; at the end.

• The CASE statement branches based on a value or condition inside MySQL stored programs. (Don't confuse it with the CASE expression used in SELECTs.)
• Simple CASE compares a value against fixed alternatives via equality — best for lookup-style mapping.
• Searched CASE evaluates a boolean condition for each branch — best for ranges, mixed conditions, NULL handling.
• First matching branch wins; the rest are skipped. Always include an ELSE to prevent the unmatched-case error.
• For 3+ branches, CASE is usually clearer than chained IF ... ELSEIF ... ELSE.