Blog

SQL CASE Keyword: A Comprehensive Guide

Introduction to SQL CASE Keyword

The CASE keyword in SQL is a powerful conditional expression that allows you to add if-then-else logic to your SQL queries. It’s used to create conditions and return a specific value based on those conditions. This is particularly useful for transforming data, creating new fields in SELECT queries, and applying complex logic without needing to use procedural code.

Basic Structure of CASE

The CASE expression has two forms: simple and searched. Here’s the general syntax for both forms:

Simple CASE

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE resultN
END

Searched CASE

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

Example Scenarios

Let’s go through several examples to understand how the CASE keyword works.

Scenario 1: Simple CASE

Example: Categorizing Employees Based on Department

Let’s assume we have an employees table with the following structure:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50)
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'HR'),
(2, 'Bob', 'Finance'),
(3, 'Charlie', 'IT'),
(4, 'David', 'Marketing'),
(5, 'Eva', 'HR');

Query: Categorize Employees into Different Departments

SELECT 
    name,
    department,
    CASE department
        WHEN 'HR' THEN 'Human Resources'
        WHEN 'Finance' THEN 'Finance Department'
        WHEN 'IT' THEN 'Information Technology'
        ELSE 'Other Department'
    END AS department_full_name
FROM employees;

Explanation:

• We use CASE to transform department abbreviations into full department names.
• WHEN specifies the condition we are checking against the department column.
• THEN specifies the result to return when the condition is met.
• ELSE provides a default value if none of the conditions are met.
• END signifies the end of the CASE expression.

Output:

name	department	department_full_name
Alice	HR	Human Resources
Bob	Finance	Finance Department
Charlie	IT	Information Technology
David	Marketing	Other Department
Eva	HR	Human Resources

Scenario 2: Searched CASE

Example: Classifying Employees by Salary Range

Suppose we have a table employees with an additional salary column:

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'Alice', 'HR', 70000),
(2, 'Bob', 'Finance', 90000),
(3, 'Charlie', 'IT', 85000),
(4, 'David', 'Marketing', 60000),
(5, 'Eva', 'HR', 72000);

Query: Classify Employees Based on Salary Range

SELECT 
    name,
    salary,
    CASE
        WHEN salary < 60000 THEN 'Low'
        WHEN salary BETWEEN 60000 AND 79999 THEN 'Medium'
        WHEN salary BETWEEN 80000 AND 99999 THEN 'High'
        ELSE 'Very High'
    END AS salary_classification
FROM employees;

Explanation:

• This CASE expression is based on conditions (searched CASE).
• Each WHEN clause checks a condition on the salary column.
• THEN specifies the classification to return when the condition is met.
• ELSE provides a default classification if none of the conditions are met.

Output:

name	salary	salary_classification
Alice	70000.00	Medium
Bob	90000.00	High
Charlie	85000.00	High
David	60000.00	Medium
Eva	72000.00	Medium

Scenario 3: Using CASE in ORDER BY

Example: Prioritizing Departments in Query Results

If you want to prioritize the order of rows based on custom logic, you can use CASE in the ORDER BY clause.

Query: Order Employees by Custom Department Priority

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

Explanation:

• The CASE expression assigns a priority number to each department.
• ORDER BY uses this priority number to sort the results.

Output:

name	department
Alice	HR
Eva	HR
Charlie	IT
Bob	Finance
David	Marketing

Scenario 4: Aggregating Data with CASE

Example: Counting Employees in Each Salary Classification

You can use CASE within aggregate functions to perform complex data aggregation.

Query: Count Employees in Each Salary Classification

SELECT 
    COUNT(CASE WHEN salary < 60000 THEN 1 END) AS low_salary_count,
    COUNT(CASE WHEN salary BETWEEN 60000 AND 79999 THEN 1 END) AS medium_salary_count,
    COUNT(CASE WHEN salary BETWEEN 80000 AND 99999 THEN 1 END) AS high_salary_count,
    COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS very_high_salary_count
FROM employees;

Explanation:

• Each CASE expression evaluates to 1 if the condition is met and NULL otherwise.
• COUNT counts the non-NULL results for each condition, giving us the count of employees in each salary classification.

Output:

low_salary_count	medium_salary_count	high_salary_count	very_high_salary_count
0	3	2	0

Conclusion

The CASE keyword in SQL is an extremely versatile tool for implementing conditional logic in your queries. By understanding both simple and searched CASE expressions, you can handle a wide variety of data transformation and classification tasks directly within your SQL queries. These examples provide a solid foundation for using CASE in real-world scenarios. Practice with these and explore more complex cases to fully leverage the power of the CASE keyword.