CASE Statement

CASE Statement

🔹 What is a CASE Statement?

A CASE statement lets you apply conditional logic in SQL queries — kind of like an if-else in programming languages.

Basic Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END
  • WHEN → specifies the condition to check
  • THEN → the value returned if the condition is true
  • ELSE → optional, value returned if none of the conditions are true
  • END → closes the CASE

🔹 Example 1: Simple CASE with Conditions

Suppose we have a table Employees:

EmployeeIDNameSalary
1Alice3000
2Bob6000
3Carol12000
4Dave8000

We want to categorize employees based on their salary.

SELECT 
    Name,
    Salary,
    CASE
        WHEN Salary < 5000 THEN 'Low'
        WHEN Salary BETWEEN 5000 AND 10000 THEN 'Medium'
        ELSE 'High'
    END AS SalaryCategory
FROM Employees;

Result:

NameSalarySalaryCategory
Alice3000Low
Bob6000Medium
Carol12000High
Dave8000Medium

🔹 Example 2: CASE in ORDER BY

We can sort results using custom rules:

SELECT Name, Salary
FROM Employees
ORDER BY 
    CASE
        WHEN Salary < 5000 THEN 1
        WHEN Salary BETWEEN 5000 AND 10000 THEN 2
        ELSE 3
    END;

This will order employees by salary category (Low → Medium → High).

🔹 Example 3: CASE in Aggregation

Count employees in each category:

SELECT 
    CASE
        WHEN Salary < 5000 THEN 'Low'
        WHEN Salary BETWEEN 5000 AND 10000 THEN 'Medium'
        ELSE 'High'
    END AS SalaryCategory,
    COUNT(*) AS NumEmployees
FROM Employees
GROUP BY
    CASE
        WHEN Salary < 5000 THEN 'Low'
        WHEN Salary BETWEEN 5000 AND 10000 THEN 'Medium'
        ELSE 'High'
    END;

✅ Key Points:

  • CASE can be used in SELECT, ORDER BY, WHERE, and GROUP BY.
  • Think of it as SQL’s if-else tool.

Leave a Reply