SQL CASE WHEN: Patterns, Pitfalls, and Better Alternatives

SQL CASE WHEN is the closest thing SQL has to an if statement, and it is the single most overused construct in analytics code. It is also one of the least understood: the ordering rules are strict, the NULL behavior surprises almost everyone the first time, and the type resolution rules can silently truncate your output. This is a working reference. Every example below runs against a single small table, and each section ends with the version of the pattern you should actually ship.

Assume this schema throughout:

CREATE TABLE orders (
  order_id       BIGINT PRIMARY KEY,
  customer_id    BIGINT NOT NULL,
  channel        TEXT,
  status         TEXT,
  amount_cents   INTEGER,
  discount_cents INTEGER,
  placed_at      TIMESTAMP NOT NULL
);

What SQL CASE WHEN actually evaluates

CASE is an expression, not a statement. It returns a single value of a single type, and it can go anywhere a scalar expression is legal: the SELECT list, WHERE, GROUP BY, ORDER BY, HAVING, a JOIN condition, the SET clause of an UPDATE, even inside an aggregate function. That last placement is where most of the interesting work happens.

The general form is:

CASE
  WHEN <boolean condition> THEN <result>
  WHEN <boolean condition> THEN <result>
  ELSE <result>
END

Three rules govern everything else. The conditions are tested top to bottom. The first one that evaluates to TRUE wins and the rest are skipped. If none is TRUE and there is no ELSE, the expression returns NULL. Nearly every CASE bug in production traces back to one of those three rules being assumed away.

Note the word TRUE. A condition that evaluates to NULL is not TRUE, and in SQL's three valued logic that is a meaningfully different thing from FALSE. It falls through exactly like FALSE does, which is why NULL inputs quietly land in your ELSE bucket.

Simple CASE versus searched CASE

There are two syntaxes and they are not interchangeable.

The simple CASE compares one expression against a list of values using implicit equality:

SELECT
  order_id,
  CASE status
    WHEN 'paid'     THEN 'Complete'
    WHEN 'refunded' THEN 'Reversed'
    WHEN 'pending'  THEN 'In progress'
    ELSE 'Unknown'
  END AS status_label
FROM orders;

The searched CASE takes a full boolean condition in every branch:

SELECT
  order_id,
  CASE
    WHEN amount_cents >= 100000 THEN 'enterprise'
    WHEN amount_cents >=  20000 THEN 'mid'
    WHEN amount_cents >      0  THEN 'small'
    ELSE 'zero_or_credit'
  END AS size_band
FROM orders;

Simple CASE is more compact and slightly easier to read for pure value mapping. It cannot express ranges, compound conditions, or anything involving NULL. Searched CASE can express everything simple CASE can, so if you only want to memorize one form, memorize the searched one.

A practical rule: use simple CASE only when you are mapping a small, closed set of literal values and you are certain the column is NOT NULL. The moment a range, an OR, or a nullable column enters the picture, switch.

Ordering semantics: the first true branch wins

Branch order is not cosmetic. It is the logic. Reverse the bands in the example above and the query still runs, still returns a row for every order, and is completely wrong:

CASE
  WHEN amount_cents >      0  THEN 'small'
  WHEN amount_cents >=  20000 THEN 'mid'
  WHEN amount_cents >= 100000 THEN 'enterprise'
  ELSE 'zero_or_credit'
END

Every positive amount matches the first branch, so mid and enterprise are unreachable. There is no warning. The query is syntactically perfect and semantically dead.

Two habits prevent this class of bug. First, order range conditions monotonically, either strictly descending or strictly ascending, and never mix directions inside one CASE. Second, when the bands are meant to be mutually exclusive, write them as if order did not matter:

CASE
  WHEN amount_cents >= 100000                            THEN 'enterprise'
  WHEN amount_cents >=  20000 AND amount_cents < 100000  THEN 'mid'
  WHEN amount_cents >       0 AND amount_cents <  20000  THEN 'small'
  ELSE 'zero_or_credit'
END

That version is more verbose, but it survives someone reordering the branches during a refactor, and it makes the intended boundaries reviewable at a glance. On a table with millions of rows the redundant comparison costs almost nothing next to the scan.

Order also matters for a very useful pattern: custom sort keys. When a status column has a business priority that does not match its alphabetical order, CASE in ORDER BY is the clean answer:

SELECT order_id, status, placed_at
FROM orders
ORDER BY
  CASE status
    WHEN 'overdue' THEN 0
    WHEN 'pending' THEN 1
    WHEN 'paid'    THEN 2
    ELSE 3
  END,
  placed_at DESC;

NULL handling, the pitfall that ships to production

This is the section worth rereading. Simple CASE compares with =, and nothing is ever equal to NULL. So this branch never fires, no matter how many NULLs are in the column:

CASE status
  WHEN NULL THEN 'missing'
  WHEN 'paid' THEN 'complete'
  ELSE 'other'
END

Every NULL status lands in 'other'. The fix is a searched CASE with an explicit IS NULL test, placed first:

CASE
  WHEN status IS NULL   THEN 'missing'
  WHEN status = 'paid'  THEN 'complete'
  ELSE 'other'
END

The same trap appears with comparisons. WHEN amount_cents > 0 is NULL, not FALSE, when the amount is NULL, so those rows fall through to ELSE and get labeled as if they were zero. If a NULL amount means something different from a zero amount in your business, and it usually does, say so explicitly:

CASE
  WHEN amount_cents IS NULL THEN 'unbilled'
  WHEN amount_cents = 0     THEN 'zero'
  WHEN amount_cents > 0     THEN 'billed'
  ELSE 'credit'
END

The omitted ELSE is the mirror image of this problem. A CASE with no ELSE returns NULL for unmatched rows, which is sometimes exactly what you want, particularly inside aggregates. But an implicit NULL and a deliberate NULL look identical six months later. Write ELSE NULL when you mean it. Reviewers should be able to tell the difference between a decision and an oversight.

Two standard shorthands exist for the most common NULL cases, and you should prefer them over hand rolled CASE. COALESCE(a, b, c) is defined as a CASE that returns the first non-null argument. NULLIF(a, b) returns NULL when the two are equal and a otherwise, which is the idiomatic divide by zero guard:

SELECT
  channel,
  SUM(discount_cents) / NULLIF(SUM(amount_cents), 0) AS discount_ratio
FROM orders
GROUP BY channel;

One documented caveat is worth knowing before you use CASE as a guard: Microsoft's SQL Server documentation warns that aggregate expressions inside a CASE may be evaluated before the CASE itself, so wrapping a division in CASE WHEN denominator <> 0 THEN ... END is not a reliable way to prevent a divide by zero error when aggregates are involved. NULLIF on the denominator is the portable, dependable fix.

SQL CASE WHEN in aggregation: pivots, buckets, and rate math

Conditional aggregation is the pattern that earns CASE its place. Putting a CASE inside an aggregate function turns rows into columns without any dialect specific PIVOT syntax, and it works everywhere.

SELECT
  channel,
  COUNT(*) AS orders,
  SUM(CASE WHEN status = 'paid'     THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded,
  SUM(CASE WHEN status = 'paid' THEN amount_cents ELSE 0 END) AS paid_cents
FROM orders
GROUP BY channel
ORDER BY orders DESC;

Three details separate a correct conditional aggregate from a subtly wrong one.

COUNT and SUM want different fallbacks. COUNT() ignores NULLs, so COUNT(CASE WHEN status = 'paid' THEN 1 END) with no ELSE is correct and idiomatic. Add ELSE 0 to that and you count every row, because zero is not null. With SUM() the reverse holds: ELSE 0 is safe and often clearer, though omitting it also works since SUM skips NULLs.

AVG almost never wants ELSE 0. AVG(CASE WHEN status = 'paid' THEN amount_cents END) averages paid orders only. Add ELSE 0 and you have averaged paid amounts across all orders, which is a different and usually meaningless number.

Rate math needs a NULLIF. Percentages computed over a filtered group will eventually meet an empty group:

SELECT
  channel,
  ROUND(
    100.0 * SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END)
          / NULLIF(COUNT(*), 0),
    2
  ) AS refund_rate_pct
FROM orders
GROUP BY channel;

If you are shaping query output into something people read, it helps to think about the result set as a grid before you write the SELECT. Our piece on rows and columns in data covers why deciding what a single row means, up front, prevents most of the reshaping work that conditional aggregation gets used for.

The filtered aggregate, when your engine has it

Standard SQL defines a cleaner form for exactly this job. PostgreSQL, SQLite from 3.30, and DuckDB support it:

SELECT
  channel,
  COUNT(*)                                   AS orders,
  COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
  SUM(amount_cents) FILTER (WHERE status = 'paid') AS paid_cents
FROM orders
GROUP BY channel;

It reads better, it removes the ELSE ambiguity entirely, and on PostgreSQL it can be faster because the planner does not evaluate a CASE per row per aggregate. MySQL and SQL Server do not support FILTER as of 2026, so SUM(CASE ...) remains the portable choice. Use FILTER where you can, and keep the CASE form for code that has to run against multiple engines.

Type resolution, ELSE, and other quiet failures

A CASE expression has one result type, derived from all its branches. Mixing types invites the engine to make a decision for you.

Return a string in one branch and a number in another, and PostgreSQL will raise an error while MySQL will happily coerce. Worse, when branches return strings of different declared lengths on SQL Server, the result can be truncated to the width the engine infers. Two habits help: cast explicitly in every branch when the types are not obviously identical, and keep labels and numbers in separate expressions rather than one polymorphic CASE.

Other quiet failures worth naming:

CASE in a WHERE clause is usually a rewrite. WHERE CASE WHEN a THEN b ELSE c END = 1 is almost always clearer and faster as plain boolean logic with AND and OR. Wrapping an indexed column in a CASE makes the predicate non sargable, so the index goes unused and you get a scan.

Repeating a CASE across SELECT, GROUP BY, and ORDER BY is a maintenance trap. Standard SQL makes you repeat the whole expression in GROUP BY. When the definition changes and only two of the three copies get updated, the query still runs. Push the CASE into a CTE or subquery and group by the resulting column instead.

WITH banded AS (
  SELECT
    order_id,
    amount_cents,
    CASE
      WHEN amount_cents >= 100000 THEN 'enterprise'
      WHEN amount_cents >=  20000 THEN 'mid'
      ELSE 'small'
    END AS size_band
  FROM orders
)
SELECT size_band, COUNT(*), SUM(amount_cents)
FROM banded
GROUP BY size_band;

CASE inside a JOIN condition is a design smell. If the join key depends on a condition, the model is telling you something. It usually means two relationships are being squeezed into one column.

Better alternatives to SQL CASE WHEN

CASE is right for small, stable, query local logic. It is wrong for business rules that many queries share, for mappings that non engineers own, and for anything with more than roughly six branches. Here is how the options compare.

The lookup table version

When a CASE has grown to fifteen branches, or when someone outside the data team decides what the branches are, move it into a table:

CREATE TABLE order_size_band (
  band       TEXT    PRIMARY KEY,
  min_cents  INTEGER NOT NULL,
  max_cents  INTEGER,
  sort_order INTEGER NOT NULL
);

SELECT
  COALESCE(b.band, 'unclassified') AS size_band,
  COUNT(*) AS orders
FROM orders o
LEFT JOIN order_size_band b
  ON o.amount_cents >= b.min_cents
 AND (b.max_cents IS NULL OR o.amount_cents < b.max_cents)
GROUP BY COALESCE(b.band, 'unclassified');

The LEFT JOIN plus COALESCE is deliberate. An inner join would silently drop rows that fall outside every band, which is the exact failure mode you moved to a table to avoid. Note also that the bands are now data: someone can change a threshold without a deploy, and you can audit when it changed. That tradeoff, logic in code versus logic in the model, is the same decision covered in our guide to data modelling tools.

The generated column version

When the same CASE appears in a dozen queries and the inputs never leave the row, compute it once:

ALTER TABLE orders
ADD COLUMN net_cents INTEGER
GENERATED ALWAYS AS (amount_cents - COALESCE(discount_cents, 0)) STORED;

Now every query reads net_cents and no one can get the definition slightly wrong. A view does the same job without the storage cost and without a schema migration, at the price of recomputation per query.

A review checklist for CASE expressions

Run through this before merging any query with a CASE in it.

• Is there an ELSE? If not, is the resulting NULL deliberate and documented?
• Are nullable columns tested with IS NULL before any comparison?
• Are range branches ordered consistently, and are the boundaries non overlapping?
• Does every branch return the same, explicitly cast, type?
• Inside COUNT, is the ELSE omitted? Inside AVG, is ELSE 0 actually intended?
• Is any percentage denominator wrapped in NULLIF?
• Is this CASE repeated in more than one query? If yes, it belongs in a view, a generated column, or a lookup table.
• Does the CASE wrap an indexed column inside WHERE or JOIN? If yes, rewrite as boolean logic.

Where the query stops and the workflow starts

Writing the CASE is the easy part. The tedious part is everything after: running it on a schedule, noticing when a band shifts, and getting the result to the person who cares before they ask.

That is the part Skopx handles. You can query PostgreSQL, MySQL, and MongoDB directly in chat, alongside data pulled from nearly 1,000 connected integrations, and answers cite where they came from. If you would rather describe the bucketing than write it, you can:

Group last quarter's orders in the production Postgres read replica into enterprise, mid, and small bands by amount, show the refund rate for each band by channel, and every Monday at 9am post the table to #revenue-ops with any band whose refund rate moved more than two points week over week.

That one sentence returns the table immediately and, because it names a schedule and a destination, builds a workflow that reruns it. Workflows are described in chat rather than dragged onto a canvas: triggers are manual, scheduled with a fifteen minute minimum, or webhook based, and every run is inspectable step by step. They are deliberately bounded, with a maximum of twenty steps, no loops, and no custom code, so they stay auditable. Actions only execute with your approval.

Two honest limits. Skopx is not a BI tool. It does not build drag and drop dashboards, and it will not render your banded refund rates as a stacked bar chart. If a chart is the deliverable, use a dedicated visualization tool and start from examples of charts or our breakdown of different types of charts to pick the right form. And Skopx is paid from day one: Solo is $5 per month, Team is $16 per seat per month with no seat cap. You bring your own AI provider key and Skopx adds no markup on model usage. Details are on the pricing page.

Frequently asked questions

What is the difference between CASE WHEN and IF in SQL?

IF() in MySQL and IIF() in SQL Server take exactly one condition and two results. SQL CASE WHEN takes any number of conditions, works in every major engine, and is part of the SQL standard. Use IF or IIF for a single inline two way choice. Use CASE for everything else, and never nest IIF more than one level deep, because the readability collapses immediately.

Why does my CASE WHEN return NULL?

Two causes account for nearly all of them. Either no branch evaluated to TRUE and there is no ELSE, in which case the expression returns NULL by definition, or a condition compared against a NULL column. column = 'x' is NULL rather than FALSE when the column is NULL, so those rows fall through. Add an explicit WHEN column IS NULL branch first, then add an ELSE.

Can I use CASE WHEN in a WHERE clause?

Yes, it is legal, but it is usually the wrong tool. Ordinary boolean logic with AND and OR expresses the same filter more clearly, and it lets the planner use indexes on the columns involved. Wrapping an indexed column in a CASE makes the predicate non sargable and typically forces a full scan. The legitimate uses are rare, mostly dynamic filters driven by a parameter.

How do I pivot rows into columns with CASE WHEN?

Put a CASE inside an aggregate, one aggregate per output column: SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid. Group by the column you want as rows. On PostgreSQL, SQLite 3.30 or later, and DuckDB, the FILTER (WHERE ...) clause does the same thing more legibly. Both approaches require knowing the output columns in advance; a truly dynamic pivot needs generated SQL or a tool outside the query.

When should I replace CASE WHEN with a lookup table?

Replace it when the branches exceed roughly six, when the same mapping appears in more than two queries, when a non engineer owns the values, or when you need to know what the mapping was on a past date. A table gives you version history, a single definition, and edits without a deploy. Keep CASE for logic that is genuinely local to one query.

Is CASE WHEN slow?

On its own, no. A CASE is a cheap per row expression and its cost is dominated by the scan around it. It becomes a performance problem indirectly: inside WHERE or a JOIN condition it can prevent index use, and dozens of CASE expressions inside aggregates over a wide scan add up. If a query with CASE is slow, read the execution plan before rewriting the CASE. The bottleneck is almost always the access path, not the conditional.