CASE Expressions: Conditional Logic in SQL

The CASE expression brings if/else logic directly into your SQL queries. It evaluates conditions and returns different values based on which condition matches.

There are two forms: simple CASE and searched CASE. Here's the searched CASE (more flexible and commonly used):

SELECT OrderId,
       TotalAmount,
       CASE
           WHEN TotalAmount < 50 THEN 'Small'
           WHEN TotalAmount BETWEEN 50 AND 200 THEN 'Medium'
           WHEN TotalAmount > 200 THEN 'Large'
           ELSE 'Unknown'
       END AS OrderSize
FROM Orders;

For each order, SQL evaluates the WHEN conditions from top to bottom. When it finds the first true condition, it returns that result and stops checking. If no conditions match, it returns the ELSE value (or NULL if no ELSE is specified).

The result adds a categorization column:

OrderId | TotalAmount | OrderSize
--------|-------------|----------
1001    | 35.00       | Small
1002    | 150.00      | Medium
1003    | 450.00      | Large
1004    | 75.00       | Medium

You can use CASE anywhere you'd use a regular value – in SELECT, WHERE, ORDER BY, or even inside aggregate functions:

SELECT
    COUNT(CASE WHEN Status = 'Completed' THEN 1 END) AS CompletedCount,
    COUNT(CASE WHEN Status = 'Pending' THEN 1 END) AS PendingCount,
    COUNT(CASE WHEN Status = 'Cancelled' THEN 1 END) AS CancelledCount
FROM Orders;

This creates a pivot-style report showing order counts by status – all in one query instead of three separate COUNT queries.

In test validation, CASE helps you categorize test results, calculate conditional metrics, or flag anomalies based on complex business rules.

CASE in WHERE and ORDER BY

Beyond just creating calculated columns, CASE enables sophisticated filtering and sorting logic that would otherwise require multiple queries.

Using CASE in WHERE for complex conditions:

SELECT ProductId, Name, Price, CategoryId
FROM Products
WHERE CASE
    WHEN CategoryId = 1 THEN Price < 100
    WHEN CategoryId = 2 THEN Price < 200
    ELSE Price < 50
END;

This applies different price thresholds based on category – products in Category 1 must be under $100, Category 2 under $200, and all others under $50. Expressing this without CASE would require multiple OR conditions and become unwieldy.

Using CASE in ORDER BY for custom sorting:

SELECT OrderId, Status, OrderDate, TotalAmount
FROM Orders
ORDER BY
    CASE Status
        WHEN 'Pending' THEN 1
        WHEN 'Processing' THEN 2
        WHEN 'Shipped' THEN 3
        WHEN 'Completed' THEN 4
        ELSE 5
    END,
    OrderDate DESC;

This sorts orders by a custom priority (pending first, then processing, then shipped, etc.) and within each status group, sorts by date descending. You're controlling the sort order through conditional logic rather than relying on alphabetical or numeric ordering.

COALESCE: Handling NULL Values Elegantly

The COALESCE function returns the first non-NULL value from a list of arguments. It's perfect for providing default values when data might be missing:

SELECT OrderId,
       Status,
       ShipmentDate,
       COALESCE(ShipmentDate, 'Not yet shipped') AS ShipmentStatus
FROM Orders;

For orders where ShipmentDate is NULL, COALESCE returns the default text 'Not yet shipped'. If ShipmentDate has a value, it returns that value instead.

You can provide multiple fallback options. COALESCE evaluates each argument left to right and returns the first non-NULL it encounters:

SELECT UserId,
       COALESCE(FirstName, Email, 'Unknown User') AS DisplayName
FROM Users;

This tries to use FirstName as the display name. If that's NULL, it falls back to Email. If both are NULL (which shouldn't happen in a well-designed schema, but defensive code handles edge cases), it uses 'Unknown User'.

A common pattern – ensuring aggregate functions return 0 instead of NULL when no rows match:

SELECT u.UserId,
       u.Email,
       COALESCE(SUM(o.TotalAmount), 0) AS TotalSpent
FROM Users u
LEFT JOIN Orders o ON u.UserId = o.UserId
GROUP BY u.UserId, u.Email;

Without COALESCE, users with no orders would show NULL for TotalSpent. With it, they show a clean 0, which is more appropriate for display and calculations.

Use COALESCE anywhere NULL values might cause display issues, calculation errors, or confusing test results.

NULLIF: Converting Values to NULL

While COALESCE replaces NULL with a value, NULLIF does the opposite – it returns NULL when two expressions are equal, otherwise returns the first expression.

NULLIF(expression1, expression2)

This might seem odd at first, but it's incredibly useful for data cleaning. Consider products with empty string descriptions that should really be NULL:

SELECT ProductId,
       Name,
       NULLIF(Description, '') AS CleanDescription
FROM Products;

If Description is an empty string, NULLIF returns NULL. If it contains actual text, it returns that text unchanged. This normalizes "no description" data to consistently be NULL rather than a mix of NULL and empty strings.

Another practical use – avoiding division-by-zero errors:

SELECT ProductId,
       Name,
       Price,
       StockQuantity,
       Price / NULLIF(StockQuantity, 0) AS PricePerUnit
FROM Products;

If StockQuantity is 0, NULLIF converts it to NULL, and dividing by NULL produces NULL (rather than an error). Products with stock show a valid price-per-unit; products with zero stock show NULL.

You can combine NULLIF and COALESCE for defensive calculations:

SELECT
    CategoryId,
    COUNT(*) AS ProductCount,
    SUM(Price) AS TotalValue,
    COALESCE(SUM(Price) / NULLIF(COUNT(*), 0), 0) AS AvgPrice
FROM Products
GROUP BY CategoryId;

This ensures the average calculation never fails, even if a category somehow has zero products (it returns 0 instead of an error or NULL).