v.22.7New Feature

Introduced additional_table_filters and additional_result_filter settings for enhanced query filtering in ClickHouse

Official CH changelog text
Introduced settings additional_table_filters. Using this setting, you can specify additional filtering condition for a table which will be applied directly after reading. Example: select number, x, y from (select number from system.numbers limit 5) f any left join (select x, y from table_1) s on f.number = s.x settings additional_table_filters={'system.numbers : 'number != 3', 'table_1' : 'x != 2'}. Introduced setting additional_result_filter which specifies additional filtering condition for query result. Closes #37918. #38475 (Nikolai Kochetov).
Introduced additional_table_filters and additional_result_filter settings for applying custom filtering conditions in queries.

Why it matters

These settings allow users to specify extra filtering conditions on tables and query results directly within the query execution process. This solves the problem of needing to repeatedly add filters manually in complex queries and enables more concise, centralized filter management that is applied automatically after data reading or on final query results.

How to use it

Use the settings additional_table_filters to apply filters on specific tables immediately after reading their data, and additional_result_filter to apply an additional filter on the entire query result. For example:

SELECT number, x, y
FROM (SELECT number FROM system.numbers LIMIT 5) f
ANY LEFT JOIN (SELECT x, y FROM table_1) s ON f.number = s.x
SETTINGS additional_table_filters = {'system.numbers': 'number != 3', 'table_1': 'x != 2'}


This applies filters number != 3 for system.numbers and x != 2 for table_1 automatically during query execution.

Related resources

PreviousNext