How CASE WHEN works in SQL? Best CASE WHEN examples

How CASE WHEN works in SQL? Best CASE WHEN examples

Understanding conditional logic is crucial when working with SQL, and one of the most powerful tools for this is the CASE WHEN statement. It allows you to perform conditional operations directly in your queries, making data analysis and transformation easier. In this guide, I’ll walk you through how CASE WHEN works in SQL, along with some of the best examples to illustrate its usage.

What is CASE WHEN in SQL?

The CASE WHEN statement in SQL is a conditional expression used to execute different logic based on specified conditions. It works similarly to an IF-THEN-ELSE statement in programming languages. You can use it in SELECT, UPDATE, ORDER BY, and even in WHERE clauses.

The basic syntax of a CASE statement looks like this:

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

The CASE statement evaluates conditions in the given order. As soon as it finds the first condition that is TRUE, it returns the corresponding result and ignores the rest. If no conditions match, it returns the ELSE value. If there’s no ELSE, it returns NULL.

Simple Examples of CASE WHEN in SQL

Example 1: Categorizing Data

Let’s say we have a table employees with columns name and salary. We want to categorize employees based on their salary level.

SELECT 
    name, 
    salary,
    CASE 
        WHEN salary > 80000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

This will return a result like:

name salary salary_category
John 90000 High
Jane 60000 Medium
Mike 40000 Low

Example 2: Handling NULL values

Often, datasets contain NULL values that can break queries or return unexpected results. The CASE statement helps handle them:

SELECT 
    name, 
    department,
    CASE 
        WHEN department IS NULL THEN 'Unknown'
        ELSE department
    END AS department_name
FROM employees;

This ensures that any employee with a NULL department gets labeled as “Unknown” rather than returning NULL.

Using CASE WHEN in Different SQL Clauses

Using CASE WHEN in ORDER BY

Sorting data based on conditions is a common requirement. Suppose we want to sort employees by salary category instead of the absolute salary number:

SELECT 
    name, 
    salary,
    CASE 
        WHEN salary > 80000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees
ORDER BY 
    CASE 
        WHEN salary > 80000 THEN 1
        WHEN salary BETWEEN 50000 AND 80000 THEN 2
        ELSE 3
    END;

This ensures “High” salary employees appear first, followed by “Medium”, and then “Low”.

Using CASE WHEN in WHERE

If we need to filter data dynamically based on certain conditions, we can use CASE in a WHERE clause:

SELECT * FROM employees
WHERE 
    CASE 
        WHEN department IS NULL THEN FALSE 
        ELSE TRUE 
    END;

This query ensures that only employees with a department value (non-NULL) are included.

CASE WHEN with Aggregations

The CASE statement is often used inside aggregate functions like SUM and COUNT.

Example 1: Counting Based on Conditions

To count employees in each salary category:

SELECT 
    COUNT(CASE WHEN salary > 80000 THEN 1 END) AS high_salary_count,
    COUNT(CASE WHEN salary BETWEEN 50000 AND 80000 THEN 1 END) AS medium_salary_count,
    COUNT(CASE WHEN salary < 50000 THEN 1 END) AS low_salary_count
FROM employees;

Example 2: Conditional Summation

Summing up total salaries for each category:

SELECT 
    SUM(CASE WHEN salary > 80000 THEN salary END) AS high_salary_total,
    SUM(CASE WHEN salary BETWEEN 50000 AND 80000 THEN salary END) AS medium_salary_total,
    SUM(CASE WHEN salary < 50000 THEN salary END) AS low_salary_total
FROM employees;

Nested CASE Statements

Sometimes, a CASE statement needs to be more complex. We can nest them inside each other.

SELECT 
    name, 
    salary,
    CASE 
        WHEN salary > 80000 THEN 
            CASE 
                WHEN salary > 100000 THEN 'Very High'
                ELSE 'High'
            END
        WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

Here, salaries above $100,000 are categorized as “Very High”, while those between 80,000 and 100,000 remain “High”.

Performance Considerations

While CASE WHEN is powerful, here are some things to keep in mind for performance:

  • Ensure indexes are used properly, as CASE statements on indexed columns may affect performance.
  • Avoid complex nested CASE expressions as they can slow down queries.
  • For large datasets, test query performance before deploying for production.

Conclusion

The CASE WHEN statement in SQL is an incredibly useful tool for handling conditional situations in queries. Whether categorizing data, handling NULL values, or even dynamically ordering results, it provides flexibility and power. Mastering it will significantly enhance your SQL querying skills and allow you to transform your datasets efficiently.

 

Other interesting article:

How EXCEPT works in SQL? Best EXCEPT examples