SQL Case

The CASE statement is SQL’s way of handling conditional logic. It functions like an if-then-else structure found in standard programming languages, allowing you to return specific values based on defined conditions.

You can use CASE inside SELECT, WHERE, ORDER BY, GROUP BY, INSERT, and UPDATE statements.

Basic Syntax

The CASE statement evaluates conditions sequentially from top to bottom. Once a condition evaluates to TRUE, it returns the corresponding result and stops reading further conditions (short-circuiting).

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result_else
END

WHEN ... THEN: Specifies the condition to evaluate and the value to return if true.
ELSE: Optional. Defines the default value if no WHEN conditions are met. If omitted and no conditions match, the CASE statement returns NULL.
END: Closes the CASE statement.

Types of `CASE` Expressions

1. Simple `CASE`

Compares a single expression against a series of static values. Best used for exact value matching.

SELECT OrderID, Quantity,
CASE Status
    WHEN 'P' THEN 'Pending'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    WHEN 'C' THEN 'Cancelled'
    ELSE 'Unknown Status'
END AS OrderStatusDescription
FROM Orders;

2. Searched `CASE`

Evaluates independent boolean conditions for each WHEN clause. This allows for complex comparisons using operators like >, <, BETWEEN, LIKE, or IS NULL.

SELECT EmployeeID, FirstName, Salary,
CASE
    WHEN Salary >= 100000 THEN 'Executive'
    WHEN Salary BETWEEN 60000 AND 99999 THEN 'Mid-Level'
    WHEN Salary < 60000 THEN 'Entry-Level'
    ELSE 'Unassigned'
END AS SalaryTier
FROM Employees;

Common Use Cases

1. Conditional Aggregation

You can nest CASE inside aggregate functions like COUNT() or SUM() to pivot data or calculate specific metrics in a single query.

Example: Count orders categorized by status in one row:

SELECT 
    COUNT(CASE WHEN Status = 'Completed' THEN 1 END) AS CompletedOrders,
    COUNT(CASE WHEN Status = 'Pending' THEN 1 END) AS PendingOrders,
    COUNT(CASE WHEN Status = 'Cancelled' THEN 1 END) AS CancelledOrders
FROM Orders;

2. Custom Sorting in `ORDER BY`

Use CASE to sort results in a non-alphabetical or non-numerical business order.

Example: Sort customers so that VIP members appear first, followed by Active, then Inactive:

SELECT CustomerID, CustomerName, AccountType
FROM Customers
ORDER BY 
    CASE AccountType
        WHEN 'VIP' THEN 1
        WHEN 'Active' THEN 2
        WHEN 'Inactive' THEN 3
        ELSE 4
    END, CustomerName ASC;

3. Dynamic Updates (`UPDATE` Statement)

Update multiple rows with different values using a single statement instead of writing multiple UPDATE queries.

UPDATE Products
SET Price = CASE CategoryID
    WHEN 1 THEN Price * 1.10  -- 10% price increase for Category 1
    WHEN 2 THEN Price * 1.05  -- 5% price increase for Category 2
    ELSE Price * 1.02         -- 2% price increase for all others
END;

Handling `NULL` with `CASE`

Because NULL = NULL evaluates to UNKNOWN in SQL, using a simple CASE to check for NULL values will fail. You must use a searched CASE with IS NULL.

-- INCORRECT: Will not catch NULL values
CASE MiddleName
    WHEN NULL THEN 'No Middle Name'
    ELSE MiddleName
END

-- CORRECT: Using Searched CASE with IS NULL
CASE 
    WHEN MiddleName IS NULL THEN 'No Middle Name'
    ELSE MiddleName
END

SQL Case

Basic Syntax

Types of CASE Expressions

1. Simple CASE

2. Searched CASE