Blog

The CASE statement is a powerful tool for conditional logic within SQL queries, allowing you to perform different actions based on specified conditions.

1. Understanding the CASE Statement:

The CASE statement evaluates a list of conditions and returns one of multiple possible result expressions. It’s similar to the if-then-else construct in other programming languages.

2. Syntax:

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

3. Explanation of Syntax:

• CASE: Begins the CASE statement.
• WHEN condition THEN result: Specifies a condition and the result expression to be returned if the condition is true.
• ELSE default_result: Optional. Specifies a default result expression if none of the preceding conditions are true.
• END: Ends the CASE statement.

4. Example 1: Basic Usage

Let’s consider a scenario where we have a table Employees with columns Name, Salary, and we want to categorize employees based on their salary.

SELECT 
    Name,
    Salary,
    CASE 
        WHEN Salary < 50000 THEN 'Low'
        WHEN Salary >= 50000 AND Salary < 100000 THEN 'Medium'
        ELSE 'High'
    END AS Salary_Category
FROM 
    Employees;

Explanation:

• For each row in the Employees table, the CASE statement evaluates the salary.
• If the salary is less than 50,000, it categorizes the employee as ‘Low’.
• If the salary is between 50,000 and 100,000, it categorizes the employee as ‘Medium’.
• Otherwise, it categorizes the employee as ‘High’.

5. Example 2: Handling NULL Values

In some cases, we might want to handle NULL values differently. Let’s modify the previous example to handle NULL salaries.

SELECT 
    Name,
    Salary,
    CASE 
        WHEN Salary IS NULL THEN 'No Salary Info'
        WHEN Salary < 50000 THEN 'Low'
        WHEN Salary >= 50000 AND Salary < 100000 THEN 'Medium'
        ELSE 'High'
    END AS Salary_Category
FROM 
    Employees;

Explanation:

• We added a condition WHEN Salary IS NULL THEN 'No Salary Info' to handle NULL values.
• Now, if the salary is NULL, it categorizes the employee as ‘No Salary Info’.

6. Example 3: Using CASE in UPDATE Statements

The CASE statement can also be used in UPDATE statements to conditionally update values in a table. Let’s consider an example where we want to update the Bonus column based on the Salary column.

UPDATE Employees
SET Bonus = 
    CASE 
        WHEN Salary < 50000 THEN 1000
        WHEN Salary >= 50000 AND Salary < 100000 THEN 500
        ELSE 0
    END;

Explanation:

• For each row in the Employees table, the CASE statement evaluates the salary.
• If the salary is less than 50,000, it sets the bonus to 1000.
• If the salary is between 50,000 and 100,000, it sets the bonus to 500.
• Otherwise, it sets the bonus to 0.

Conclusion

The CASE statement in SQL is a versatile tool for implementing conditional logic within queries and updates. By understanding its syntax and usage examples, you can effectively manipulate and analyze data in relational databases. Experiment with different scenarios to deepen your understanding of how CASE statements can be applied in SQL programming.