CASE WHEN is SQL's if-then-else logic. Use it to create calculated columns, categorize data, or handle conditional updates.

Basic Syntax

SELECT name, salary,
    CASE
        WHEN salary < 50000 THEN 'Junior'
        WHEN salary < 100000 THEN 'Mid-level'
        WHEN salary < 150000 THEN 'Senior'
        ELSE 'Executive'
    END as salary_tier
FROM employees;

Simple CASE vs Searched CASE

Simple CASE (equality checks only)

SELECT order_id,
    CASE status
        WHEN 'pending' THEN 'Order Received'
        WHEN 'processing' THEN 'Being Prepared'
        WHEN 'shipped' THEN 'On The Way'
        ELSE 'Delivered'
    END as status_message
FROM orders;

Searched CASE (complex conditions)

SELECT product_name, stock_quantity,
    CASE
        WHEN stock_quantity = 0 THEN 'Out of Stock'
        WHEN stock_quantity < 10 THEN 'Low Stock'
        WHEN stock_quantity < 50 THEN 'In Stock'
        ELSE 'Well Stocked'
    END as inventory_status
FROM products;

Practical Use Cases

Conditional Aggregation

SELECT department,
    COUNT(*) as total_employees,
    SUM(CASE WHEN salary > 80000 THEN 1 ELSE 0 END) as high_earners,
    AVG(CASE WHEN hire_date >= '2024-01-01' THEN salary END) as avg_new_hire_salary
FROM employees
GROUP BY department;

Pivot Data

SELECT customer_id,
    SUM(CASE WHEN product_category = 'Electronics' THEN amount ELSE 0 END) as electronics_total,
    SUM(CASE WHEN product_category = 'Clothing' THEN amount ELSE 0 END) as clothing_total,
    SUM(CASE WHEN product_category = 'Books' THEN amount ELSE 0 END) as books_total
FROM orders
GROUP BY customer_id;

Handle NULL Values

SELECT name,
    CASE
        WHEN phone IS NULL THEN email
        ELSE phone
    END as primary_contact
FROM customers;