How to Filter Data in ClickHouse

Galaxy Glossary

How do I filter data in ClickHouse using WHERE and PREWHERE?

Filters restrict rows returned by a ClickHouse query using clauses like WHERE, PREWHERE, HAVING, and FILTER.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use the WHERE clause in ClickHouse?

WHERE removes rows that do not satisfy a boolean expression before data reaches the processing pipeline. Filtering early reduces memory, CPU, and network usage, giving faster queries and lower costs.

How do I write a simple filter?

Start with SELECT columns FROM table WHERE condition. Combine conditions with AND, OR, and parentheses. Use functions such as like(), in(), between, and direct comparisons for numeric or date ranges.

When should I switch to PREWHERE?

Use PREWHERE when the condition targets fields in primary or data-skipping indexes that are highly selective. ClickHouse reads only matching granules, reducing disk I/O before evaluating complex expressions.

Example with PREWHERE

SELECT id, order_date, total_amount FROM Orders PREWHERE order_date >= '2024-01-01' WHERE total_amount > 100;

How can I filter aggregated data?

Apply HAVING after GROUP BY to keep groups that meet a condition, or use a subquery and filter its result for maximum flexibility.

HAVING example

SELECT customer_id, sum(total_amount) AS lifetime_value FROM Orders GROUP BY customer_id HAVING lifetime_value > 1000;

Does ClickHouse support the FILTER clause?

Yes. Add FILTER (WHERE ...) inside aggregate functions to include rows conditionally without CASE statements.

FILTER example

SELECT count(*) FILTER (WHERE status = 'completed') AS completed_orders, count(*) FILTER (WHERE status = 'cancelled') AS cancelled_orders FROM Orders;

Best practices for filtering

Keep filters sargable, avoid wrapping indexed columns in functions, use materialized columns for expensive expressions, build data-skipping indexes on frequently filtered fields, and pair PREWHERE with LIMIT to push work down early.

Why How to Filter Data in ClickHouse is important

How to Filter Data in ClickHouse Example Usage


SELECT
    o.id,
    o.order_date,
    o.total_amount
FROM Orders AS o
PREWHERE o.order_date >= toDate('2024-01-01')
WHERE o.total_amount > 100
ORDER BY o.total_amount DESC
LIMIT 10;

How to Filter Data in ClickHouse Syntax


SELECT column_list
FROM table_name
[PREWHERE pre_condition]
[WHERE condition]
[GROUP BY expression [, ...]]
[HAVING group_condition]
[ORDER BY expression [ASC|DESC] [, ...]]
[LIMIT n [OFFSET m]]

-- Example with ecommerce tables
SELECT c.name, o.order_date, o.total_amount
FROM Orders AS o
JOIN Customers AS c ON c.id = o.customer_id
PREWHERE o.order_date >= '2024-01-01'
WHERE o.total_amount > 100;

Common Mistakes

Skipping PREWHERE: Placing all conditions in WHERE forces ClickHouse to read full data parts. Move highly selective checks to PREWHERE to cut I/O and speed up queries.
Wrapping indexed columns in functions (e.g., toDate(order_date)) prevents data-skipping indexes from working. Compare raw columns and cast literals instead.