ClickHouse DB::Exception: Invalid JOIN ON expression

The "DB::Exception: Invalid JOIN ON expression" error in ClickHouse signals that the ON clause of a JOIN statement is syntactically valid but semantically incorrect. ClickHouse requires that each equality condition in the ON clause references exactly one column from the left table and one from the right table. When this rule is violated, the INVALID_JOIN_ON_EXPRESSION error is thrown.

Impact

The query is rejected before execution begins. No data is read, and no partial results are returned. If the query is part of a materialized view definition or an automated pipeline, it will block the entire flow until corrected.

Common Causes

1. Both sides of an ON condition reference the same table -- writing ON a.x = a.y instead of ON a.x = b.x.
2. ON condition references a table not involved in the join -- referencing a column from an outer query or a different join in a multi-join statement.
3. Missing table qualifier on an ambiguous column -- when both tables have a column with the same name and no alias is used, ClickHouse cannot determine which table is being referenced.
4. Using subquery results without proper aliasing -- when a subquery is used in the FROM clause but not aliased, columns cannot be properly attributed.
5. Complex expressions that ClickHouse cannot split into left/right parts -- expressions like a.x + b.y = 10 where both tables appear on the same side.

Troubleshooting and Resolution Steps

1. Ensure each ON condition has one column from each side of the join:

   -- Correct: one column from each table
   SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
   
   -- Wrong: both columns from the same table
   SELECT * FROM orders o JOIN customers c ON o.customer_id = o.id;
   

2. Add table aliases and qualify all column references:

   -- Ambiguous: 'id' exists in both tables
   SELECT * FROM orders JOIN customers ON id = customer_id;
   
   -- Clear: fully qualified
   SELECT * FROM orders AS o JOIN customers AS c ON o.customer_id = c.id;
   

3. Verify subquery aliases are used correctly:

   -- Wrong: no alias for subquery
   SELECT * FROM (SELECT id, name FROM customers) JOIN orders ON id = customer_id;
   
   -- Correct: subquery has alias
   SELECT * FROM (SELECT id, name FROM customers) AS c
   JOIN orders AS o ON c.id = o.customer_id;
   

4. For multi-join queries, ensure ON references the correct tables:

   SELECT *
   FROM orders AS o
   JOIN customers AS c ON o.customer_id = c.id
   JOIN products AS p ON o.product_id = p.id;  -- references 'o' and 'p', not 'c'
   

5. Move expressions involving both tables to a WHERE clause:

   -- Instead of complex ON expression
   SELECT * FROM a JOIN b ON a.id = b.id WHERE a.value + b.value > 100;
   

Best Practices

• Always use table aliases in join queries, especially when dealing with multiple tables or self-joins.
• Qualify every column reference with its table alias in multi-table queries to prevent ambiguity.
• Keep ON clauses simple -- use them only for defining the join relationship, and move additional filter conditions to WHERE.
• When constructing joins programmatically, validate that each ON predicate references exactly one column from each side.

Frequently Asked Questions

Q: Can I use functions in JOIN ON expressions?
A: Yes, as long as each side of the equality references columns from only one table. For example, ON toDate(a.timestamp) = toDate(b.timestamp) is valid because each function application involves a single table.

Q: Why do I get this error in a self-join?
A: In a self-join, both sides reference the same underlying table. You must use different aliases to distinguish them: FROM events AS e1 JOIN events AS e2 ON e1.parent_id = e2.id. Without aliases, ClickHouse cannot tell which instance of the table a column belongs to.

Q: Is there a difference between USING and ON for this error?
A: Yes. USING (column) implicitly matches columns of the same name from both tables and does not trigger this error for ambiguity. However, USING requires the column names to be identical in both tables.

Q: Can I reference columns from more than two tables in a single ON clause?
A: No. Each ON clause applies to exactly two tables -- the immediate left and right sides of that particular JOIN. For multi-way conditions, use WHERE.