PostgreSQL: CASE WHEN Multiple Conditions

I remember the first time I tried to classify employees into job levels based on salary and experience. I couldn’t use a simple IF/THEN—the logic had multiple conditions that all mattered. Do I look at salary first, or experience? What if salary is high but experience is low? PostgreSQL’s CASE WHEN statement lets you handle exactly this kind of multi-condition logic.

You can stack multiple WHEN clauses, combine conditions with AND/OR, and even nest cases for really complex scenarios. PostgreSQL evaluates them top-to-bottom and stops at the first match—which means you can create powerful classification logic that’s actually readable.

Quick Answer: Stack Multiple WHEN Clauses

To handle multiple conditions:

SELECT
    emp_id,
    salary,
    years_exp,
    CASE
        WHEN salary > 100000 AND years_exp > 5 THEN 'Senior'
        WHEN salary > 80000 AND years_exp > 3 THEN 'Mid-level'
        WHEN salary > 50000 THEN 'Junior'
        ELSE 'Trainee'
    END as job_level
FROM employees;

Returns:

emp_id | salary  | years_exp | job_level
-------|---------|-----------|----------
1      | 120000  | 8         | Senior
2      | 85000   | 5         | Mid-level
3      | 65000   | 2         | Junior
4      | 45000   | 0         | Trainee

PostgreSQL evaluates each WHEN clause from top to bottom and returns the first one that’s true. Everything after the match is ignored—which is exactly how you want cascading logic to work.

How It Works

Here’s the critical thing to understand: CASE WHEN doesn’t just evaluate all conditions at once. It uses short-circuit evaluation. PostgreSQL checks conditions in order, and the moment one is true, it returns that result and stops.

This means:

  • Order matters. If you put the most general condition first, more specific ones below it might never execute.
  • You can safely put conditions that might fail early (like division checks) in later WHEN clauses.
  • Performance-wise, it’s efficient because PostgreSQL doesn’t check unnecessary conditions.

The ELSE clause is your safety net—it handles any row that doesn’t match any WHEN condition. If you don’t include an ELSE, rows that match no conditions return NULL.

Different Approaches & When to Use Them

Approach 1: Multiple AND Conditions (Most Common)

When you need all conditions to be true together:

SELECT
    product_id,
    price,
    inventory,
    CASE
        WHEN price > 100 AND inventory > 50 THEN 'Premium Stock'
        WHEN price > 50 AND inventory > 20 THEN 'Standard Stock'
        WHEN price <= 50 THEN 'Budget Item'
        ELSE 'Low Stock'
    END as product_category
FROM products;

This is the pattern I use most often. Each WHEN has multiple conditions that all must be true. It’s clean, readable, and reflects real-world business logic where multiple factors determine a classification.

Approach 2: Mix AND with OR for Complex Logic

Sometimes you need to say “match if (A AND B) OR C”:

SELECT
    customer_id,
    account_age_days,
    purchase_count,
    total_spent,
    CASE
        WHEN (account_age_days > 365 AND purchase_count > 10) OR total_spent > 5000 THEN 'VIP'
        WHEN (account_age_days > 180 AND purchase_count > 5) OR total_spent > 1000 THEN 'Regular'
        WHEN account_age_days > 30 THEN 'Active'
        ELSE 'New Customer'
    END as customer_tier
FROM customers;

This is more complex, but the logic is: someone is a VIP if they’re both old and active, or if they’ve spent big money. That’s real business classification.

Approach 3: Check for NULL and Edge Cases

I’ve learned this the hard way: always check for NULL early if you’re going to use a value later:

SELECT
    order_id,
    discount_code,
    order_total,
    CASE
        WHEN discount_code IS NULL THEN order_total
        WHEN discount_code = 'SPECIAL50' THEN ROUND(order_total * 0.5, 2)
        WHEN discount_code = 'SAVE20' THEN ROUND(order_total * 0.8, 2)
        ELSE order_total
    END as final_price
FROM orders;

The NULL check comes first. This prevents errors and makes your logic explicit. You’re saying: “If there’s no code, use full price. If there’s a code, apply discount.” It’s defensive programming.

Approach 4: Nested CASE for Deep Classification

When your logic is really complex and depends on prior decisions, you can nest CASE statements:

SELECT
    employee_id,
    department,
    salary,
    performance_rating,
    CASE
        WHEN department IN ('Engineering', 'Product') THEN
            CASE
                WHEN performance_rating = 'Exceeds' AND salary < 120000 THEN 'Raise Candidate'
                WHEN performance_rating = 'Meets' THEN 'Stable'
                ELSE 'Review'
            END
        WHEN department = 'Sales' THEN
            CASE
                WHEN salary > 80000 AND performance_rating = 'Exceeds' THEN 'Leadership Track'
                ELSE 'Standard Review'
            END
        ELSE 'Other Department'
    END as salary_action
FROM employees;

This is more complex, but sometimes business logic really is nested. “For engineers, if they exceed and earn below X, they’re raise candidates. For sales, if they earn above Y and exceed, they’re leadership track.” That’s the reality you’re capturing.

Real-World Example: Customer Risk & Opportunity Assessment

Let me show you a practical scenario from a lending platform. You’re scoring customers for both risk and opportunity:

SELECT
    customer_id,
    credit_score,
    annual_income,
    delinquencies,
    existing_debt,
    account_age_months,
    CASE
        -- High risk: poor credit and delinquencies
        WHEN credit_score < 600 AND delinquencies > 2 THEN 'High Risk - Decline'
        -- Medium-high risk: fair credit with issues
        WHEN credit_score < 650 AND delinquencies > 0 THEN 'Medium-High Risk - Review'
        -- Medium risk: mixed signals
        WHEN (credit_score < 700 OR annual_income < 30000) AND existing_debt > 50000 THEN 'Medium Risk - Limit'
        -- Good candidates: good credit, stable income, clean history
        WHEN credit_score >= 750 AND delinquencies = 0 AND account_age_months > 24 THEN 'Approved - Standard'
        -- Excellent candidates: all good metrics
        WHEN credit_score >= 800 AND annual_income > 100000 AND existing_debt < 20000 THEN 'Approved - Premium'
        -- Default
        ELSE 'Review - Standard'
    END as approval_decision,
    CASE
        WHEN credit_score >= 750 AND annual_income > 75000 THEN 'High'
        WHEN credit_score >= 700 OR annual_income > 50000 THEN 'Medium'
        ELSE 'Low'
    END as opportunity_score
FROM customers
ORDER BY approval_decision, customer_id;

This shows real multi-condition logic: you’re not just checking one thing, you’re combining multiple factors to make a business decision. The beauty is it’s all readable and maintainable.

Common Errors & Solutions

Error: “First WHEN clause always matches, later ones never execute” You probably put your most general condition first. WHEN salary > 0 matches almost everything. Reorder from most specific to most general. Put WHEN salary > 100000 AND years_exp > 5 before WHEN salary > 50000.

Error: “Getting NULL where I expect a value” You probably didn’t include ELSE. If no WHEN matches, CASE returns NULL. Always add ELSE 'default_value' unless NULL is intentional.

Error: “Results seem inconsistent or wrong” Check your AND/OR precedence. a AND b OR c is evaluated as (a AND b) OR c, not a AND (b OR c). Use parentheses to be explicit: WHEN (a AND b) OR c.

FAQ

Q: How many WHEN clauses can I have? A: Technically unlimited, but realistically after 10-15 clauses, consider breaking logic into separate queries or functions. Long CASE statements get hard to maintain.

Q: What’s the performance impact? A: Negligible for typical CASE statements (5-10 conditions). PostgreSQL optimizes short-circuit evaluation. Once a WHEN matches, it stops checking. For hundreds of conditions, you might notice it, but that’s rarely needed.

Q: Can I use CASE in a WHERE clause? A: Yes, but differently than SELECT. WHERE CASE WHEN x > 10 THEN true ELSE false END works but is awkward. Better to use the condition directly: WHERE x > 10. Use CASE in WHERE only when you’re actually outputting the CASE result.

Q: Can I ORDER BY or GROUP BY a CASE result? A: Yes! ORDER BY (CASE WHEN...) DESC or GROUP BY (CASE WHEN...) both work perfectly. This is often how you implement custom sort orders.

Q: What if I need to use a CASE result in another CASE? A: You can nest CASE statements (shown above) or use subqueries/CTEs. Nested cases are readable for 2-3 levels; beyond that, consider a CTE.

Q: How do I handle complex logic like “if value between X and Y”? A: Use AND: WHEN value > 100 AND value <= 200 THEN 'In Range'. Don’t do WHEN value BETWEEN 100 AND 200—okay, actually that works too and is cleaner.

Q: Can I use functions inside WHEN conditions? A: Absolutely. WHEN DATE_TRUNC('year', order_date) = CURRENT_DATE THEN or WHEN LENGTH(name) > 20 THEN both work great.

Q: What about performance—should I avoid complex CASE statements? A: They’re fast. But if you’re doing the same CASE logic in WHERE and SELECT, consider putting it in a stored function. That’s optimization territory though—get it working first.

Q: How’s CASE WHEN different from IF/ELSE in other languages? A: Very similar conceptually, but SQL’s CASE is expression-based. It returns a value, not control flow. You can use it anywhere you use a value: SELECT, WHERE (kinda), ORDER BY, INSERT, UPDATE, and even inside functions.

Q: Can I use regex or string matching in WHEN conditions? A: Yes! WHEN name ~ '^J.*' THEN for regex, or WHEN name LIKE 'J%' THEN for pattern matching. Full power of PostgreSQL operators is available.

Q: What happens if I don’t include ELSE? A: Rows that match no WHEN condition return NULL. This is intentional—you’re saying “I don’t have a classification for this.” Add ELSE 'Unknown' if you want a default.

Performance Tips

  • Order by frequency: Put the most common conditions first. Short-circuit evaluation means PostgreSQL skips later conditions once one matches.
  • Specific before general: WHEN salary > 100000 AND years > 5 should come before WHEN salary > 50000. Otherwise the second never triggers.
  • Avoid in WHERE when possible: WHERE CASE WHEN x THEN true ELSE false END doesn’t use indexes well. Use WHERE x directly instead.
  • Use in SELECT and ORDER BY freely: These are CASE’s natural home. SELECT CASE... ORDER BY CASE... are both fast.
  • Consider functions for reuse: If you use the same CASE logic multiple times, create a stored function. You’ll write it once, maintain it in one place.

Wrapping Up

CASE WHEN with multiple conditions is one of SQL’s most powerful features. You’re not limited to simple binary logic—you can express complex business rules in a readable, maintainable way. Remember: order matters (most specific first), short-circuit evaluation means it’s efficient, and you can nest or combine conditions however your logic requires.