How to CASE WHEN in ClickHouse

Galaxy Glossary

How do I use CASE WHEN in ClickHouse?

CASE WHEN lets you create conditional columns or transform values on-the-fly during SELECT queries.

Sign up for the latest in SQL knowledge from the Galaxy Team!
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.

Description

Table of Contents

What problem does CASE WHEN solve?

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.

How do I write a simple CASE WHEN?

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.

Example: flag high-value orders

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;

Can I nest CASE WHEN blocks?

Yes. Keep nesting shallow for readability. Break into CTEs when logic exceeds 2-3 levels.

How do I combine CASE WHEN with aggregates?

Wrap aggregates inside the CASE expression or vice-versa. Use it to sum only certain rows or create grouped labels.

Example: sum VIP revenue

SELECT SUM(CASE WHEN total_amount > 500 THEN total_amount ELSE 0 END) AS vip_revenue
FROM Orders;

Best practices for CASE WHEN

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.

Why How to CASE WHEN in ClickHouse is important

How to CASE WHEN in ClickHouse Example Usage


-- Bucket customers by signup year and flag email domain
SELECT id,
       name,
       CASE WHEN created_at < toDate('2022-01-01') THEN 'Legacy'
            WHEN created_at < toDate('2023-01-01') THEN '2022 Cohort'
            ELSE '2023+' END AS signup_bucket,
       CASE WHEN email ILIKE '%@gmail.com' THEN 'Gmail'
            WHEN email ILIKE '%@yahoo.com' THEN 'Yahoo'
            ELSE 'Other' END AS email_provider
FROM Customers;

How to CASE WHEN in ClickHouse Syntax


CASE WHEN Syntax in ClickHouse:
SELECT
    CASE
        WHEN <condition_1> THEN <result_1>
        [WHEN <condition_N> THEN <result_N> ...]
        [ELSE <default_result>]
    END AS <alias>
FROM <table>;

-- Example with ecommerce tables
SELECT id,
       CASE WHEN stock = 0 THEN 'Out of stock'
            WHEN stock < 10 THEN 'Low'
            ELSE 'OK' END AS stock_status
FROM Products;

Common Mistakes

Frequently Asked Questions (FAQs)

Is CASE WHEN slower than multiple queries?

No. ClickHouse evaluates CASE WHEN per row in-memory, which is usually faster than running separate queries or joins.

Can I use CASE WHEN in WHERE?

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.

Does ClickHouse support searched and simple CASE?

Yes. Searched CASE (with full conditions) is most common. Simple CASE (CASE expression WHEN value THEN result) also works.

Want to learn about other SQL terms?

Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo
Welcome to the Galaxy, Guardian!
Oops! Something went wrong while submitting the form.