beginnerCore SQL100% Free

SQL CASE WHEN Expressions

Definition

The SQL CASE WHEN expression evaluates a list of conditions and returns a value for the first true condition, functioning as SQL's if-then-else logic.

Introduction to SQL CASE WHEN Expressions

CASE WHEN is SQL's conditional expression, similar to if/else in programming languages. It can be used anywhere an expression is allowed: in SELECT, WHERE, ORDER BY, GROUP BY, and aggregate functions. It's essential for data bucketing, conditional aggregation, and transforming raw data for reporting.

Syntax

SQL

-- Searched CASE (most common)
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE default_result
END

-- Simple CASE (equality checks only)
CASE column
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ELSE default_result
END

Examples

Salary Tier Bucketing

SELECT name, salary,
  CASE
    WHEN salary >= 100000 THEN 'Executive'
    WHEN salary >= 75000  THEN 'Senior'
    WHEN salary >= 50000  THEN 'Mid-Level'
    ELSE 'Junior'
  END AS salary_tier
FROM employees;

CASE WHEN is evaluated top-to-bottom — the first matching condition wins. Employees earning ≥$100k are 'Executive', regardless of lower conditions.

Try in Playground

Conditional Aggregation (Pivot-like)

SELECT
  department,
  COUNT(CASE WHEN gender = 'M' THEN 1 END) AS male_count,
  COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
  AVG(CASE WHEN gender = 'M' THEN salary END) AS avg_male_salary,
  AVG(CASE WHEN gender = 'F' THEN salary END) AS avg_female_salary
FROM employees
GROUP BY department;

CASE inside aggregate functions enables conditional aggregation — computing separate counts/averages for subgroups within a single GROUP BY query.

Try in Playground

Common Mistakes

Forgetting the END keyword — every CASE must be closed with END

Not including ELSE — without ELSE, unmatched conditions return NULL silently

Condition order matters — more specific conditions must come before less specific ones

Using CASE in WHERE incorrectly — WHERE CASE WHEN ... can be replaced with standard AND/OR logic

Frequently Asked Questions

What is CASE WHEN in SQL?

CASE WHEN is SQL's conditional expression. It evaluates conditions in order and returns the result for the first true condition, similar to if/elseif/else in programming. Syntax: CASE WHEN condition THEN result [WHEN ...] [ELSE default] END. It can be used in SELECT, WHERE, ORDER BY, and inside aggregate functions.

What is the difference between simple CASE and searched CASE?

Simple CASE: CASE column WHEN value THEN result END — compares a single column/expression against equality conditions. Searched CASE: CASE WHEN condition THEN result END — evaluates any boolean condition, not just equality. Searched CASE is more flexible and commonly used.

Can CASE WHEN be used inside aggregate functions?

Yes, this is called conditional aggregation. Example: SUM(CASE WHEN status = 'won' THEN deal_value ELSE 0 END) AS won_revenue. It sums only deals with 'won' status, returning 0 for others. This technique creates pivot-like summaries in a single GROUP BY query.

Practice This in the SQL Playground

Write real queries, see live results, and master SQL CASE WHEN Expressions hands-on. 100% free.

Open SQL Playground Browse All Lessons