ClickHouse DB::Exception: Expected ALL or ANY qualifier in comparison with subquery

The "DB::Exception: Expected ALL or ANY" error in ClickHouse occurs when you use a comparison operator (such as =, >, <) with a subquery that can return multiple rows, but you have not specified whether the comparison should hold for ALL rows or ANY row in the result. The error code for this condition is EXPECTED_ALL_OR_ANY.

Impact

The query that triggered this error will not execute. This typically surfaces during development or when migrating SQL from other database systems that handle multi-row subquery comparisons differently (e.g., some databases implicitly treat them as ANY).

Common Causes

1. Writing a comparison like WHERE x > (SELECT y FROM ...) where the subquery returns more than one row, without specifying ANY or ALL.
2. Porting queries from PostgreSQL or MySQL that rely on implicit behavior for multi-row subquery comparisons.
3. Using a scalar subquery that unintentionally returns multiple rows due to missing filters or aggregation.
4. Forgetting to wrap the subquery in an aggregate function like MAX() or MIN() when a single value is intended.

Troubleshooting and Resolution Steps

1. Add the ANY or ALL qualifier: Decide whether the comparison should be true for at least one row (ANY) or every row (ALL) returned by the subquery:

   -- True if x is greater than at least one value returned
   SELECT * FROM table1 WHERE x > ANY (SELECT y FROM table2);
   
   -- True if x is greater than every value returned
   SELECT * FROM table1 WHERE x > ALL (SELECT y FROM table2);
   

2. Use an aggregate to produce a scalar: If you actually want a single value, apply an aggregate function so the subquery is guaranteed to return one row:

   SELECT * FROM table1 WHERE x > (SELECT MAX(y) FROM table2);
   

3. Replace with IN for equality checks: If you are checking membership rather than ordering, use IN instead of = ANY:

   SELECT * FROM table1 WHERE x IN (SELECT y FROM table2);
   

4. Add a LIMIT 1 clause: If the subquery should logically return a single row but might not due to data issues, adding LIMIT 1 makes the intent explicit — though fixing the underlying data or query logic is preferable.

5. Review the subquery for correctness: Verify that the subquery returns the expected number of rows. A missing WHERE clause or JOIN condition can inadvertently turn a scalar subquery into a multi-row one.

Best Practices

• Be explicit about ANY or ALL whenever comparing against a subquery that may return multiple rows.
• Prefer IN / NOT IN for set-membership checks over = ANY.
• Use aggregate functions to reduce subqueries to scalar values when only one value is needed.
• Test subqueries in isolation to confirm the row count before embedding them in comparisons.

Frequently Asked Questions

Q: What is the difference between ANY and ALL in this context?
A: ANY means the comparison must be true for at least one row returned by the subquery. ALL means it must be true for every row. For example, x > ANY (...) succeeds if x is greater than the smallest value, while x > ALL (...) succeeds only if x is greater than the largest.

Q: Does ClickHouse support the SOME keyword as a synonym for ANY?
A: Yes, SOME is accepted as an alias for ANY in ClickHouse, consistent with the SQL standard.

Q: Why doesn't ClickHouse just treat it as ANY like some other databases do?
A: ClickHouse favors explicit syntax to avoid ambiguity. Implicit behavior can lead to subtle bugs, especially in analytical workloads where the distinction between ANY and ALL significantly changes results.