SQL - CASE Expressions in SQL

The CASE expression in SQL is used to perform conditional logic within a SQL query. It allows you to check one or more conditions and return different values depending on which condition is true. It is similar to an if-else statement in programming languages.

CASE is useful when you want to transform, categorize, label, or calculate data directly while retrieving it from a database. Instead of modifying the stored data, you can use CASE to display the data in a meaningful way in the query result.

Basic Syntax

The general syntax of a searched CASE expression is:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
END

SQL evaluates the conditions from top to bottom. When it finds the first condition that is true, it returns the corresponding result and stops evaluating the remaining conditions.

The ELSE clause is optional. If no condition is true and there is no ELSE clause, SQL returns NULL.

Example of CASE Expression

Suppose we have a table named Students:

StudentID	Name	Marks
101	Rahul	85
102	Priya	72
103	Arun	48
104	Sneha	91

We can use CASE to classify students according to their marks:

SELECT
    Name,
    Marks,
    CASE
        WHEN Marks >= 80 THEN 'Excellent'
        WHEN Marks >= 60 THEN 'Good'
        WHEN Marks >= 40 THEN 'Pass'
        ELSE 'Fail'
    END AS Performance
FROM Students;

The result would be:

Name	Marks	Performance
Rahul	85	Excellent
Priya	72	Good
Arun	48	Pass
Sneha	91	Excellent

Here, SQL does not change the Marks column. It creates a new calculated column called Performance based on the conditions.

Types of CASE Expressions

There are two commonly used forms of CASE expressions:

Simple CASE expression
Searched CASE expression

1. Simple CASE Expression

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

Syntax:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    WHEN value3 THEN result3
    ELSE default_result
END

For example, suppose a Students table contains a Grade column:

SELECT
    Name,
    Grade,
    CASE Grade
        WHEN 'A' THEN 'Excellent'
        WHEN 'B' THEN 'Good'
        WHEN 'C' THEN 'Average'
        WHEN 'D' THEN 'Needs Improvement'
        ELSE 'Invalid Grade'
    END AS Description
FROM Students;

In this example, SQL compares the value of Grade with each WHEN value.

2. Searched CASE Expression

A searched CASE expression evaluates individual conditions.

For example:

SELECT
    Name,
    Salary,
    CASE
        WHEN Salary >= 100000 THEN 'High Salary'
        WHEN Salary >= 50000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS Salary_Category
FROM Employees;

This form is more flexible because the conditions can use comparison operators such as >, <, >=, <=, and =.

CASE with Multiple Conditions

A CASE expression can contain several conditions.

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

The order of conditions is important. SQL checks the first condition, then the second, and continues until it finds a true condition.

For example, a student with 85 marks satisfies both Marks >= 80 and Marks >= 70. However, SQL stops at the first matching condition and returns A.

Therefore, conditions should generally be arranged from the most specific or highest threshold to the broader conditions.

CASE in the SELECT Statement

The most common use of CASE is inside a SELECT statement.

For example:

SELECT
    ProductName,
    Price,
    CASE
        WHEN Price >= 5000 THEN 'Expensive'
        WHEN Price >= 2000 THEN 'Moderate'
        ELSE 'Affordable'
    END AS Price_Category
FROM Products;

This allows the query to display an additional category without changing the original product prices.

CASE with Calculations

CASE can also be used to perform conditional calculations.

For example, a company may provide different discounts depending on the purchase amount:

SELECT
    CustomerName,
    Amount,
    CASE
        WHEN Amount >= 10000 THEN Amount * 0.20
        WHEN Amount >= 5000 THEN Amount * 0.10
        ELSE Amount * 0.05
    END AS Discount
FROM Orders;

Here, the discount is calculated differently depending on the order amount.

CASE in an UPDATE Statement

CASE can also be used with UPDATE to modify values conditionally.

For example:

UPDATE Employees
SET Salary =
    CASE
        WHEN Performance = 'Excellent' THEN Salary * 1.15
        WHEN Performance = 'Good' THEN Salary * 1.10
        ELSE Salary
    END;

In this example, employees with excellent performance receive a 15 percent salary increase, while employees with good performance receive a 10 percent increase.

Because this operation modifies database data, an UPDATE statement should be used carefully, particularly when working with production databases.

CASE in ORDER BY

A CASE expression can also control the order in which records are displayed.

For example:

SELECT Name, Status
FROM Employees
ORDER BY
    CASE
        WHEN Status = 'Active' THEN 1
        WHEN Status = 'On Leave' THEN 2
        WHEN Status = 'Inactive' THEN 3
        ELSE 4
    END;

This allows the developer to define a custom sorting order instead of relying on normal alphabetical or numerical ordering.

CASE with NULL Values

CASE can also be useful when handling missing values.

For example:

SELECT
    Name,
    CASE
        WHEN PhoneNumber IS NULL THEN 'Not Available'
        ELSE PhoneNumber
    END AS ContactNumber
FROM Customers;

If a customer does not have a phone number, the query displays Not Available.

The original PhoneNumber value remains unchanged.

CASE with Aggregate Functions

CASE can be combined with aggregate functions such as COUNT() and SUM() to perform conditional calculations.

For example, suppose an Employees table contains a Department column:

SELECT
    COUNT(
        CASE
            WHEN Department = 'IT' THEN 1
        END
    ) AS IT_Employees
FROM Employees;

This counts employees who belong to the IT department.

Another example is conditional sales calculation:

SELECT
    SUM(
        CASE
            WHEN Amount >= 10000 THEN Amount
            ELSE 0
        END
    ) AS High_Value_Sales
FROM Orders;

This calculates the total value of orders where the amount is at least 10,000.

CASE in WHERE Conditions

Although CASE is most commonly used to produce calculated values, it can also be incorporated into filtering logic when appropriate.

For example:

SELECT *
FROM Employees
WHERE
    CASE
        WHEN Department = 'IT' THEN Salary
        ELSE 0
    END > 50000;

However, directly expressing the condition with logical operators is often clearer and more efficient:

SELECT *
FROM Employees
WHERE Department = 'IT'
  AND Salary > 50000;

Therefore, CASE should be used when conditional value generation is actually needed rather than simply replacing straightforward Boolean conditions.

Important Rules of CASE

There are several important points to remember when using CASE expressions.

First, every CASE expression must end with the END keyword.

Second, conditions are evaluated in order. The first true condition determines the result.

Third, the ELSE clause is optional. If it is omitted and no condition matches, SQL generally returns NULL.

Fourth, the data types returned by the different THEN and ELSE branches should be compatible. Mixing incompatible data types can cause errors or implicit conversions depending on the database system.

Fifth, CASE generally produces a value; it is not a complete replacement for procedural IF-ELSE statements used in database programming environments.

Advantages of CASE Expressions

CASE expressions provide several benefits:

They allow conditional logic directly within SQL queries.
They can categorize numerical or textual data.
They can create meaningful labels from existing values.
They can perform conditional calculations.
They can be used for customized sorting.
They can help handle missing or special values.
They can be combined with aggregate functions.
They avoid the need to modify the original data merely to display it differently.

Practical Example

Consider an Orders table:

OrderID	Customer	Amount
1	Ravi	12000
2	Meena	7000
3	Kiran	2500
4	Anu	15000

We can classify the orders into categories:

SELECT
    OrderID,
    Customer,
    Amount,
    CASE
        WHEN Amount >= 10000 THEN 'Premium'
        WHEN Amount >= 5000 THEN 'Standard'
        ELSE 'Basic'
    END AS Order_Category
FROM Orders;

The result would be:

OrderID	Customer	Amount	Order_Category
1	Ravi	12000	Premium
2	Meena	7000	Standard
3	Kiran	2500	Basic
4	Anu	15000	Premium

This demonstrates the main purpose of a CASE expression: it allows SQL to make conditional decisions while retrieving or processing data without changing the underlying table.

Summary

The CASE expression is an important SQL feature for implementing conditional logic within queries. It can be used to classify records, generate labels, calculate values, handle NULL values, customize sorting, and perform conditional aggregation. The two main forms are simple CASE, which compares one expression with several values, and searched CASE, which evaluates multiple conditions. Understanding CASE helps developers write more flexible and meaningful SQL queries while keeping data-processing logic close to the database query itself.