Does CASE evaluate every branch?

No. It checks the WHEN conditions top to bottom and returns the result of the first true one. Later branches are never reached for that row. This is standard SQL in Postgres, MySQL and SQL Server.

Why use ELSE 'other' instead of leaving NULL?

A NULL bucket shows up as a blank label, or disappears from a chart or a later filter. An explicit ELSE puts those rows in a named group you can see, count and question.

Is this the same as the OR precedence problem?

No. OR precedence changes which rows a WHERE keeps. CASE changes the label each row gets, and the order of the branches decides it.

Why are BETWEEN ranges risky in CASE?

BETWEEN 0 AND 49 and BETWEEN 50 AND 99 look complete, but 49.50 matches neither. Amounts have decimals. Use amount >= low AND amount < high so there are no gaps and no overlaps.

What about SUM(CASE WHEN ...) for counting a condition?

SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) works. Without the ELSE 0, rows that do not match are NULL, and if no row matches the whole SUM is NULL, not 0.

CASE WHEN Order and Missing ELSE | Buckets Wrong

Q: What about SUM(CASE WHEN ...) for counting a condition?

SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) works. Without the ELSE 0, rows that do not match are NULL, and if no row matches the whole SUM is NULL, not 0.

You ask: "How many orders are small, medium and large?" The AI returns three rows. Small 640. Medium 310. And a row with a blank label and 50 orders. There is no large row, but you know you have big orders.

SELECT
  CASE
    WHEN amount >= 50  THEN 'medium'
    WHEN amount >= 500 THEN 'large'
    WHEN amount >= 10  THEN 'small'
  END AS size,
  COUNT(*)
FROM orders
GROUP BY 1

Two silent bugs sit in that query.

CASE stops at the first match

A $900 order is >= 50, so it becomes 'medium'. The >= 500 line below it is never reached for any row. 'large' cannot appear. Overlapping thresholds written from smallest to largest hide everything past the first one.

No ELSE means NULL

An order of $4 matches none of the branches. With no ELSE, the label is NULL. It still counts in GROUP BY, as the blank-labeled 50 above. If the AI then filters, charts or summarizes by label, those rows vanish. This is the same family as filters that skip NULL.

The ranges also leave gaps

-- looks complete, is not
WHEN amount BETWEEN 0  AND 49 THEN 'small'
WHEN amount BETWEEN 50 AND 99 THEN 'medium'
-- 49.50 matches neither

Whole-number thinking on a decimal column leaves holes. A 49.50 order falls through to NULL.

The fix

CASE
  WHEN amount >= 500 THEN 'large'
  WHEN amount >= 50  THEN 'medium'
  WHEN amount >= 10  THEN 'small'
  ELSE 'tiny'
END

Order from the most specific (highest) threshold down, use only >= against the lower bound, and always finish with an ELSE. There are no gaps and no overlaps.

If the CASE feeds a sum, SUM over nothing is NULL, not zero. If it builds a rate, integer division can turn it to 0.

How to catch it

-- bucket counts must add up to the table
SELECT COUNT(*) FROM orders          -- 1000
-- sum of the bucket counts           -- 640 + 310 = 950 labeled
-- the 50 missing are the NULL bucket

If the buckets do not add up to the row count, a branch is missing or unreachable. Also check that every bucket you expected appears.

The prompt that heads this off

When you bucket values with CASE, use half-open ranges (>= low AND < high),
order branches from the highest threshold down, and end with ELSE so no row
gets NULL. Show the count per bucket and confirm they sum to the total.

The short version

CASE returns the first branch that is true. Put the most specific one first, leave no gaps, and end with ELSE. If the buckets do not sum to the row count, something fell through.

Read-only MCP gateway: mcpserver.design.

Related reading: