All Articles

CASE WHEN in SQL: Beyond the Basics

Fundamentals
#case-when#conditional-logic#conditional-aggregation#sql-basics#sql-interview

CASE WHEN: More Than an If-Statement

CASE WHEN is SQL's conditional expression — most people learn it as a simple if/else for labeling rows, but it's actually one of the most versatile tools in SQL, showing up inside aggregates for conditional counting, inside ORDER BY for custom sort logic, and as the standard mechanism behind pivoting. This guide goes past the basic syntax into the patterns that come up constantly in real reporting and interview questions.

Basic Syntax: Simple and Searched CASE

-- Simple CASE: compares one expression against several possible values
SELECT name,
  CASE department
    WHEN 'ENG' THEN 'Engineering'
    WHEN 'MKT' THEN 'Marketing'
    ELSE 'Other'
  END AS department_name
FROM employees;

-- Searched CASE: evaluates independent boolean conditions — far more flexible
SELECT name, salary,
  CASE
    WHEN salary >= 150000 THEN 'Senior'
    WHEN salary >= 90000 THEN 'Mid'
    ELSE 'Junior'
  END AS level
FROM employees;

The searched form (CASE WHEN condition THEN ...) is used far more often in practice, since it supports arbitrary boolean logic rather than just equality against a single expression. Order matters — conditions are evaluated top to bottom, and the first match wins, so put the most specific or highest-priority condition first.

Conditional Aggregation: CASE Inside SUM/COUNT/AVG

This is arguably the single highest-value CASE WHEN pattern — turning multiple rows into multiple conditional counts or sums within one GROUP BY, without needing separate queries.

SELECT
  department,
  COUNT(*) AS total_employees,
  SUM(CASE WHEN salary > 100000 THEN 1 ELSE 0 END) AS high_earners,
  AVG(CASE WHEN gender = 'F' THEN salary END) AS avg_female_salary
FROM employees
GROUP BY department;

Note the third line: leaving off the ELSE clause means non-matching rows evaluate to NULL, which AVG() automatically ignores — so avg_female_salary correctly averages only the matching rows, not all rows in the group. This is the exact same mechanism behind manual pivoting; see our pivot and unpivot guide for the full pattern.

CASE Inside WHERE and HAVING

-- Conditional filtering logic based on another column's value
SELECT * FROM orders
WHERE
  CASE
    WHEN order_type = 'international' THEN shipping_cost > 50
    ELSE shipping_cost > 20
  END;

This pattern is less common than conditional aggregation but useful when a filter's threshold genuinely depends on another column — though for anything beyond a couple of branches, it's often clearer to just write two separate OR-connected conditions instead.

CASE Inside ORDER BY: Custom Sort Logic

-- Sort by a custom priority order that isn't alphabetical or numeric
SELECT * FROM tickets
ORDER BY
  CASE priority
    WHEN 'urgent' THEN 1
    WHEN 'high' THEN 2
    WHEN 'medium' THEN 3
    WHEN 'low' THEN 4
  END;

Without this, sorting the text values 'urgent', 'high', 'medium', 'low' alphabetically would produce a meaningless order — mapping each to a numeric priority inside CASE lets you sort by real business priority instead of string order.

CASE for NULL-Safe Bucketing

SELECT
  CASE
    WHEN age IS NULL THEN 'Unknown'
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 64 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group,
  COUNT(*)
FROM users
GROUP BY 1;

Handling NULL as an explicit first branch (rather than letting it fall through unexpectedly) is a good habit — without it, a NULL age would silently fall through every numeric comparison (since NULL < 18 is UNKNOWN, not TRUE) and land in the ELSE bucket, which is usually not what you want. See our NULL handling guide for the full logic behind this.

Nested CASE Expressions

SELECT
  name,
  CASE
    WHEN department = 'Engineering' THEN
      CASE WHEN salary > 150000 THEN 'Senior Engineer' ELSE 'Engineer' END
    WHEN department = 'Sales' THEN
      CASE WHEN salary > 120000 THEN 'Senior Sales' ELSE 'Sales' END
    ELSE 'Other'
  END AS title
FROM employees;

Nesting works but readability degrades fast — beyond two levels, most engineers switch to a lookup table joined in, or break the logic into a CTE with intermediate labeled columns instead of one deeply nested expression.

CASE vs. COALESCE vs. NULLIF

COALESCE and NULLIF (covered in our NULL handling guide) are technically both shorthand for specific, common CASE patterns:

-- COALESCE(a, b) is equivalent to:
CASE WHEN a IS NOT NULL THEN a ELSE b END

-- NULLIF(a, b) is equivalent to:
CASE WHEN a = b THEN NULL ELSE a END

Knowing this equivalence is a good interview signal — it shows you understand these aren't separate magic keywords, but readable shortcuts for patterns CASE could also express directly.

Common Mistakes

  • Forgetting ELSE when a default is actually needed — an unmatched row silently returns NULL instead of erroring, which can be surprising downstream if not intentional.
  • Wrong condition order — since the first matching WHEN wins, an overly broad condition placed too early can silently shadow more specific conditions listed after it.
  • Not handling NULL explicitly in a chain of comparisons — a NULL value falls through every numeric/string comparison silently (since they all evaluate to UNKNOWN) and lands in ELSE, often unintentionally.
  • Over-nesting CASE expressions instead of extracting the logic into a CTE or a joined lookup table once it gets more than 2 levels deep.

Common Interview Questions

  1. Write a query to count how many employees fall into each of several salary bands. Conditional aggregation with SUM(CASE WHEN ... THEN 1 ELSE 0 END), or GROUP BY on a CASE-derived bucket column.
  2. How would you sort rows by a custom, non-alphabetical priority order? CASE inside ORDER BY, mapping each category to a numeric rank.
  3. What's the relationship between CASE, COALESCE, and NULLIF? Both COALESCE and NULLIF are shorthand for specific common CASE patterns — walk through the equivalence shown above.
  4. Why might a CASE expression return an unexpected NULL? A missing ELSE clause when no WHEN condition matches — including the common trap of a NULL input silently failing every comparison in the chain.

Frequently Asked Questions

Does CASE WHEN evaluation order matter for performance, not just correctness?

Mostly for correctness — but some databases do short-circuit evaluation, stopping at the first matching condition, so placing the most commonly-true condition first can offer a small performance benefit on very large row counts, in addition to being important for correctness with overlapping conditions.

Can CASE WHEN be used in an UPDATE statement?

Yes — UPDATE employees SET level = CASE WHEN salary > 100000 THEN 'Senior' ELSE 'Junior' END is a common pattern for bulk-updating a derived column based on conditional logic.

Practice CASE WHEN Patterns

Conditional logic is one of the most frequently reused SQL skills across reporting, data cleaning, and analytics work. Try it hands-on with our SQL practice questions, or apply it to real business logic in our case studies. For the pivoting technique built directly on conditional aggregation, see our pivot and unpivot guide.

Back to All Articles