Home / SQL / CASE

CASE

Overview

The CASE expression in SQL allows you to add conditional logic to your queries. It works like an IF-THEN-ELSE statement, allowing you to return different values based on conditions.

CASE is useful for creating calculated columns, categorizing data, and handling conditional logic directly in SQL queries.

Basic Syntax

CASE has two forms: Simple CASE and Searched CASE.

Simple CASE

Simple CASE Syntax

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

Searched CASE

Searched CASE Syntax

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

Examples

Example 1: Simple CASE

Categorize by status

SELECT 
    order_id,
    status,
    CASE status
        WHEN 'Pending' THEN 'Not Started'
        WHEN 'Processing' THEN 'In Progress'
        WHEN 'Shipped' THEN 'Completed'
        WHEN 'Delivered' THEN 'Completed'
        ELSE 'Unknown'
    END AS status_category
FROM Orders;

Example 2: Searched CASE

Price range categories

SELECT 
    product_name,
    price,
    CASE
        WHEN price < 50 THEN 'Budget'
        WHEN price < 200 THEN 'Standard'
        WHEN price < 500 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_category
FROM Products;

Example 3: CASE with Aggregates

Conditional aggregates

SELECT 
    COUNT(*) AS total_orders,
    SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN status = 'Shipped' THEN 1 ELSE 0 END) AS shipped_orders,
    SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered_orders
FROM Orders;

Example 4: CASE in WHERE

Filter with CASE (limited use)

SELECT *
FROM Products
WHERE CASE 
    WHEN category = 'Electronics' THEN price < 500
    WHEN category = 'Books' THEN price < 50
    ELSE price < 100
END = TRUE;

Example 5: CASE with NULL Handling

Handle NULL values

SELECT 
    name,
    phone,
    CASE 
        WHEN phone IS NULL THEN 'No Phone'
        ELSE phone
    END AS contact_phone
FROM Customers;

ELSE Clause

The ELSE clause is optional. If no conditions match and there's no ELSE, CASE returns NULL:

Without ELSE

SELECT 
    status,
    CASE status
        WHEN 'Pending' THEN 'Not Started'
        WHEN 'Shipped' THEN 'Completed'
        -- No ELSE: returns NULL for other statuses
    END AS status_category
FROM Orders;

Nested CASE

You can nest CASE statements, though complex nesting can be hard to read:

Nested CASE

SELECT 
    price,
    CASE 
        WHEN price < 100 THEN 'Low'
        WHEN price >= 100 THEN 
            CASE 
                WHEN price < 500 THEN 'Medium'
                ELSE 'High'
            END
    END AS price_level
FROM Products;

CASE vs IF Functions

Some databases have IF functions, but CASE is standard SQL:

CASE vs IF (MySQL)

-- CASE (Standard SQL)
SELECT CASE WHEN price > 100 THEN 'Expensive' ELSE 'Cheap' END
FROM Products;

-- IF (MySQL specific)
SELECT IF(price > 100, 'Expensive', 'Cheap')
FROM Products;

Best Practices

Always use ELSE: Prevents unexpected NULL values
Keep it simple: Avoid deep nesting when possible
Use searched CASE for ranges: More flexible than simple CASE
Document logic: Comment complex CASE expressions
Test thoroughly: Verify all conditions work correctly

Common Use Cases

Creating calculated columns with conditional logic
Categorizing or grouping data
Handling NULL values elegantly
Conditional aggregates
Data transformation and normalization

Next Steps

Learn about handling NULL values with NULL Functions, or explore creating database objects with CREATE DATABASE.

← Exists Create Db →