MySQL CASE Expression

The CASE expression in MySQL works like an if-else structure in programming. It evaluates conditions in order and returns a value when the first matching condition is found. It is used inside SELECT, UPDATE, and ORDER BY clauses to apply conditional logic directly in SQL queries.

Two Forms of CASE

1. Simple CASE

Compares a column or expression to specific values.

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

2. Searched CASE

Evaluates conditions (not just equality) — more flexible.

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

Sample Table

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES (1, 'Ravi Kumar', 'IT', 72000);
INSERT INTO employees VALUES (2, 'Sneha Joshi', 'HR', 45000);
INSERT INTO employees VALUES (3, 'Karan Das', 'IT', 88000);
INSERT INTO employees VALUES (4, 'Pooja Nair', 'Finance', 61000);
INSERT INTO employees VALUES (5, 'Amit Roy', 'HR', 34000);

Example: Simple CASE — Label Departments

SELECT emp_name,
       department,
       CASE department
           WHEN 'IT'      THEN 'Technology'
           WHEN 'HR'      THEN 'Human Resources'
           WHEN 'Finance' THEN 'Accounts'
           ELSE 'Other'
       END AS dept_label
FROM employees;

Result:

emp_name    | department | dept_label
------------+------------+------------------
Ravi Kumar  | IT         | Technology
Sneha Joshi | HR         | Human Resources
Karan Das   | IT         | Technology
Pooja Nair  | Finance    | Accounts
Amit Roy    | HR         | Human Resources

Example: Searched CASE — Salary Grade

SELECT emp_name, salary,
       CASE
           WHEN salary >= 80000 THEN 'Grade A'
           WHEN salary >= 60000 THEN 'Grade B'
           WHEN salary >= 40000 THEN 'Grade C'
           ELSE 'Grade D'
       END AS salary_grade
FROM employees;

Result:

emp_name    | salary   | salary_grade
------------+----------+-------------
Ravi Kumar  | 72000.00 | Grade B
Sneha Joshi | 45000.00 | Grade C
Karan Das   | 88000.00 | Grade A
Pooja Nair  | 61000.00 | Grade B
Amit Roy    | 34000.00 | Grade D

CASE in ORDER BY

SELECT emp_name, department
FROM employees
ORDER BY
    CASE department
        WHEN 'IT'      THEN 1
        WHEN 'Finance' THEN 2
        WHEN 'HR'      THEN 3
        ELSE 4
    END;

This sorts departments in a custom order rather than alphabetically.

CASE in UPDATE

UPDATE employees
SET salary = CASE
    WHEN department = 'IT'      THEN salary * 1.10
    WHEN department = 'HR'      THEN salary * 1.05
    WHEN department = 'Finance' THEN salary * 1.08
    ELSE salary
END;

Each department receives a different percentage salary increase in a single query.

CASE with Aggregate Functions

SELECT
    SUM(CASE WHEN department = 'IT' THEN salary ELSE 0 END) AS IT_Total,
    SUM(CASE WHEN department = 'HR' THEN salary ELSE 0 END) AS HR_Total
FROM employees;

This pivots department salary totals into separate columns.

Key Points

• CASE applies conditional logic within SQL queries.
• Simple CASE checks equality; searched CASE evaluates any condition.
• The ELSE clause provides a fallback value if no condition matches.
• CASE can be used in SELECT, ORDER BY, and UPDATE statements.
• Combining CASE with aggregate functions enables pivot-style reports.