> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# CASE expression in SQL

The `CASE` expression in SQL is used to implement conditional logic in queries. It allows you to perform different actions based on specific conditions. The `CASE` expression is similar to the `IF-THEN-ELSE` logic in programming languages and is useful for creating derived columns, categorizing data, and applying conditional formatting.

## Key Points

1. **Conditional Logic**: The `CASE` expression allows you to implement conditional logic in SQL queries.
2. **Multiple Conditions**: You can specify multiple conditions using `WHEN` clauses.
3. **ELSE Clause**: An optional `ELSE` clause can be used to specify a default value if none of the conditions are met.
4. **Use Cases**: Commonly used in `SELECT`, `UPDATE`, and `ORDER BY` clauses to create derived columns, categorize data, or apply conditional sorting.

## Syntax of CASE

```sql theme={"system"}
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END
```

* `condition1, condition2, ...`: The conditions to evaluate.
* `result1, result2, ...`: The result to return if the corresponding condition is true.
* `default_result`: The result to return if none of the conditions are true (optional).

## Examples

### CASE in SELECT Clause

Suppose you have a table named `Employees` that stores employee details.

Table: Employees

| EmployeeID | Name    | City       | State      | Salary |
| ---------- | ------- | ---------- | ---------- | ------ |
| 1          | Anand   | Chennai    | Tamil Nadu | 50000  |
| 2          | Bala    | Coimbatore | Tamil Nadu | 60000  |
| 3          | Kavitha | Karaikal   | Puducherry | 55000  |
| 4          | Raj     | Madurai    | Tamil Nadu | 70000  |
| 5          | Kumar   | Trichy     | Tamil Nadu | 45000  |

To categorize employees based on their salary:

```sql theme={"system"}
SELECT Name, Salary,
    CASE
        WHEN Salary > 60000 THEN 'High'
        WHEN Salary BETWEEN 50000 AND 60000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryCategory
FROM Employees;
```

| Name    | Salary | SalaryCategory |
| ------- | ------ | -------------- |
| Anand   | 50000  | Medium         |
| Bala    | 60000  | Medium         |
| Kavitha | 55000  | Medium         |
| Raj     | 70000  | High           |
| Kumar   | 45000  | Low            |

### CASE in ORDER BY Clause

You can use the `CASE` expression in the `ORDER BY` clause to apply conditional sorting.

To sort employees by salary category (High, Medium, Low):

```sql theme={"system"}
SELECT Name, Salary
FROM Employees
ORDER BY
    CASE
        WHEN Salary > 60000 THEN 1
        WHEN Salary BETWEEN 50000 AND 60000 THEN 2
        ELSE 3
    END;
```

| Name    | Salary |
| ------- | ------ |
| Raj     | 70000  |
| Anand   | 50000  |
| Bala    | 60000  |
| Kavitha | 55000  |
| Kumar   | 45000  |

### CASE in UPDATE Statement

You can use the `CASE` expression in the `UPDATE` statement to apply conditional updates.

To update the salary of employees based on their current salary:

```sql theme={"system"}
UPDATE Employees
SET Salary =
    CASE
        WHEN Salary > 60000 THEN Salary + 5000
        WHEN Salary BETWEEN 50000 AND 60000 THEN Salary + 3000
        ELSE Salary + 2000
    END;
```

The salaries are updated as follows:

* Employees with a salary greater than ₹60,000 receive a ₹5,000 raise.
* Employees with a salary between ₹50,000 and ₹60,000 receive a ₹3,000 raise.
* All other employees receive a ₹2,000 raise.

## Practical Use Case

Suppose you have a table named `Students` that stores student details.

Students

| StudentID | Name    | City       | State      | Percentage |
| --------- | ------- | ---------- | ---------- | ---------- |
| 1         | Ram     | Chennai    | Tamil Nadu | 92         |
| 2         | Karthik | Coimbatore | Tamil Nadu | 88         |
| 3         | David   | Bangalore  | Karnataka  | 95         |
| 4         | Kannan  | Karaikal   | Puducherry | 91         |
| 5         | Siva    | Madurai    | Tamil Nadu | 89         |

To categorize students based on their percentage:

```sql theme={"system"}
SELECT Name, Percentage,
    CASE
        WHEN Percentage >= 90 THEN 'A'
        WHEN Percentage BETWEEN 80 AND 89 THEN 'B'
        ELSE 'C'
    END AS Grade
FROM Students;
```

| Name    | Percentage | Grade |
| ------- | ---------- | ----- |
| Ram     | 92         | A     |
| Karthik | 88         | B     |
| David   | 95         | A     |
| Kannan  | 91         | A     |
| Siva    | 89         | B     |

## Key Takeaways

1. The `CASE` expression allows you to implement conditional logic in SQL queries.
2. It can be used in `SELECT`, `UPDATE`, and `ORDER BY` clauses to create derived columns, categorize data, or apply conditional sorting.
3. Multiple conditions can be specified using `WHEN` clauses, and an optional `ELSE` clause can provide a default value.
4. The `CASE` expression is useful for creating dynamic and flexible queries.