SQL - CASE

SQL CASE: Conditional Logic in Queries

The SQL CASE Expression

The CASE expression is SQL’s way of handling if/then/else logic. It allows you to evaluate a list of conditions and return one of multiple possible result expressions. It does not control the flow of execution like an IF statement in procedural languages; instead, it is an expression that returns a value.

This makes it incredibly versatile for creating derived columns, custom sorting logic, and conditional updates, all within a single SQL statement.

Syntax: Simple vs. Searched CASE

There are two forms of the CASE expression:

1. Simple CASE Expression

This form compares an expression against a set of specific values.

CASE input_expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE else_result
END

2. Searched CASE Expression

This form evaluates a set of independent boolean conditions.

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

Note: The CASE statement processes conditions in order and stops at the first one that is met. The optional ELSE clause handles cases where no conditions are met; if omitted, NULL is returned.

Example: Using CASE in a SELECT Statement

Let’s use a CUSTOMERS table to categorize customers into different tiers based on their SALARY.

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL,
    ADDRESS CHAR(25), SALARY DECIMAL(18, 2)
);
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

This query uses a searched CASE expression to create a new CustomerTier column.

SELECT NAME, SALARY,
    CASE
        WHEN SALARY >= 9000 THEN 'Platinum'
        WHEN SALARY >= 6000 THEN 'Gold'
        WHEN SALARY >= 3000 THEN 'Silver'
        ELSE 'Bronze'
    END AS CustomerTier
FROM CUSTOMERS;

Output

NAME	SALARY	CustomerTier
Ramesh	2000.00	Bronze
Khilan	1500.00	Bronze
Kaushik	2000.00	Bronze
Chaitali	6500.00	Gold
Hardik	8500.00	Gold
Komal	4500.00	Silver
Muffy	10000.00	Platinum

Using CASE with ORDER BY

CASE can create sophisticated sorting rules. For example, let’s sort customers by address, but ensure all customers from ‘Mumbai’ appear first.

SELECT NAME, ADDRESS, SALARY
FROM CUSTOMERS
ORDER BY
    CASE
        WHEN ADDRESS = 'Mumbai' THEN 1
        ELSE 2
    END, -- Primary sort key
    NAME ASC; -- Secondary sort key

Using CASE with GROUP BY

You can also group by the result of a CASE expression. This is useful for creating custom aggregation buckets.

SELECT
   CASE
      WHEN SALARY < 5000 THEN 'Entry Level'
      WHEN SALARY >= 5000 AND SALARY < 9000 THEN 'Mid Level'
      ELSE 'Senior Level'
   END AS SalaryBracket,
   COUNT(ID) AS NumberOfCustomers,
   AVG(SALARY) AS AverageSalary
FROM CUSTOMERS
GROUP BY SalaryBracket;

Output

SalaryBracket	NumberOfCustomers	AverageSalary
Entry Level	4	2500.00
Mid Level	2	7500.00
Senior Level	1	10000.00

Using CASE in an UPDATE Statement

CASE is perfect for conditional updates. Let’s give raises based on current salary.

UPDATE CUSTOMERS
SET SALARY =
    CASE
        WHEN SALARY < 2000 THEN SALARY * 1.15  -- 15% raise
        WHEN SALARY < 5000 THEN SALARY * 1.10  -- 10% raise
        ELSE SALARY * 1.05                     -- 5% raise
    END
WHERE ID > 0; -- A WHERE clause is always a good idea in UPDATEs

Verification

After running the update, you can select the data to see the new salaries.

SELECT NAME, SALARY FROM CUSTOMERS;

Common Pitfalls and Best Practices

• Data Type Consistency: All THEN and ELSE expressions in a CASE statement must return compatible data types. You cannot return a number in one THEN and a string in another.
• Don’t Forget ELSE: Omitting the ELSE clause will result in NULL for any rows that don’t meet a WHEN condition. This can be intentional, but it’s often a source of bugs. Be explicit.
• Order Matters: The WHEN conditions are evaluated in the order they are written. The first condition that evaluates to true determines the result, and subsequent conditions are ignored.
• NULL Handling: Comparisons with NULL (e.g., WHEN my_column = NULL) are tricky. The correct way to check for NULL is WHEN my_column IS NULL.