How to Use CASE WHEN in MySQL: Syntax and Examples

In MySQL, use the CASE WHEN statement to perform conditional logic inside queries. It evaluates conditions and returns values based on which condition is true, similar to an if-else structure. The syntax includes CASE WHEN condition THEN result ELSE default END.

Syntax

The CASE WHEN statement in MySQL lets you check conditions and return values accordingly. It starts with CASE, followed by one or more WHEN condition THEN result pairs, and ends with ELSE default (optional) and END.

• CASE: Begins the conditional statement.
• WHEN condition THEN result: Checks a condition and returns a result if true.
• ELSE result: Optional default result if no conditions match.
• END: Marks the end of the CASE statement.

sql

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

Example

This example shows how to use CASE WHEN to categorize ages into groups in a table called people with a column age. It returns 'Child' if age is less than 13, 'Teen' if age is between 13 and 19, and 'Adult' otherwise.

sql

SELECT name, age,
  CASE
    WHEN age < 13 THEN 'Child'
    WHEN age BETWEEN 13 AND 19 THEN 'Teen'
    ELSE 'Adult'
  END AS age_group
FROM people;

Output

name | age | age_group
-----|-----|----------
Alice| 10  | Child
Bob  | 15  | Teen
Eve  | 25  | Adult

Common Pitfalls

Common mistakes when using CASE WHEN include:

• Forgetting the END keyword, which causes syntax errors.
• Not covering all possible conditions, which may lead to unexpected NULL results if ELSE is missing.
• Using incorrect condition syntax, like missing comparison operators.

Always include END and consider adding ELSE to handle unmatched cases.

sql

/* Wrong: Missing END */
SELECT name, age,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 THEN 'Adult'
  -- Missing END here
FROM people;

/* Correct: Includes END */
SELECT name, age,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 THEN 'Adult'
    ELSE 'Unknown'
  END AS age_group
FROM people;

Quick Reference

Part	Description
CASE	Starts the conditional expression
WHEN condition THEN result	Checks a condition and returns the result if true
ELSE result	Optional default result if no conditions match
END	Ends the CASE expression

Key Takeaways

Use CASE WHEN to add conditional logic inside MySQL queries.

Always end the CASE statement with END to avoid syntax errors.

Include ELSE to handle cases where no conditions match and avoid NULL results.

Conditions in WHEN must be valid expressions returning true or false.

CASE WHEN works like an if-else ladder to return different values based on conditions.