Advanced SQL

CASE Expression

Add conditional logic to SQL queries with the CASE expression for dynamic results, categorization, and pivoting.

Conditional Logic — Implement if-then-else branching directly in queries
Data Pivoting — Transform rows into columns with conditional aggregation CASE is SQL's answer to the if-else statement — it lives inside your queries.

What Is a CASE Expression?

DfCASE Expression

A conditional expression that evaluates a set of conditions and returns a value for the first condition that evaluates to TRUE. If no condition matches, the ELSE clause is returned. If no ELSE is specified, NULL is returned.

-- Basic CASE structure
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN condition3 THEN result3
    ELSE default_result
END

CASE is an expression, not a statement. It can appear anywhere a value is expected: SELECT, WHERE, ORDER BY, GROUP BY, and UPDATE SET clauses.

Searched CASE (Most Common)

DfSearched CASE

Evaluates a series of boolean conditions in order. The first TRUE condition determines the result. Conditions are checked top-to-bottom, so order matters.

-- Categorize employees by salary level
SELECT
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary >= 150000 THEN 'Executive'
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 70000 THEN 'Mid-Level'
        WHEN salary >= 40000 THEN 'Junior'
        ELSE 'Entry'
    END AS salary_tier
FROM employees;

-- Assign priority levels to support tickets
SELECT
    ticket_id,
    subject,
    CASE
        WHEN status = 'open' AND priority = 'critical' THEN 'Immediate'
        WHEN status = 'open' AND priority = 'high' THEN 'Urgent'
        WHEN status = 'open' AND priority = 'medium' THEN 'Standard'
        WHEN status = 'pending' THEN 'Awaiting Response'
        WHEN status = 'resolved' THEN 'Closed'
        ELSE 'Other'
    END AS action_needed
FROM support_tickets;

Simple CASE (Exact Match)

DfSimple CASE

Compares a single expression against a list of possible values. Works like a switch statement — only handles equality checks, not inequalities or complex conditions.

-- Simple CASE for exact value matching
SELECT
    first_name,
    last_name,
    department,
    CASE department
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Marketing' THEN 'Growth'
        WHEN 'Sales' THEN 'Revenue'
        WHEN 'HR' THEN 'People'
        WHEN 'Finance' THEN 'Numbers'
        ELSE 'Other'
    END AS dept_group
FROM employees;

-- Convert status codes to readable labels
SELECT
    order_id,
    CASE status
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        WHEN 'R' THEN 'Returned'
        ELSE 'Unknown'
    END AS order_status
FROM orders;

Simple vs Searched CASE

Feature	Simple CASE	Searched CASE
Syntax	`CASE expr WHEN val THEN...`	`CASE WHEN condition THEN...`
Comparison	Equality only	Any boolean condition
Flexibility	Limited	Full comparison operators
Multiple columns	Not supported	Supported
Readability	Good for single column	Better for complex logic

CASE in WHERE Clauses

-- Filter using conditional logic
SELECT
    first_name,
    last_name,
    salary,
    department
FROM employees
WHERE CASE
    WHEN department = 'Engineering' THEN salary > 80000
    WHEN department = 'Sales' THEN salary > 60000
    ELSE salary > 50000
END;

-- Dynamic date range filtering
SELECT
    order_id,
    order_date,
    amount
FROM orders
WHERE order_date >= CASE
    WHEN @period = 'week' THEN CURRENT_DATE - INTERVAL '7' DAY
    WHEN @period = 'month' THEN CURRENT_DATE - INTERVAL '1' MONTH
    WHEN @period = 'quarter' THEN CURRENT_DATE - INTERVAL '3' MONTH
    WHEN @period = 'year' THEN CURRENT_DATE - INTERVAL '1' YEAR
    ELSE CURRENT_DATE - INTERVAL '30' DAY
END;

Using CASE in WHERE clauses can sometimes prevent index usage. If performance is critical, consider separate queries or application-level filtering.

CASE in ORDER BY

-- Custom sort order using CASE
SELECT
    product_name,
    category,
    price
FROM products
ORDER BY
    CASE
        WHEN category = 'Electronics' THEN 1
        WHEN category = 'Clothing' THEN 2
        WHEN category = 'Books' THEN 3
        WHEN category = 'Home' THEN 4
        ELSE 5
    END,
    price DESC;

-- Sort by status priority, then by date
SELECT
    ticket_id,
    subject,
    status,
    created_at
FROM support_tickets
ORDER BY
    CASE status
        WHEN 'critical' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
        ELSE 5
    END,
    created_at DESC;

CASE in UPDATE Statements

-- Apply tiered raises based on current salary
UPDATE employees
SET salary = salary * CASE
    WHEN salary < 40000 THEN 1.15
    WHEN salary < 70000 THEN 1.10
    WHEN salary < 100000 THEN 1.08
    ELSE 1.05
END;

-- Update order status based on conditions
UPDATE orders
SET status = CASE
    WHEN shipped_date IS NOT NULL AND delivered_date IS NULL THEN 'In Transit'
    WHEN delivered_date IS NOT NULL THEN 'Delivered'
    WHEN cancelled_date IS NOT NULL THEN 'Cancelled'
    ELSE status
END
WHERE status != 'Delivered';

Always test your UPDATE with a SELECT first. CASE in UPDATE applies to every matched row — incorrect logic can corrupt data across your entire table.

CASE for Data Pivoting

DfConditional Aggregation

Use CASE inside aggregate functions to pivot rows into columns. Each CASE produces a 1 or 0, and SUM/COUNT aggregates them into column totals.

-- Pivot monthly orders into columns
SELECT
    customer_id,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN 1 ELSE 0 END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN 1 ELSE 0 END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN 1 ELSE 0 END) AS mar,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4 THEN 1 ELSE 0 END) AS apr,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 5 THEN 1 ELSE 0 END) AS may,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 6 THEN 1 ELSE 0 END) AS jun
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2026
GROUP BY customer_id;

-- Pivot department headcount by gender
SELECT
    department,
    COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS male_count,
    COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS female_count,
    COUNT(CASE WHEN gender NOT IN ('Male', 'Female') THEN 1 END) AS other_count
FROM employees
GROUP BY department;

CASE with NULL Handling

DfNULLIF and COALESCE

NULLIF(a, b) returns NULL if a equals b, otherwise returns a. COALESCE(a, b) returns the first non-NULL value. Both work with CASE for clean NULL handling.

-- Handle NULL values gracefully
SELECT
    first_name,
    last_name,
    COALESCE(phone, 'No Phone') AS phone,
    COALESCE(email, 'No Email') AS email,
    CASE
        WHEN commission IS NULL THEN 'No Commission'
        WHEN commission = 0 THEN 'Zero Commission'
        ELSE CAST(commission AS VARCHAR)
    END AS commission_status
FROM employees;

-- Safe division using NULLIF
SELECT
    product_name,
    total_revenue,
    total_units,
    ROUND(total_revenue / NULLIF(total_units, 0), 2) AS price_per_unit
FROM product_stats;

Nested and Complex CASE

-- Nested CASE for multi-level categorization
SELECT
    first_name,
    last_name,
    salary,
    department,
    CASE
        WHEN department IN ('Engineering', 'IT') THEN
            CASE
                WHEN salary >= 120000 THEN 'Senior Tech'
                WHEN salary >= 80000 THEN 'Mid Tech'
                ELSE 'Junior Tech'
            END
        WHEN department IN ('Sales', 'Marketing') THEN
            CASE
                WHEN salary >= 100000 THEN 'Senior Business'
                WHEN salary >= 60000 THEN 'Mid Business'
                ELSE 'Junior Business'
            END
        ELSE 'Other'
    END AS role_tier
FROM employees;

-- Multi-condition scoring
SELECT
    order_id,
    customer_id,
    amount,
    (CASE WHEN amount >= 500 THEN 5 ELSE 0 END) +
    (CASE WHEN order_date >= CURRENT_DATE - INTERVAL '30' DAY THEN 3 ELSE 0 END) +
    (CASE WHEN customer_id IN (SELECT customer_id FROM vip_customers) THEN 2 ELSE 0 END) AS priority_score
FROM orders
ORDER BY priority_score DESC;

CASE in Aggregate Functions

-- Count employees in different salary bands per department
SELECT
    department,
    COUNT(*) AS total_employees,
    COUNT(CASE WHEN salary >= 100000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN salary BETWEEN 60000 AND 99999 THEN 1 END) AS mid_earners,
    COUNT(CASE WHEN salary < 60000 THEN 1 END) AS low_earners,
    ROUND(
        COUNT(CASE WHEN salary >= 100000 THEN 1 END) * 100.0 / COUNT(*), 1
    ) AS pct_high_earners
FROM employees
GROUP BY department;

-- Conditional sum for revenue analysis
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS total_revenue,
    SUM(CASE WHEN amount >= 100 THEN amount ELSE 0 END) AS large_order_rev,
    SUM(CASE WHEN amount < 100 THEN amount ELSE 0 END) AS small_order_rev,
    COUNT(CASE WHEN amount >= 100 THEN 1 END) AS large_order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Conditional aggregation with CASE is often faster than using separate WHERE clauses for each category, because it scans the table only once.

Practical Examples

Employee Report Card

-- Generate performance categories
SELECT
    e.first_name,
    e.last_name,
    e.department,
    e.salary,
    e.hire_date,
    CASE
        WHEN e.salary > d.avg_salary * 1.3 THEN 'Exceeds Expectations'
        WHEN e.salary > d.avg_salary * 1.1 THEN 'Above Average'
        WHEN e.salary >= d.avg_salary * 0.9 THEN 'Meets Expectations'
        ELSE 'Below Average'
    END AS performance_rating,
    CASE
        WHEN AGE(CURRENT_DATE, e.hire_date) > INTERVAL '5' years THEN 'Veteran'
        WHEN AGE(CURRENT_DATE, e.hire_date) > INTERVAL '2' years THEN 'Experienced'
        ELSE 'New Hire'
    END AS tenure_category
FROM employees e
JOIN (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) d ON e.department = d.department;

Dynamic Report Columns

-- Create a flexible status report
SELECT
    p.product_name,
    p.category,
    p.price,
    CASE
        WHEN p.stock_quantity = 0 THEN 'Out of Stock'
        WHEN p.stock_quantity < 10 THEN 'Low Stock'
        WHEN p.stock_quantity < 50 THEN 'In Stock'
        ELSE 'Well Stocked'
    END AS inventory_status,
    CASE
        WHEN p.last_sold >= CURRENT_DATE - INTERVAL '7' DAY THEN 'Fast Mover'
        WHEN p.last_sold >= CURRENT_DATE - INTERVAL '30' DAY THEN 'Regular'
        WHEN p.last_sold >= CURRENT_DATE - INTERVAL '90' DAY THEN 'Slow Mover'
        ELSE 'Stale'
    END AS velocity,
    CASE
        WHEN p.price >= 100 THEN 'Premium'
        WHEN p.price >= 50 THEN 'Standard'
        ELSE 'Budget'
    END AS price_tier
FROM products p
ORDER BY p.category, p.price DESC;

Summary

Key Takeaways

Searched CASE (CASE WHEN condition THEN...) is more flexible than simple CASE (CASE expr WHEN value THEN...).
CASE evaluates top-to-bottom — put the most specific conditions first.
CASE can appear in SELECT, WHERE, ORDER BY, GROUP BY, and UPDATE SET clauses.
Use CASE inside COUNT/SUM for conditional aggregation — it pivots rows into columns.
Always include an ELSE clause to handle unexpected values; otherwise NULL is returned.
Nested CASE works but becomes hard to read — consider CTEs or window functions for complex logic.

CASE Expression

CASE Expression

What Is a CASE Expression?

DfCASE Expression

Searched CASE (Most Common)

DfSearched CASE

Simple CASE (Exact Match)

DfSimple CASE

Simple vs Searched CASE

CASE in WHERE Clauses

CASE in ORDER BY

CASE in UPDATE Statements

CASE for Data Pivoting

DfConditional Aggregation

CASE with NULL Handling

DfNULLIF and COALESCE

Nested and Complex CASE

CASE in Aggregate Functions

Practical Examples

Employee Report Card

Dynamic Report Columns

Summary

Key Takeaways

Premium Content

Need Expert SQL Help?