SQL | Case Statement

The CASE statement in SQL is used to introduce conditional logic in SQL queries, allowing you to perform operations based on specific conditions. It's similar to "if-then-else" logic in programming languages.

There are two main forms of the CASE statement:

1. Simple CASE Expression:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

Syntax:

CASE expression 
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    ...
    ELSE result
END

Example: Suppose you have a products table and you want to categorize products based on their price:

SELECT product_name, price,
CASE price 
    WHEN < 10 THEN 'Cheap'
    WHEN < 50 THEN 'Moderate'
    ELSE 'Expensive'
END AS price_category
FROM products;

2. Searched CASE Expression:

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Syntax:

CASE 
    WHEN boolean_expression1 THEN result1
    WHEN boolean_expression2 THEN result2
    ...
    ELSE result
END

Example: Using the same products table to categorize products based on their price:

SELECT product_name, price,
CASE 
    WHEN price < 10 THEN 'Cheap'
    WHEN price < 50 THEN 'Moderate'
    ELSE 'Expensive'
END AS price_category
FROM products;

Points to Note:

The CASE statement returns the first true condition. So, if multiple conditions are true, only the result of the first true condition will be returned.
The ELSE part is optional, but it's good practice to include it to handle potential cases that aren't covered by your conditions.
You can use CASE statements in various parts of your SQL query like the SELECT, WHERE, ORDER BY, and even in GROUP BY clauses.
Always ensure the data types returned by each branch of the CASE statement are compatible. For example, if one THEN returns a string, all others should also return strings, or you might encounter errors.
The CASE statement can be nested for more complex conditional logic, but this can lead to readability issues, so always document and format your queries properly.

Using the CASE statement wisely can significantly increase the power and flexibility of your SQL queries, allowing you to handle various data scenarios within the database itself without additional processing in the application layer.

How to Use CASE in SQL:

SELECT column1,
       CASE
          WHEN column1 > 10 THEN 'High'
          WHEN column1 > 5 THEN 'Medium'
          ELSE 'Low'
       END AS category
FROM your_table;

Simple and Nested CASE Statements in SQL:

SELECT column1,
       CASE
          WHEN column1 > 10 THEN 'High'
          ELSE
             CASE
                WHEN column1 > 5 THEN 'Medium'
                ELSE 'Low'
             END
       END AS category
FROM your_table;

Using CASE for Conditional Logic in SQL:

SELECT column1,
       CASE
          WHEN column2 = 'A' AND column3 > 10 THEN 'Condition 1'
          WHEN column2 = 'B' THEN 'Condition 2'
          ELSE 'Default'
       END AS result
FROM your_table;

CASE vs. IF Statement in SQL: SQL does not have a direct IF statement. Instead, you use the CASE statement for conditional logic.

CASE with Aggregate Functions in SQL:

SELECT AVG(CASE WHEN column1 > 10 THEN column2 ELSE 0 END) AS avg_result
FROM your_table;

Handling NULL Values with CASE in SQL:

SELECT column1,
       CASE
          WHEN column2 IS NULL THEN 'No Data'
          ELSE column2
       END AS result
FROM your_table;

Multiple Conditions with CASE in SQL:

SELECT column1,
       CASE
          WHEN column2 = 'A' AND column3 > 10 THEN 'Condition 1'
          WHEN column2 = 'B' THEN 'Condition 2'
          WHEN column3 IS NULL THEN 'Unknown'
          ELSE 'Default'
       END AS result
FROM your_table;

Using CASE with SELECT, UPDATE, or DELETE in SQL:

-- SELECT
SELECT column1,
       CASE
          WHEN column1 > 10 THEN 'High'
          ELSE 'Low'
       END AS category
FROM your_table;

-- UPDATE
UPDATE your_table
SET column2 = CASE WHEN column1 > 10 THEN 'High' ELSE 'Low' END
WHERE condition;

-- DELETE
DELETE FROM your_table
WHERE column1 = CASE WHEN column2 = 'A' THEN 10 ELSE 20 END;

Dynamic Values in CASE Statements in SQL:

SELECT column1,
       CASE
          WHEN column2 = 'A' THEN column3
          WHEN column2 = 'B' THEN column4
          ELSE 0
       END AS dynamic_value
FROM your_table;

Searched and Simple CASE Expressions in SQL:

Searched CASE:

SELECT column1,
       CASE
          WHEN column2 = 'A' THEN 'Category A'
          WHEN column2 = 'B' THEN 'Category B'
          ELSE 'Other'
       END AS category
FROM your_table;

Simple CASE:

SELECT column1,
       CASE column2
          WHEN 'A' THEN 'Category A'
          WHEN 'B' THEN 'Category B'
          ELSE 'Other'
       END AS category
FROM your_table;

CASE Statement vs. DECODE in SQL: In some databases like Oracle, DECODE is an alternative to the CASE statement for simple conditional logic.

CASE Statement in GROUP BY Clauses in SQL:

SELECT column1,
       COUNT(*),
       CASE
          WHEN column2 = 'A' THEN 'Category A'
          WHEN column2 = 'B' THEN 'Category B'
          ELSE 'Other'
       END AS category
FROM your_table
GROUP BY column1, category;