Understanding the CASE Statement in SQL (With Real Examples)

“Smart queries make smart data insights — and the CASE statement is your Swiss Army knife in SQL.”

What is the CASE Statement in SQL?

The CASE statement in SQL is used to apply conditional logic inside queries. It’s similar to if-else in programming and allows you to return different values based on conditions.

You can use it in:

• SELECT queries
• UPDATE statements
• ORDER BY or GROUP BY
• Even inside aggregate functions
  
  

Syntax of CASE

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

Example 1: Simple CASE in SELECT

Let’s classify employees based on their salary:

SELECT 
    emp_id, 
    emp_name, 
    salary,
    CASE 
        WHEN salary > 50000 THEN 'High'
        WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

Example 2: CASE in UPDATE Statement

Update bonus based on department:

UPDATE employees
SET bonus = 
    CASE 
        WHEN department = 'HR' THEN 1000
        WHEN department = 'IT' THEN 2000
        ELSE 500
    END;

Example 3: Using CASE in Aggregates

Count male and female employees in each department:

SELECT 
    department,
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department;

Conclusion

The CASE statement is one of the most powerful tools in SQL. Whether you’re building dashboards, cleaning data, or just querying smarter — CASE makes your SQL queries dynamic, clear, and professional.🙂🙂