CASE WHEN lets you create conditional columns or transform values on-the-fly during SELECT queries.
CASE WHEN replaces nested IFs or multi-step data cleaning by letting you branch logic inside one SELECT. It is perfect for bucketing order totals, flagging low stock products, or anonymizing customer emails without extra tables.
Start with CASE
, add one or more WHEN condition THEN result
pairs, finish with optional ELSE default
, and close with END
. The expression returns the first matching result.
SELECT id,
total_amount,
CASE WHEN total_amount > 500 THEN 'VIP'
WHEN total_amount > 100 THEN 'Preferred'
ELSE 'Standard' END AS customer_tier
FROM Orders;
Yes. Keep nesting shallow for readability. Break into CTEs when logic exceeds 2-3 levels.
Wrap aggregates inside the CASE expression or vice-versa. Use it to sum only certain rows or create grouped labels.
SELECT SUM(CASE WHEN total_amount > 500 THEN total_amount ELSE 0 END) AS vip_revenue
FROM Orders;
Order conditions from most-specific to least-specific. Avoid overlapping ranges. Alias the result for clarity. Test with small result sets before adding to production queries.
No. ClickHouse evaluates CASE WHEN per row in-memory, which is usually faster than running separate queries or joins.
Yes, wrap it in a subquery or CTE and filter on the computed column, or embed a simple CASE in the WHERE clause when returning boolean.
Yes. Searched CASE (with full conditions) is most common. Simple CASE (CASE expression WHEN value THEN result) also works.