v.21.1New Features

Implement UNION DISTINCT as Default and Add union_default_mode Setting

Official CH changelog text
Implement UNION DISTINCT and treat the plain UNION clause as UNION DISTINCT by default. Add a setting union_default_mode that allows to treat it as UNION ALL or require explicit mode specification. #16338 (flynn).
Implemented UNION DISTINCT support and made plain UNION default to UNION DISTINCT in ClickHouse queries. This introduces a new setting, union_default_mode, to control whether UNION behaves as UNION DISTINCT, UNION ALL, or requires explicit mode declaration.

Why it matters

The feature addresses ambiguity in the default behavior of the UNION clause by explicitly supporting UNION DISTINCT. It provides users with control over the default set operation mode, improving query clarity and predictability. This reduces accidental data duplication or unexpected result sets when using UNION without specifying ALL or DISTINCT.

How to use it

By default, plain UNION is treated as UNION DISTINCT. To change this behavior, set the union_default_mode setting to one of the following values:

- 'distinct' to treat UNION as UNION DISTINCT (default)
- 'all' to treat UNION as UNION ALL
- 'explicit' to require explicit specification of ALL or DISTINCT in queries

Example to set the mode in a session:

SET union_default_mode = 'all';


Then you can use:

SELECT  FROM table1
UNION
SELECT  FROM table2

where UNION behavior corresponds to your mode setting.

Related resources

PreviousNext