Understanding the CASE Statement in SQL: Examples

SQL is a powerful language that allows us to retrieve, manipulate, and analyze data from relational databases. One of the most useful features in SQL is the CASE statement, which allows us to perform conditional logic within our queries. In this blog post, we will explore the CASE statement in SQL, starting from simple examples and gradually moving towards more complex scenarios.

1. Simple CASE Example

Let's begin with a simple example to understand the basic syntax and functionality of the CASE statement. Suppose we have a table called "employees" with columns "employee_id", "first_name", "last_name", and "salary". We want to categorize employees based on their salary range.

To achieve this, we can use the following SQL query:

SELECT first_name, last_name,
    CASE
        WHEN salary < 50000 THEN 'Low'
        WHEN salary >= 50000 AND salary < 100000 THEN 'Medium'
        ELSE 'High'
    END AS salary_category
FROM employees;

In the above example, we use the CASE statement to evaluate the salary of each employee. If the salary is less than 50,000, we assign the category 'Low'. If the salary is between 50,000 and 100,000, we assign the category 'Medium'. For salaries greater than or equal to 100,000, we assign the category 'High'.

2. Complex CASE Example

Now, let's explore a more complex example of using the CASE statement in SQL. Suppose we have a table called "orders" with columns "order_id", "order_date", "customer_id", and "order_total". We want to calculate the discount percentage for each order based on the order date and customer type.

To achieve this, we can use the following SQL query:

SELECT order_id, order_date, customer_id, order_total,
    CASE
        WHEN order_date < '2022-01-01' AND customer_type = 'Regular' THEN 0.05
        WHEN order_date < '2022-01-01' AND customer_type = 'Premium' THEN 0.1
        WHEN order_date >= '2022-01-01' AND customer_type = 'Regular' THEN 0.1
        WHEN order_date >= '2022-01-01' AND customer_type = 'Premium' THEN 0.15
        ELSE 0
    END AS discount_percentage
FROM orders;

In this example, we use the CASE statement to calculate the discount percentage for each order. If the order date is before January 1, 2022, and the customer type is 'Regular', we assign a discount of 5%. If the customer type is 'Premium', we assign a discount of 10%. If the order date is on or after January 1, 2022, and the customer type is 'Regular', we assign a discount of 10%. If the customer type is 'Premium', we assign a discount of 15%. For all other cases, we assign a discount of 0%.

Conclusion

The CASE statement in SQL is a powerful tool for performing conditional logic within our queries. It allows us to categorize and calculate values based on specific conditions. In this blog post, we explored the CASE statement through simple and complex examples, showcasing its versatility and usefulness in data analysis and manipulation. By mastering the CASE statement, SQL developers can enhance their query capabilities and gain deeper insights from their data.

Remember, the examples provided here are just the tip of the iceberg. The CASE statement can be further customized and combined with other SQL functions and clauses to handle even more complex scenarios. So, keep exploring and experimenting with the CASE statement to unlock its full potential in your SQL journey.