INVALID_JOIN_ON_EXPRESSION ClickHouse error¶

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¶

SELECT * FROM users JOIN events ON users.id + events.user_id = 10

SELECT * FROM users JOIN events ON users.id = (SELECT user_id FROM events LIMIT 1)

SELECT * FROM users JOIN events ON users.id + events.user_id = 10

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:

SELECT * FROM users JOIN events ON users.id = events.user_id

Check column existence¶

Verify that all columns in the JOIN condition exist:

SELECT * FROM users u JOIN events e ON u.id = e.user_id

Use simple comparisons¶

Keep JOIN conditions simple and avoid complex expressions:

SELECT * FROM users JOIN events ON users.id = events.user_id

Use proper operators¶

Use valid comparison operators in JOIN conditions:

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:

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:

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:

SELECT u.id, u.name, e.event_type
FROM users u
LEFT JOIN events e ON u.id = e.user_id

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:

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:

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:

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:

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¶

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¶

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¶

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¶

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¶

• ClickHouse JOIN Operations
• Working with Data Sources
• Common function errors