🔀 The CASE expression in SQL is used to implement conditional logic within a query. It works similarly to an if-else or switch statement in programming languages, allowing you to return different values based on specified conditions. The CASEexpression is commonly used for categorizing data, creating calculated columns, custom sorting, and conditional aggregation.

📖 What is the CASE Expression?

The CASE expression evaluates one or more conditions in order. As soon as a condition evaluates to TRUE, SQL returns the corresponding result and stops checking the remaining conditions. If none of the conditions match, the optional ELSE clause is returned.

📝 Types of CASE Expressions

SQL supports two forms of the CASE expression:

1️⃣ Simple CASE Expression

A simple CASE compares a single expression against multiple possible values.

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

Example: Department Names

SELECT
    Name,
    Department,
    CASE Department
        WHEN 'CS' THEN 'Computer Science'
        WHEN 'IT' THEN 'Information Technology'
        WHEN 'ME' THEN 'Mechanical Engineering'
        ELSE 'Other Department'
    END AS DepartmentName
FROM Students;

2️⃣ Searched CASE Expression

A searched CASE evaluates one or more Boolean conditions and is the more flexible form of the expression.

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

Example: Grade Classification

SELECT
    Name,
    Marks,
    CASE
        WHEN Marks >= 90 THEN 'A'
        WHEN Marks >= 80 THEN 'B'
        WHEN Marks >= 70 THEN 'C'
        WHEN Marks >= 60 THEN 'D'
        ELSE 'F'
    END AS Grade
FROM Students;

📊 Sample Table

Assume the following Students table:

🏷️ CASE in the SELECT Statement

One of the most common uses of CASE is creating new calculated columns based on conditions.

SELECT
    Name,
    Marks,
    CASE
        WHEN Marks >= 50 THEN 'Pass'
        ELSE 'Fail'
    END AS Result
FROM Students;

🔍 CASE in the WHERE Clause

Although less common, CASE can also be used inside the WHERE clause when conditional filtering is required.

SELECT *
FROM Students
WHERE
CASE
    WHEN Department = 'Computer Science'
        THEN Marks >= 90
    ELSE Marks >= 80
END;

📈 CASE in ORDER BY

CASE can customize the sorting order of query results.

SELECT Name,
       Department
FROM Students
ORDER BY
CASE
    WHEN Department = 'Computer Science' THEN 1
    WHEN Department = 'Mathematics' THEN 2
    ELSE 3
END;

📊 CASE with Aggregate Functions

Combining CASE with aggregate functions enables conditional calculations and reporting.

SELECT
    COUNT(
        CASE
            WHEN Marks >= 90 THEN 1
        END
    ) AS ExcellentStudents
FROM Students;

SELECT
    SUM(
        CASE
            WHEN Department = 'Computer Science'
            THEN Marks
            ELSE 0
        END
    ) AS TotalCSMarks
FROM Students;

📌 CASE with UPDATE

The CASE expression can also be used to update values based on conditions.

UPDATE Students
SET Grade =
CASE
    WHEN Marks >= 90 THEN 'A'
    WHEN Marks >= 80 THEN 'B'
    WHEN Marks >= 70 THEN 'C'
    ELSE 'D'
END;

📊 Simple CASE vs Searched CASE

💼 Real-World Example

A university wants to display the performance level of each student instead of showing only numerical marks.

SELECT
    StudentID,
    Name,
    Marks,
    CASE
        WHEN Marks >= 90 THEN 'Excellent'
        WHEN Marks >= 75 THEN 'Good'
        WHEN Marks >= 50 THEN 'Average'
        ELSE 'Needs Improvement'
    END AS Performance
FROM Students
ORDER BY Marks DESC;

This query converts raw marks into meaningful performance categories that are easier to understand in reports.

⚠️ Best Practices

🚀 Key Points to Remember