SQL CASE Tutorial

Example Table

We will use the following table named employees for our examples:


        CREATE TABLE employees (
            employee_id INT PRIMARY KEY,
            employee_name VARCHAR(50),
            department VARCHAR(50),
            salary DECIMAL(10, 2)
        );

        INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
        (1, 'John Doe', 'HR', 50000.00),
        (2, 'Jane Smith', 'Finance', 60000.00),
        (3, 'Sam Brown', 'IT', 70000.00),
        (4, 'Nancy White', 'HR', 55000.00),
        (5, 'Mike Green', 'Finance', 65000.00);

employee_id	employee_name	department	salary
1	John Doe	HR	50000.00
2	Jane Smith	Finance	60000.00
3	Sam Brown	IT	70000.00
4	Nancy White	HR	55000.00
5	Mike Green	Finance	65000.00

Using SQL CASE

The SQL CASE statement is used to create conditional logic in SQL queries. It allows you to return different values based on certain conditions.

Example 1: Categorizing Salaries


        SELECT employee_name,
               salary,
               CASE
                   WHEN salary < 55000 THEN 'Low'
                   WHEN salary BETWEEN 55000 AND 65000 THEN 'Medium'
                   ELSE 'High'
               END AS salary_category
        FROM employees;

Result:

employee_name	John Doe
salary	50000.00
salary_category	Low
employee_name	Jane Smith
salary	60000.00
salary_category	Medium
employee_name	Sam Brown
salary	70000.00
salary_category	High
employee_name	Nancy White
salary	55000.00
salary_category	Medium
employee_name	Mike Green
salary	65000.00
salary_category	Medium

Example 2: Department Bonus Eligibility


        SELECT employee_name,
               department,
               CASE department
                   WHEN 'HR' THEN 'Eligible for Bonus'
                   WHEN 'Finance' THEN 'Eligible for Bonus'
                   ELSE 'Not Eligible for Bonus'
               END AS bonus_eligibility
        FROM employees;