SQL SELECT case else

23 Sep 2023

codenewbie

In SQL, the CASE statement allows you to perform conditional logic within a SELECT statement. It provides a way to conditionally retrieve data based on different conditions. Sometimes, you may encounter scenarios where you need to handle cases where none of the conditions are met. In such situations, the CASE ELSE statement can be used.

The `CASE` Statement Overview

Before diving into the CASE ELSE statement, let’s briefly review the basics of the CASE statement. The syntax of the CASE statement is as follows:

SELECT 
    column_name,
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE result
    END AS result_column
FROM
    table_name;

The CASE statement evaluates the conditions in the order specified. Once a condition is met, the corresponding result is returned, and the evaluation is stopped.

Using `CASE ELSE`

In cases where none of the conditions provided in the CASE statement match, we can include an ELSE statement to define the result to be returned. The ELSE part acts as a fallback option when no conditions are satisfied.

Here’s an example of using CASE ELSE in a SELECT statement:

SELECT 
    column_name,
    CASE 
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ...
        ELSE default_result
    END AS result_column
FROM
    table_name;

In the above example, if none of the conditions (condition1, condition2, etc.) are met, the default_result will be returned as the value of the result_column.

Use Cases for `CASE ELSE`

The CASE ELSE statement is useful in various scenarios. Let’s explore a few common use cases:

Handling Unmatched Records

Suppose you have a table containing employees’ salary data. You want to categorize their salaries based on different ranges, but you also want to assign an “Unknown” category to any salary not falling within the specified ranges:

SELECT 
    employee_name,
    CASE
        WHEN salary >= 50000 AND salary <= 70000 THEN 'Low Range'
        WHEN salary > 70000 AND salary <= 90000 THEN 'Medium Range'
        WHEN salary > 90000 AND salary <= 120000 THEN 'High Range'
        ELSE 'Unknown'
    END AS salary_range
FROM
    employee_data;

In the above example, if the salary doesn’t fall within any range specified in the WHEN conditions, it will be categorized as “Unknown”.

Dealing with Null Values

Another scenario where CASE ELSE is useful is when dealing with null values. You might want to substitute null values with a specific value or handle them differently:

SELECT 
    column_name,
    CASE
        WHEN column_name IS NULL THEN 'N/A'
        ELSE column_name
    END AS result_column
FROM
    table_name;

In the above example, if the column_name contains a null value, it will be replaced with “N/A” using the CASE ELSE statement.

Conclusion

In this guide, we have covered the usage of the CASE ELSE statement in SQL. It provides a flexible and powerful way to handle scenarios where none of the conditions in the CASE statement are met. By incorporating the CASE ELSE statement, you can ensure your SQL queries handle fallback scenarios and produce meaningful results.

#sql #codenewbie

The CASE Statement Overview

Using CASE ELSE

Use Cases for CASE ELSE