CASE Statement in MySQL

The CASE statement in MySQL is used for conditional logic inside queries, similar to IF-ELSE statements in programming.

1. Simple CASE (Exact Match)

• Compares a column to specific values.
• Works like a switch-case in programming.

Syntax

CASE column_name    WHEN value1 THEN result1    WHEN value2 THEN result2    ELSE default_resultEND

Example: Assign job titles based on department

SELECT employee_name, department,    CASE department        WHEN 'IT' THEN 'Software Engineer'        WHEN 'HR' THEN 'HR Manager'        WHEN 'Finance' THEN 'Accountant'        ELSE 'General Staff'    END AS job_titleFROM employees;

✅ Output:

+---------------+-----------+-------------------+| employee_name | department | job_title       |+---------------+-----------+-------------------+| Alice        | IT        | Software Engineer || Bob         | HR        | HR Manager        || Charlie     | Finance   | Accountant        || Dave        | Sales     | General Staff     |+---------------+-----------+-------------------+

2. Searched CASE (Custom Conditions)

• Allows using comparisons (=, >, <, LIKE) inside CASE.
• More flexible than Simple CASE.

Syntax

CASE     WHEN condition1 THEN result1    WHEN condition2 THEN result2    ELSE default_resultEND

Example: Categorize employees based on salary

SELECT employee_name, salary,    CASE         WHEN salary > 80000 THEN 'High'        WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'        ELSE 'Low'    END AS salary_categoryFROM employees;

✅ Output:

+---------------+--------+----------------+| employee_name | salary | salary_category |+---------------+--------+----------------+| Alice        | 90000  | High           || Bob         | 70000  | Medium         || Charlie     | 40000  | Low            |+---------------+--------+----------------+

3. CASE in ORDER BY

Use CASE to customize sorting.

Example: Prioritize 'High' salaries first, then 'Medium', then 'Low'

SELECT employee_name, salaryFROM employeesORDER BY     CASE         WHEN salary > 80000 THEN 1        WHEN salary BETWEEN 50000 AND 80000 THEN 2        ELSE 3    END;

4. CASE in GROUP BY

Use CASE inside aggregations.

Example: Count employees by salary categor

SELECT     CASE         WHEN salary > 80000 THEN 'High'        WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'        ELSE 'Low'    END AS salary_category,    COUNT(*) AS total_employeesFROM employeesGROUP BY salary_category;

✅ Output:

+----------------+----------------+| salary_category | total_employees |+----------------+----------------+| High          | 5              || Medium        | 8              || Low          | 3              |+----------------+----------------+

5. CASE in UPDATE

Use CASE to update values based on conditions.

Example: Increase salary based on current salary range

UPDATE employeesSET salary = CASE     WHEN salary < 50000 THEN salary * 1.10  -- Increase by 10%    WHEN salary BETWEEN 50000 AND 80000 THEN salary * 1.05  -- Increase by 5%    ELSE salary * 1.02  -- Increase by 2%END;

Key Points

✅ CASE works in SELECT, UPDATE, ORDER BY, GROUP BY.
✅ Simple CASE: Matches exact values (like switch).
✅ Searched CASE: Uses conditions (>, <, BETWEEN).
✅ Great for custom sorting, categorization, and bulk updates.