ClickHouse DB::Exception: Expected ALL or DISTINCT qualifier for UNION

The "DB::Exception: Expected ALL or DISTINCT" error in ClickHouse appears when you write a UNION statement without specifying whether duplicates should be kept (ALL) or removed (DISTINCT). Unlike some SQL dialects where bare UNION defaults to deduplication, ClickHouse requires the qualifier to be explicit. This error is identified by the code EXPECTED_ALL_OR_DISTINCT.

Impact

Any query containing a bare UNION without a qualifier will fail immediately. This commonly affects SQL migrations from systems like PostgreSQL or MySQL, where UNION alone is valid syntax that implicitly deduplicates rows.

Common Causes

1. Writing SELECT ... UNION SELECT ... without appending ALL or DISTINCT.
2. Migrating queries from another RDBMS that allows bare UNION.
3. Using SQL generation tools or ORMs that emit standard SQL without ClickHouse-specific adjustments.
4. Copy-pasting example queries from generic SQL tutorials.

Troubleshooting and Resolution Steps

1. Add the appropriate qualifier: Determine whether you need all rows (including duplicates) or only unique rows:

   -- Keep all rows, including duplicates
   SELECT col1 FROM table1
   UNION ALL
   SELECT col1 FROM table2;
   
   -- Remove duplicate rows
   SELECT col1 FROM table1
   UNION DISTINCT
   SELECT col1 FROM table2;
   

2. Default to UNION ALL when unsure: In most analytical workloads, UNION ALL is the right choice. It is faster because it skips the deduplication step, and if your source queries already produce non-overlapping data, deduplication is unnecessary overhead.

3. Update SQL generators: If the query is built by an ORM or code generator, configure it to emit UNION ALL or UNION DISTINCT for ClickHouse targets.

4. Set the default mode: ClickHouse offers the union_default_mode setting. You can set it to 'ALL' or 'DISTINCT' to allow bare UNION statements:

   SET union_default_mode = 'ALL';
   SELECT col1 FROM table1
   UNION
   SELECT col1 FROM table2;  -- now treated as UNION ALL
   

   However, relying on this setting reduces query portability and clarity.

5. Apply the fix to INTERSECT and EXCEPT as well: The same qualifier requirement applies to INTERSECT and EXCEPT in ClickHouse. If your query uses those set operations, add ALL or DISTINCT to each one.

Best Practices

• Always write UNION ALL or UNION DISTINCT explicitly for clarity and portability.
• Prefer UNION ALL in analytical queries where performance matters and duplicates are acceptable or impossible.
• If migrating a large SQL codebase, use a search-and-replace to find bare UNION keywords and add the qualifier.
• Avoid relying on union_default_mode in production queries; explicit qualifiers are clearer to future maintainers.

Frequently Asked Questions

Q: Why doesn't ClickHouse just default to UNION DISTINCT like standard SQL?
A: ClickHouse's design philosophy emphasizes explicit behavior. Since UNION ALL and UNION DISTINCT have very different performance characteristics — deduplication can be expensive on large datasets — the engine requires you to state your intent.

Q: Can I set union_default_mode globally for all sessions?
A: Yes, you can set it in the server configuration under user profiles or in users.xml. This makes bare UNION work for all queries under that profile, though explicit qualifiers are still recommended.

Q: Does this also apply to UNION inside subqueries and CTEs?
A: Yes. Every UNION in a query — whether at the top level, inside a subquery, or within a CTE — must have an ALL or DISTINCT qualifier unless union_default_mode is set.