---
title: UNION_ALL_RESULT_STRUCTURES_MISMATCH ClickHouse error
meta:
  description: Learn how to fix the UNION_ALL_RESULT_STRUCTURES_MISMATCH error in ClickHouse and Tinybird. Understand what causes it and see examples of how to resolve it
---

# UNION_ALL_RESULT_STRUCTURES_MISMATCH ClickHouse error

{% callout %}
This error usually means the columns in your UNION ALL queries don't match in number, order, or data types. All queries in a UNION must have the same structure.
{% /callout %}

The `UNION_ALL_RESULT_STRUCTURES_MISMATCH` error in ClickHouse (and Tinybird) happens when you try to combine multiple queries with UNION ALL, but the result structures don't match. This includes having different numbers of columns, different column orders, or incompatible data types.

## What causes this error

You'll typically see it when:

* Using UNION ALL with different numbers of columns
* Having columns in different orders across queries
* Using incompatible data types in corresponding columns
* Missing columns in one part of the UNION
* Using different column names across UNION parts

{% callout type="caution" %}
Column names in the final result come from the first SELECT. Subsequent SELECTs should align but do not rename columns.
{% /callout %}

## Example errors

```sql {% title="Fails: different number of columns" %}
SELECT user_id, event_type FROM users
UNION ALL
SELECT event_id FROM events
```

```sql {% title="Fails: different column order" %}
SELECT user_id, event_type FROM users
UNION ALL
SELECT event_type, user_id FROM events
```

```sql {% title="Fails: incompatible data types" %}
SELECT user_id, event_type FROM users
UNION ALL
SELECT event_type, user_id FROM events
```

```sql {% title="Fails: different column names" %}
SELECT user_id, event_type FROM users
UNION ALL
SELECT event_id, event_type FROM events
```

## How to fix it

### Match column count

Ensure all queries have the same number of columns:

```sql {% title="Fix: same number of columns" %}
SELECT user_id, event_type FROM users
UNION ALL
SELECT user_id, event_type FROM events
```

### Match column order

Ensure columns are in the same order across all queries:

```sql {% title="Fix: same column order" %}
SELECT user_id, event_type FROM users
UNION ALL
SELECT user_id, event_type FROM events
```

### Use compatible data types

Ensure corresponding columns have compatible types:

```sql {% title="Fix: compatible data types" %}
SELECT toString(user_id) as user_id, event_type FROM users
UNION ALL
SELECT toString(user_id) as user_id, event_type FROM events
```

### Use column aliases

Use aliases to ensure consistent column names:

```sql {% title="Fix: use column aliases" %}
SELECT user_id, event_type FROM users
UNION ALL
SELECT event_id as user_id, event_type FROM events
```

## Common patterns and solutions

### Simple UNION ALL

When combining similar tables:

```sql {% title="Simple UNION ALL" %}
SELECT user_id, event_type, timestamp FROM users
UNION ALL
SELECT user_id, event_type, timestamp FROM events
```

### UNION ALL with different sources

When combining different data sources:

```sql {% title="Different sources with same structure" %}
SELECT user_id, 'user' as source, name as description FROM users
UNION ALL
SELECT user_id, 'event' as source, event_type as description FROM events
```

### UNION ALL with aggregations

When combining aggregated results:

```sql {% title="Aggregated results" %}
SELECT user_id, COUNT(*) as count FROM users GROUP BY user_id
UNION ALL
SELECT user_id, COUNT(*) as count FROM events GROUP BY user_id
```

### UNION ALL with constants

When adding constant values:

```sql {% title="Adding constants" %}
SELECT user_id, event_type, 'user' as source FROM users
UNION ALL
SELECT user_id, event_type, 'event' as source FROM events
```

## Advanced solutions

### Using CTEs for complex unions

Break down complex unions using CTEs:

```sql {% title="Complex union with CTEs" %}
WITH user_data AS (
    SELECT user_id, name as description, 'user' as source
    FROM users
),
event_data AS (
    SELECT user_id, event_type as description, 'event' as source
    FROM events
)
SELECT * FROM user_data
UNION ALL
SELECT * FROM event_data
```

### Handling different schemas

When tables have different schemas:

```sql {% title="Different schemas" %}
SELECT
    user_id,
    name as description,
    'user' as source,
    created_at as timestamp
FROM users
UNION ALL
SELECT
    user_id,
    event_type as description,
    'event' as source,
    timestamp
FROM events
```

### Using type conversion

Convert types to make them compatible:

```sql {% title="Type conversion in UNION" %}
SELECT
    toString(user_id) as user_id,
    name as description
FROM users
UNION ALL
SELECT
    toString(user_id) as user_id,
    event_type as description
FROM events
```

### Handling nullable types

When dealing with nullable types, ensure compatibility:

```sql {% title="Nullable types in UNION" %}
SELECT id, count(*) as count FROM users GROUP BY id
UNION ALL
SELECT id, toInt64(NULL) as count FROM events GROUP BY id
```

## Best practices

### Column alignment

* Always check column count and order
* Use consistent column names across queries
* Ensure compatible data types
* Use aliases for clarity

### Query structure

* Test each part of the UNION separately
* Use CTEs for complex unions
* Document the expected structure
* Validate results after combining

### Performance considerations

* Use UNION ALL instead of UNION when duplicates are acceptable
* Consider using Materialized Views for complex unions
* Monitor query performance on large datasets
* Use appropriate indexes for UNION queries

## Tinybird-specific notes

In Tinybird, this error often occurs when:

* Building Pipes that combine multiple Data Sources
* Using Materialized Views with UNION operations
* Working with nested Pipes that have different schemas
* Creating API endpoints that combine multiple queries

To debug in Tinybird:

1. Check your Data Source schemas for compatibility
2. Verify Pipe node outputs have consistent structures
3. Test UNION operations with simple queries first
4. Use the Query Builder to validate UNION structures

{% callout type="caution" %}
UNION in Pipes isn't supported. Use multiple Data Sources or perform UNION operations in SQL queries.
{% /callout %}

## See also

* [SQL Reference](/sql-reference) - SQL reference documentation
* [Working with Data Sources](/forward/core-concepts/data-sources)