How to Use Subqueries in ClickHouse

Galaxy Glossary

How do I use subqueries in ClickHouse?

A subquery lets you embed one SELECT inside another to pre-filter or aggregate data before the outer query runs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problems do subqueries solve in ClickHouse?

Subqueries let you stage calculations—filters, aggregations, or joins—before the outer query. They reduce repeated logic, improve readability, and sometimes speed up execution by narrowing data early.

Where can I place a subquery?

ClickHouse accepts subqueries in the FROM, JOIN, and SELECT clauses. Correlated subqueries in WHERE are not supported; use JOINs instead.

How do I write a subquery in the FROM clause?

Wrap an inner SELECT in parentheses, give it an alias, and select from it as if it were a table.

Example: latest customers with order count

SELECT c.id, c.name, stats.order_cntFROM ( SELECT id, name FROM Customers WHERE created_at >= today() - 7) AS cLEFT JOIN ( SELECT customer_id, count() AS order_cnt FROM Orders GROUP BY customer_id) AS statsON stats.customer_id = c.id;

How do I use a scalar subquery in the SELECT list?

A scalar subquery returns a single value. ClickHouse executes it once per query.

SELECT id, name, (SELECT avg(total_amount) FROM Orders) AS avg_order_valueFROM Customers

Can I reference outer columns in a subquery?

No. ClickHouse lacks correlated subquery support. Convert the logic to a JOIN or use WITH clauses.

Best practices for subqueries

Alias every subquery, compute only needed columns, and chain WITH expressions for readability. Test performance; sometimes moving logic into a materialized view is faster.

Why How to Use Subqueries in ClickHouse is important

How to Use Subqueries in ClickHouse Example Usage


-- Products that sold out this week
SELECT p.id,
       p.name,
       o.sales_qty
FROM Products AS p
JOIN (
    SELECT product_id, sum(quantity) AS sales_qty
    FROM OrderItems
    WHERE order_id IN (
        SELECT id FROM Orders WHERE order_date >= today() - 7
    )
    GROUP BY product_id
) AS o
ON o.product_id = p.id
WHERE p.stock = 0;

How to Use Subqueries in ClickHouse Syntax


-- Subquery in FROM
SELECT cols
FROM (SELECT ... FROM table WHERE ...) AS alias
[JOIN ...]

-- Scalar subquery in SELECT list
SELECT column1,
       (SELECT agg(col) FROM table2 WHERE ...) AS alias
FROM table1;

-- Ecommerce example: total spent per customer in the last month
SELECT c.id,
       c.name,
       stats.monthly_spend
FROM Customers AS c
LEFT JOIN (
    SELECT customer_id,
           sum(total_amount) AS monthly_spend
    FROM Orders
    WHERE order_date >= date_trunc('month', now())
    GROUP BY customer_id
) AS stats
ON stats.customer_id = c.id;

Common Mistakes

Using correlated subqueries in WHERE. ClickHouse cannot evaluate outer-column references, so the query fails. Rewrite the logic as a JOIN or pre-aggregate in a subquery placed in the FROM clause.
Forgetting to alias the subquery. Without an alias, ClickHouse treats the inner SELECT as anonymous and throws a syntax error. Always add AS alias right after the closing parenthesis.