ClickHouse DB::Exception: INTERSECT or EXCEPT result structures mismatch

The "DB::Exception: INTERSECT or EXCEPT result structures mismatch" error in ClickHouse occurs when the branches of an INTERSECT or EXCEPT set operation produce result sets with different numbers of columns or incompatible column types. The error code is INTERSECT_OR_EXCEPT_RESULT_STRUCTURES_MISMATCH. Like UNION, these set operations require both sides to have matching structures.

Impact

The query fails during analysis and returns no results. This is a query structure issue that must be corrected in the SQL before execution. No data is affected.

Common Causes

1. Different number of columns -- One branch of the INTERSECT or EXCEPT selects more or fewer columns than the other.
2. Incompatible column types -- The columns at the same position have types that ClickHouse cannot reconcile (e.g., String vs Date).
3. SELECT * with different table schemas -- Using SELECT * on tables that have different numbers of columns or column types.
4. Unintended column in one branch -- An extra expression or column alias was added to one side but not the other.
5. Subquery structure mismatch -- Complex subqueries that produce different output schemas on each side of the set operation.

Troubleshooting and Resolution Steps

1. Compare the column lists of both branches. Run each side independently and check the output structure:

   -- Check first branch
   SELECT name, toTypeName(name), age, toTypeName(age) FROM table_a LIMIT 0;
   
   -- Check second branch
   SELECT name, toTypeName(name), age, toTypeName(age) FROM table_b LIMIT 0;
   

2. Ensure both branches have the same number of columns:

   -- Wrong: different column counts
   SELECT a, b, c FROM table1
   INTERSECT
   SELECT x, y FROM table2;
   
   -- Correct: same column count
   SELECT a, b FROM table1
   INTERSECT
   SELECT x, y FROM table2;
   

3. Cast columns to compatible types where needed:

   SELECT name, toUInt64(age) FROM table_a
   EXCEPT
   SELECT name, toUInt64(age) FROM table_b;
   

4. Avoid SELECT * when tables have different schemas:

   -- Instead of:
   SELECT * FROM table_a INTERSECT SELECT * FROM table_b;
   
   -- Explicitly list matching columns:
   SELECT id, name, status FROM table_a
   INTERSECT
   SELECT id, name, status FROM table_b;
   

5. Use DESCRIBE to compare table structures:

   DESCRIBE TABLE table_a;
   DESCRIBE TABLE table_b;
   

Best Practices

• Always explicitly list columns in INTERSECT and EXCEPT queries rather than using SELECT *.
• Verify column types match at each position. Use toTypeName() if unsure about implicit type differences.
• When building set operations incrementally, test each branch independently first to confirm its output structure.
• Apply explicit casts to ensure type compatibility, especially when mixing tables with similar but not identical schemas.
• Use column aliases in the first branch to name the output columns clearly.

Frequently Asked Questions

Q: Do column names need to match in INTERSECT and EXCEPT?
A: No. ClickHouse matches columns by position, not by name. The output column names come from the first branch. Only the number of columns and their types must be compatible.

Q: Does ClickHouse perform implicit type conversion for set operations?
A: ClickHouse will attempt to find a common supertype for corresponding columns (e.g., UInt32 and UInt64 become UInt64). However, if no common type exists (e.g., String and Int64), the error is raised. Use explicit casts to resolve ambiguity.

Q: Can I use INTERSECT ALL or EXCEPT ALL in ClickHouse?
A: Yes. ClickHouse supports both INTERSECT ALL / EXCEPT ALL (preserves duplicates) and INTERSECT DISTINCT / EXCEPT DISTINCT (removes duplicates); the DISTINCT variants were added in version 22.9. When the qualifier is omitted, the default is controlled by the intersect_default_mode and except_default_mode settings (default ALL). The structure matching requirement applies to all variants.