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

# INVALID_JOIN_ON_EXPRESSION ClickHouse error

{% callout %}
This error usually means your JOIN condition is malformed or contains invalid expressions. Check your JOIN syntax and make sure the ON clause contains valid comparisons.
{% /callout %}

The `INVALID_JOIN_ON_EXPRESSION` error in ClickHouse (and Tinybird) happens when the expression in a JOIN's ON clause is invalid or malformed. This can occur due to syntax errors, invalid column references, or unsupported operations in the JOIN condition.

## What causes this error

You'll typically see it when:

* Using invalid syntax in the ON clause
* Referencing columns that don't exist in the joined tables
* Using unsupported operations in JOIN conditions
* Missing proper comparison operators
* Using functions that aren't allowed in JOIN conditions

## Example errors

```sql {% title="Fails: complex expression in JOIN" %}
SELECT * FROM users JOIN events ON users.id + events.user_id = 10
```

```sql {% title="Fails: subquery in JOIN condition" %}
SELECT * FROM users JOIN events ON users.id = (SELECT user_id FROM events LIMIT 1)
```

```sql {% title="Fails: invalid operation in JOIN" %}
SELECT * FROM users JOIN events ON users.id + events.user_id = 10
```

```sql {% title="Fails: subquery in JOIN condition" %}
SELECT * FROM users JOIN events ON users.id = (SELECT MAX(user_id) FROM events)
```

## How to fix it

### Use proper JOIN syntax

Ensure your JOIN condition has valid syntax:

```sql {% title="Fix: proper JOIN syntax" %}
SELECT * FROM users JOIN events ON users.id = events.user_id
```

### Check column existence

Verify that all columns in the JOIN condition exist:

```sql {% title="Fix: use existing columns" %}
SELECT * FROM users u JOIN events e ON u.id = e.user_id
```

### Use simple comparisons

Keep JOIN conditions simple and avoid complex expressions:

```sql {% title="Fix: simple comparison" %}
SELECT * FROM users JOIN events ON users.id = events.user_id
```

### Use proper operators

Use valid comparison operators in JOIN conditions:

```sql {% title="Fix: use valid operators" %}
SELECT * FROM users JOIN events ON users.id = events.user_id
SELECT * FROM users JOIN events ON users.id > events.user_id
SELECT * FROM users JOIN events ON users.id IN (SELECT user_id FROM events)
```

## Common patterns and solutions

### Simple equality JOIN

The most common and reliable JOIN pattern:

```sql {% title="Simple equality JOIN" %}
SELECT u.id, u.name, e.event_type
FROM users u
JOIN events e ON u.id = e.user_id
```

### Multiple conditions

Use AND to combine multiple JOIN conditions:

```sql {% title="Multiple JOIN conditions" %}
SELECT u.id, u.name, e.event_type
FROM users u
JOIN events e ON u.id = e.user_id AND e.timestamp >= '2023-01-01'
```

### Different JOIN types

Use appropriate JOIN types for your use case:

```sql {% title="LEFT JOIN example" %}
SELECT u.id, u.name, e.event_type
FROM users u
LEFT JOIN events e ON u.id = e.user_id
```

```sql {% title="INNER JOIN example" %}
SELECT u.id, u.name, e.event_type
FROM users u
INNER JOIN events e ON u.id = e.user_id
```

### Self-joins

When joining a table to itself:

```sql {% title="Self-join example" %}
SELECT t1.id, t1.name, t2.name as parent_name
FROM categories t1
LEFT JOIN categories t2 ON t1.parent_id = t2.id
```

## Advanced solutions

### Using subqueries in JOIN conditions

Some complex conditions can be handled with subqueries:

```sql {% title="Subquery in JOIN" %}
SELECT u.id, u.name, e.event_type
FROM users u
JOIN events e ON u.id = e.user_id
    AND e.timestamp = (
        SELECT MAX(timestamp)
        FROM events e2
        WHERE e2.user_id = u.id
    )
```

### Multiple table JOINs

When joining multiple tables:

```sql {% title="Multiple table JOIN" %}
SELECT u.id, u.name, p.product_name, e.event_type
FROM users u
JOIN products p ON u.id = p.user_id
JOIN events e ON u.id = e.user_id
```

### Using table aliases

Always use aliases for clarity in complex JOINs:

```sql {% title="Using aliases" %}
SELECT u.id as user_id, u.name as user_name,
       p.id as product_id, p.name as product_name,
       e.event_type, e.timestamp
FROM users u
JOIN products p ON u.id = p.user_id
JOIN events e ON u.id = e.user_id
```

## Common JOIN patterns

### One-to-many relationships

```sql {% title="One-to-many JOIN" %}
SELECT u.id, u.name, COUNT(e.id) as event_count
FROM users u
LEFT JOIN events e ON u.id = e.user_id
GROUP BY u.id, u.name
```

### Many-to-many relationships

```sql {% title="Many-to-many JOIN" %}
SELECT u.id, u.name, p.id, p.name
FROM users u
JOIN user_products up ON u.id = up.user_id
JOIN products p ON up.product_id = p.id
```

### Time-based JOINs

```sql {% title="Time-based JOIN" %}
SELECT u.id, u.name, e.event_type
FROM users u
JOIN events e ON u.id = e.user_id
    AND e.timestamp >= u.created_at
```

## Tinybird-specific notes

In Tinybird, this error often occurs when:

* Building Pipes with complex JOIN conditions
* Joining Data Sources with incompatible schemas
* Using Materialized Views with invalid JOIN logic
* Working with nested Pipes that have JOIN operations

To debug in Tinybird:

1. Check your Data Source schemas for column compatibility
2. Test JOIN operations with simple queries first
3. Verify column names and types in Pipe nodes
4. Use the Query Builder to validate JOIN syntax

### Join conditions in Pipes

{% callout type="caution" %}
Join conditions in Pipes must reference fields that exist in both Data Sources. If a field doesn't exist in one of the Data Sources, the join will fail.
{% /callout %}

## Best practices

### JOIN optimization

* Use appropriate JOIN types (INNER, LEFT, RIGHT)
* Keep JOIN conditions simple and efficient
* Use primary key ordering for better performance
* Avoid complex expressions in JOIN conditions

### Query structure

* Use table aliases for clarity
* Test JOINs with small datasets first
* Document your table relationships
* Validate JOIN conditions before running on large datasets

## See also

* [Join Performance](/sql-reference/performance/joins) - JOIN performance troubleshooting
* [Join Keys and Aliases](/sql-reference/functions-troubleshooting/join-keys-and-aliases) - JOIN troubleshooting
* [Working with Data Sources](/forward/get-data-in/data-sources)