ClickHouse DB::Exception: No alias for subquery or table function in JOIN (Code: 206)

The "DB::Exception: No alias for subquery or table function in JOIN" error in ClickHouse (error code ALIAS_REQUIRED) occurs when a subquery or table function used in a JOIN does not have an alias. The full message usually reads "No alias for subquery or table function in JOIN (set joined_subquery_requires_alias=0 to disable restriction)". This restriction is controlled by the joined_subquery_requires_alias setting, which is enabled (1) by default, and exists so that columns coming from the joined subquery can be referenced unambiguously.

Impact

The query is rejected at parse or analysis time -- no data is read or processed. This is purely a syntactic or structural issue that requires modifying the query text. It does not affect any stored data or server state.

Common Causes

1. Subqueries without aliases in JOIN clauses -- joining against (SELECT ...) without an AS name triggers the restriction.
2. Table functions without aliases in JOIN clauses -- joining against a table function such as numbers(...) or file(...) without an alias.
3. Generated SQL from ORMs or query builders -- tools that emit join subqueries without adding an alias.
4. Migrations from other databases -- some databases allow unaliased join subqueries, so ported queries hit this restriction in ClickHouse.

Troubleshooting and Resolution Steps

1. Add an alias to the joined subquery using AS:

   -- Before (triggers ALIAS_REQUIRED)
   SELECT a.id, b.total
   FROM table_a AS a
   JOIN (SELECT id, sum(value) AS total FROM table_b GROUP BY id)
   ON a.id = b.id;
   
   -- After
   SELECT a.id, b.total
   FROM table_a AS a
   JOIN (SELECT id, sum(value) AS total FROM table_b GROUP BY id) AS b
   ON a.id = b.id;
   

2. Add an alias to table functions used in a JOIN:

   -- Before
   SELECT t.number FROM table_a AS a JOIN numbers(10) ON a.id = number;
   
   -- After
   SELECT t.number FROM table_a AS a JOIN numbers(10) AS t ON a.id = t.number;
   

3. Disable the restriction (only if you cannot add an alias):

   SET joined_subquery_requires_alias = 0;
   

   This is discouraged because unaliased join subqueries make column references ambiguous; prefer adding aliases.

4. Check the exact position in the error message. ClickHouse names the join member that is missing an alias, so read the full message carefully.

Best Practices

• Alias every subquery and table function in JOIN clauses as a habit -- this is what the error specifically requires.
• Use meaningful alias names that describe the joined data (e.g., daily_totals).
• When building queries programmatically, ensure your query builder always emits an alias for join subqueries.
• Keep joined_subquery_requires_alias = 1 (the default) so ambiguous joins are caught early rather than producing surprising results.
• Even where ClickHouse does not strictly require it, aliasing computed columns in SELECT makes queries more readable and results easier to consume.

Frequently Asked Questions

Q: Why does ClickHouse require an alias here but not for a plain SELECT expression?
A: Computed columns in a top-level SELECT are auto-named (e.g., count() becomes count()), so they do not trigger this error. The ALIAS_REQUIRED error is specifically about subqueries and table functions inside a JOIN: ClickHouse needs a name to disambiguate the columns coming from the joined member, which is enforced by joined_subquery_requires_alias.

Q: Can I just turn the restriction off?
A: You can set joined_subquery_requires_alias = 0 to disable it, but this is discouraged. Without aliases, references to columns from the joined subquery can be ambiguous and lead to confusing results. Adding an alias is the safer fix.

Q: Can I use backticks or double quotes for aliases?
A: Yes. ClickHouse supports AS alias_name, AS "alias name", and backtick-quoted aliases. Use quoting when the alias contains spaces or special characters, though simple identifiers are preferred for clarity.

Q: Will adding aliases affect query performance?
A: No. Aliases are resolved at query analysis time and have zero runtime cost. They are purely a naming mechanism.