Understanding CASE Statements in MySQL: Age Brackets, Salary Raises, and Bonuses

Introduction

In this lesson we explore how the CASE expression adds conditional logic to MySQL SELECT queries, similar to an IF‑ELSE statement in programming languages.

Basic Syntax

CASE
    WHEN condition THEN result
    [WHEN condition THEN result] ...
    [ELSE default_result]
END

• CASE starts the expression.
• One or more WHEN … THEN … clauses define the conditions.
• ELSE (optional) provides a fallback.
• END terminates the expression and can be aliased with AS.

Example 1 – Age Bracket Classification

We use the employee_demographics table to label employees by age.

SELECT first_name,
       last_name,
       age,
       CASE
           WHEN age <= 30 THEN 'Young'
           WHEN age BETWEEN 31 AND 50 THEN 'Old'
           WHEN age >= 50 THEN 'On Death''s Door'
       END AS age_bracket
FROM employee_demographics;

• Employees ≤30 are marked Young (only April qualifies).
• Ages 31‑50 receive Old.
• Ages ≥50 receive On Death's Door. The result set shows each employee with a readable age category.

Example 2 – Salary Increases and Bonuses

Scenario: The Pawnee Council issues a pay‑increase memo. - Salary < 50 000 → 5 % raise. - Salary ≥ 50 000 → 7 % raise. - Finance department (dept_id = 6) → additional 10 % bonus.

Step‑by‑step query

SELECT first_name,
       last_name,
       salary,
       CASE
           WHEN salary < 50000 THEN salary * 1.05
           WHEN salary >= 50000 THEN salary * 1.07
       END AS new_salary,
       CASE
           WHEN dept_id = 6 THEN salary * 0.10
           ELSE 0
       END AS bonus
FROM employee_salary;

• Employees under 50 k (e.g., April Ludgate) see a new salary of 26 250.
• Employees over 50 k receive a 7 % increase.
• Tom Haverford and Jerry Gergich, whose salary is exactly 50 k, fall outside both brackets and receive no raise.
• Ben Wyatt, the sole finance employee (dept_id = 6), gets a $7 000 bonus.

Key Points about CASE

• Multiple WHEN clauses allow fine‑grained categorisation.
• You can perform arithmetic inside the THEN part (e.g., salary * 0.05).
• ELSE is optional; if omitted, rows that match no condition return NULL.
• Alias the result with AS for readability.
• CASE can be used for both labeling (text) and calculations (numeric).

Next Steps

The next lesson will cover sub‑queries in MySQL, building on the conditional logic demonstrated here.

CASE statements let you embed conditional logic directly in SELECT queries, enabling dynamic labeling and calculations such as age categories, salary raises, and department‑specific bonuses—all without writing separate procedural code.