SQL CASE Expression

Welcome to The Coding College! In this tutorial, we will dive deep into the SQL CASE Expression, a powerful tool used for conditional logic in SQL queries. Learn how to use it effectively to simplify complex queries and enhance data insights.

What Is the SQL CASE Expression?

The SQL CASE expression is used to create conditional logic in SQL queries. It works like an if-then-else statement, allowing you to evaluate conditions and return specific values based on the outcome.

Syntax

Simple CASE Syntax

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

Searched CASE Syntax

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

• Simple CASE compares an expression to predefined values.
• Searched CASE evaluates multiple conditions.

Examples

Example Table: sales

Example 1: Simple CASE

Query: Assign Product Categories

SELECT product,  
       CASE product  
           WHEN 'Laptop' THEN 'Electronics'  
           WHEN 'Phone' THEN 'Mobiles'  
           WHEN 'Tablet' THEN 'Gadgets'  
           ELSE 'Other'  
       END AS category  
FROM sales;  

Result

Example 2: Searched CASE

Query: Identify High-Value Sales

SELECT sale_id, product, quantity,  
       CASE  
           WHEN quantity * price_per_unit > 3000 THEN 'High Value'  
           WHEN quantity * price_per_unit BETWEEN 1000 AND 3000 THEN 'Medium Value'  
           ELSE 'Low Value'  
       END AS sale_category  
FROM sales;  

Result

Example 3: Using CASE in ORDER BY

Query: Prioritize High-Value Sales

SELECT sale_id, product, quantity, price_per_unit,  
       quantity * price_per_unit AS total_price  
FROM sales  
ORDER BY  
       CASE  
           WHEN quantity * price_per_unit > 3000 THEN 1  
           WHEN quantity * price_per_unit BETWEEN 1000 AND 3000 THEN 2  
           ELSE 3  
       END;  

Explanation

• High-value sales are prioritized first, followed by medium, then low-value sales.

Example 4: Using CASE in an UPDATE Statement

Query: Update Regions Based on Conditions

UPDATE sales  
SET region =  
       CASE  
           WHEN region = 'North' THEN 'Northern Region'  
           WHEN region = 'South' THEN 'Southern Region'  
           ELSE region  
       END;  

Explanation

• Updates the region column to more descriptive names based on conditions.

Example 5: Aggregating Data with CASE

Query: Calculate Total Sales by Region

SELECT region,  
       SUM(CASE  
           WHEN product = 'Laptop' THEN quantity * price_per_unit  
           ELSE 0  
       END) AS laptop_sales,  
       SUM(CASE  
           WHEN product = 'Phone' THEN quantity * price_per_unit  
           ELSE 0  
       END) AS phone_sales  
FROM sales  
GROUP BY region;  

Explanation

• Computes total sales for each product type in each region.

Key Benefits of CASE Expression

1. Simplifies Logic: Avoids complex nested queries by integrating conditional logic into a single query.
2. Improves Readability: Makes queries easier to understand and maintain.
3. Versatile: Can be used in SELECT, WHERE, ORDER BY, GROUP BY, and UPDATE statements.

Common Use Cases

1. Data Categorization: Grouping data into categories based on conditions.
2. Dynamic Calculations: Calculating fields dynamically based on input data.
3. Custom Sorting: Prioritizing results with specific conditions.
4. Conditional Updates: Modifying data selectively in an UPDATE query.

Best Practices

1. Use ELSE: Always include an ELSE clause to handle unexpected cases.
2. Optimize for Performance: Ensure conditions are simple and well-indexed for better query performance.
3. Test Separately: Test individual CASE conditions to validate their logic.

Conclusion

The SQL CASE expression is an indispensable tool for adding conditional logic to your SQL queries. Whether you’re categorizing data, customizing sorting, or performing dynamic calculations, CASE empowers you to write efficient and intuitive queries.