SQL CASE Statement

CASE adds if/else-style logic directly inside a query — evaluating conditions and returning different values based on which one matches.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

Sample Table: employees

Example

SELECT name, salary,
    CASE
        WHEN salary >= 60000 THEN 'Senior Band'
        WHEN salary >= 45000 THEN 'Mid Band'
        ELSE 'Entry Band'
    END AS salary_band
FROM employees;

Output:

WHEN conditions are checked top to bottom — the first one that matches wins, so order them from most specific to least specific.

CASE Inside Aggregates — A Very Common Pattern

SELECT
    SUM(CASE WHEN department = 'Engineering' THEN salary ELSE 0 END) AS engineering_total,
    SUM(CASE WHEN department = 'Marketing' THEN salary ELSE 0 END) AS marketing_total
FROM employees;

This is the standard way to "pivot" data — turning row values into separate summary columns — without a dedicated PIVOT feature.

Simple CASE Form

SELECT name,
    CASE department
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Marketing' THEN 'Growth'
        ELSE 'Other'
    END AS team_group
FROM employees;

Shorter when every branch is checking the same column for exact equality.

Common Mistakes

• Forgetting ELSE — without it, non-matching rows return NULL instead of a sensible default, which can silently break downstream logic
• Ordering WHEN conditions incorrectly — e.g. checking salary >= 45000 before salary >= 60000 means high earners never reach the second condition

Interview Relevance

The "pivot with CASE + SUM" pattern above is asked constantly in data analyst interviews — practice it until it's automatic.

Practice Question

Write a query that labels each order as 'Small' (under 500), 'Medium' (500–2000), or 'Large' (over 2000) based on its total_amount.

Related SQL Notes

• GROUP BY
• COALESCE
• Aggregate Functions