Subqueries & CTEs

Use subqueries and Common Table Expressions (CTEs) to build a query from other queries.

Query builder syntax

These examples use a typed db query builder.

Subqueries in `FROM`

Pass a query builder to db.from() to use its result as the source of another query. The outer builder only exposes columns produced by the inner query, including aggregate aliases.

import { rawAs } from '@hypequery/clickhouse';

const totalsByUser = db
  .table('orders')
  .where('created_at', 'gte', '2026-06-06')
  .select(['account_id', 'user_id'])
  .sum('amount', 'total_amount')
  .groupBy(['account_id', 'user_id']);

const totalsByAccount = await db
  .from(totalsByUser)
  .select([
    'account_id',
    rawAs<string, 'negative_total'>(
      'sumIf(total_amount, total_amount < 0)',
      'negative_total',
    ),
    rawAs<string, 'positive_total'>(
      'sumIf(total_amount, total_amount > 0)',
      'positive_total',
    ),
  ])
  .groupBy('account_id')
  .execute();

The outer query can use only the columns selected by the inner query. Parameters stay bound until rendering or execution. Use rawAs() for trusted SQL expressions and specify their result types.

Common Table Expressions (CTEs)

A CTE gives a query a name so you can read from it or join it elsewhere in the same query.

Using QueryBuilder as a CTE

Pass a query builder to withCTE(). The CTE inherits its selected columns and their types:

const activeUsersSubquery = db
  .table('users')
  .select(['id', 'user_name', 'email'])
  .where('status', 'eq', 'active');

const results = await db
  .table('orders')
  .withCTE('active_users', activeUsersSubquery)
  .innerJoin('active_users', 'user_id', 'active_users.id')
  .select([
    'orders.id',
    'orders.total',
    'active_users.user_name'
  ])
  .execute();

This generates:

WITH active_users AS (
  SELECT id, user_name, email
  FROM users
  WHERE status = 'active'
)
SELECT orders.id, orders.total, active_users.user_name
FROM orders
INNER JOIN active_users ON orders.user_id = active_users.id

Join the CTE before selecting its columns. The selected columns keep their types in the result.

CTE names in joins

Reference a CTE by its declared name. CTE joins do not accept an additional table alias.

Using Raw SQL as a CTE

Pass a SQL string and declare its output columns:

const results = await db
  .table('orders')
  .withCTE(
    'monthly_totals',
    `SELECT user_id, toStartOfMonth(created_at) AS month, SUM(total) AS monthly_sum
     FROM orders
     GROUP BY user_id, month`,
    { user_id: 'UInt64', month: 'Date', monthly_sum: 'Float64' }
  )
  .innerJoin('monthly_totals', 'user_id', 'monthly_totals.user_id')
  .select([
    'orders.id',
    'orders.created_at',
    'monthly_totals.monthly_sum'
  ])
  .execute();

The column declaration uses the same types as a generated schema. Keep it in sync with the SQL: hypequery does not check that they match.

Without declared columns, a raw CTE cannot be used as a typed source or join target. Use a literal alias such as 'monthly_totals'; an alias typed as string stays untyped.

Binding Values in a Raw CTE

Use { sql, parameters } to pass values separately from SQL. Match each {name:Type} placeholder to a key in parameters:

const results = await db
  .table('orders')
  .withCTE(
    'recent_users',
    {
      sql: `
        SELECT id
        FROM users
        WHERE organization_id = {organizationId:UUID}
          AND created_at >= {since:DateTime}
      `,
      parameters: { organizationId, since },
    },
    { id: 'UInt64' },
  )
  .innerJoin('recent_users', 'user_id', 'recent_users.id')
  .select(['orders.id'])
  .execute();

Each placeholder needs a type and a matching value. Extra values are ignored. Placeholders inside strings, comments, and heredocs are left unchanged.

hypequery preserves the declared type with a CAST. For example, {rootId:UUID} stays a UUID when used in a join.

Use these JavaScript values for compound types:

ClickHouse type	JavaScript value
`Array(T)`	An array, including nested arrays
`Tuple(...)`	An array in field order
Named `Tuple(...)`	An array in field order or an object keyed by field name
`Map(K, V)`	A plain object or a JavaScript `Map`
`JSON`	A plain object
`Nullable(T)`	A value of type `T` or `null`

These types can be nested. For example, Array(Tuple(UInt32, String)) accepts [[1, 'x']]. Unsupported object shapes and incorrect tuple lengths throw before execution. You can also pass a whole compound parameter as a string in ClickHouse's text format.

Parameter handling

Values are escaped into SQL client-side at rendering or execution time. They are not sent as ClickHouse query_params.

Reading From a CTE

Declare a CTE on db, then use .table(alias) to read from it:

const rows = await db
  .withCTE('active_users', activeUsersSubquery)
  .table('active_users')
  .select(['id', 'user_name'])
  .execute();

Selections, filters, ordering, and result rows use the CTE's declared columns. The CTE is available only to queries built from that declaration. It does not change your database schema.

You can also start from a schema table and join the CTE:

const rows = await db
  .withCTE('active_users', activeUsersSubquery)
  .table('orders')
  .innerJoin('active_users', 'user_id', 'active_users.id')
  .select(['orders.id', 'active_users.user_name'])
  .execute();

FINAL and PREWHERE apply only to table sources, so they are rejected on CTEs.

Recursive CTEs

Use withRecursiveCTE() with raw SQL to walk a hierarchy. This example follows parent–child links within one organization and returns the starting node and all reachable descendants:

const descendants = await db
  .withRecursiveCTE(
    'descendants',
    {
      sql: `
        SELECT {rootId:UUID} AS id
        UNION ALL
        SELECT link.child_id AS id
        FROM asset_link AS link
        INNER JOIN descendants AS walked
          ON link.parent_id = walked.id
        WHERE link.organization_id = {organizationId:UUID}
      `,
      parameters: { rootId, organizationId },
    },
    { id: 'UUID' },
  )
  .table('descendants')
  .select(['id'])
  .distinct()
  .execute();
// Array<{ id: string }>

The query uses WITH RECURSIVE for the whole clause. Ordinary CTEs and withScalar() aliases can share that clause.

To join a recursive CTE from a schema table, declare it on the query builder:

db.table('asset')
  .withRecursiveCTE('descendants', lineageSql, { id: 'UUID' })
  .innerJoin('descendants', 'id', 'descendants.id');

ClickHouse requirements

Enable the analyzer (enable_analyzer, on by default from ClickHouse 24.8) and separate the starting query and recursive step with UNION ALL. ClickHouse limits recursion with max_recursive_cte_evaluation_depth (1000 by default). ClickHouse reports errors if these requirements are not met.

Multiple CTEs

You can chain multiple CTEs for complex analytics:

const results = await db
  .table('events')
  .withCTE(
    'daily_users',
    'SELECT user_id, toDate(timestamp) as day, COUNT(*) as event_count FROM events GROUP BY user_id, day'
  )
  .withCTE(
    'active_users',
    'SELECT user_id, COUNT(DISTINCT day) as active_days FROM daily_users GROUP BY user_id HAVING active_days > 7',
    { user_id: 'UInt64', active_days: 'UInt64' }
  )
  .innerJoin('active_users', 'user_id', 'active_users.user_id')
  .select(['events.user_id', 'events.timestamp'])
  .execute();

Scalar `WITH` Aliases

ClickHouse also supports scalar expressions in the WITH clause. Use withScalar() when you want to define an expression once and reuse it in SELECT, WHERE, and ORDER BY.

const results = await db
  .table('orders')
  .withScalar('user_name', expr =>
    expr.ch.dictGet<string>('users_dict', 'name', expr.col('user_id'))
  )
  .select(['order_id', 'amount', 'user_name'])
  .where('user_name', 'like', '%Alice%')
  .orderBy('user_name', 'ASC')
  .limit(50)
  .execute();

This generates:

WITH dictGet('users_dict', 'name', user_id) AS user_name
SELECT order_id, amount, user_name
FROM orders
WHERE user_name LIKE '%Alice%'
ORDER BY user_name ASC
LIMIT 50

Raw SQL Expressions

Use raw SQL for expressions the query builder does not cover.

HAVING Clauses

Use raw() to add a HAVING condition:

const results = await db
  .table('orders')
  .select(['user_id'])
  .sum('total', 'total_spent')
  .groupBy(['user_id'])
  .raw('SUM(total) > 1000')
  .raw('COUNT(DISTINCT product_id) >= 3')
  .execute();

This generates:

SELECT user_id, SUM(total) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(total) > 1000 AND COUNT(DISTINCT product_id) >= 3

Subqueries in `WHERE`

Use inSubquery with a SQL string to filter by another query's results:

const results = await db
  .table('products')
  .select('*')
  .where('category_id', 'inSubquery', 'SELECT id FROM categories WHERE active = 1')
  .execute();

See Subquery IN for more examples.

Subqueries & CTEs

Subqueries & CTEs

Subqueries in FROM

Common Table Expressions (CTEs)

Using QueryBuilder as a CTE

Using Raw SQL as a CTE

Binding Values in a Raw CTE

Reading From a CTE

Recursive CTEs

Multiple CTEs

Scalar WITH Aliases

Raw SQL Expressions

HAVING Clauses

Subqueries in WHERE

On this page

Subqueries in `FROM`

Scalar `WITH` Aliases

Subqueries in `WHERE`