Unions and CTEs

unionAll

unionAll combines queries that share an output shape. TypeScript enforces the shape match, and compilation also rejects mismatched keys at runtime. Each branch is emitted in the first branch's alias order, so object insertion order cannot swap values. Nullable codecs are merged across every branch, including when selecting through fromUnion.

const recent = CH.from(Events)
	.select(($) => ({ name: $.Name }))
	.where(($) => [$.OrgId.eq("org_123"), $.Timestamp.gte("2026-01-01 00:00:00")])

const archived = CH.from(Events)
	.select(($) => ({ name: $.Name }))
	.where(($) => [$.OrgId.eq("org_123"), $.Timestamp.lt("2026-01-01 00:00:00")])

const combined = CH.unionAll(recent, archived).orderBy(["name", "asc"]).limit(100)

const compiled = CH.compileUnionUnsafe(combined, {})
// SELECT * FROM ( … UNION ALL … ) ORDER BY name ASC LIMIT 100

A CHUnionQuery is deliberately narrower than a CHQuery — it offers only orderBy, limit, offset, and format. Those wrap the union in an outer SELECT * FROM (…); the branches themselves keep whatever ordering they declared.

Unions compile with compileUnion, not compile.

Watch branch types. ClickHouse unifies column types across UNION ALL branches, and a branch pairing (say) a Float64 with a UInt64 can be rejected outright depending on server settings. Cast explicitly in each branch when the types are not already identical.

(Backed by docs/unions-and-ctes.md > unionAll with an outer ORDER BY.)

fromUnion

To aggregate across a union, wrap it as the FROM source:

const outer = CH.fromUnion(combined, "branches")
	.select(($) => ({ name: $.name, total: CH.count() }))
	.groupBy("name")

Like fromQuery, accessors are flat ($.name), and the outer query inherits the union's tenant scope — a union of branches bound to the same tenant stays scoped.

The classic use is stitching a sealed hourly rollup onto a live raw-table branch for the in-progress hour, then re-aggregating over both.

CTEs

withCTE(name, query) prepends a WITH clause. Pass the query itself — it is compiled at compile time and its tenant scope is derived, so nobody has to assert it:

const Recent = CH.table("recent", { Name: T.string })

const cte = CH.from(Events)
	.select(($) => ({ Name: $.Name }))
	.where(($) => [$.OrgId.eq("org_123")])

const query = CH.from(Recent)
	.withCTE("recent", cte)
	.select(($) => ({ name: $.Name }))

const compiled = CH.compileUnsafe(query, {})
// WITH recent AS ( SELECT Name AS Name FROM events WHERE OrgId = 'org_123' )
// SELECT Name AS name FROM recent
compiled.tenantScope // "single-tenant" — read off the CTE, not declared

To read a CTE, declare a table whose name matches it and start the query there. That is what gives you typed accessors over the CTE's columns.

(Backed by docs/unions-and-ctes.md > Selecting from a CTE.)

The string form declares its own scope

withCTE(name, sql, { tenantScope }) takes a pre-compiled body, for the rare case where that is all you have:

const cteSql = "SELECT Name FROM events WHERE OrgId = 'org_123'"

CH.from(CH.table("recent", { Name: T.string }))
	.withCTE("recent", cteSql, { tenantScope: "single-tenant" })
	.select(($) => ({ name: $.Name }))

The body is opaque here, so the builder cannot see the OrgId filter inside it. If the CTE is the query's row source and you omit tenantScope, the compiled query reads as "cross-tenant" even though its SQL is perfectly well filtered.

Two caveats worth internalising:

• The declaration is an assertion you are making, not something that gets verified. Passing tenantScope: "single-tenant" for a CTE that does not filter by tenant defeats the mechanism. This is the reason to prefer the query form.
• It only takes effect when the query's FROM names that CTE. A CTE attached to a query that reads from a different table contributes nothing to the scope.

(Backed by docs/unions-and-ctes.md > A CTE needs its tenantScope declared.)

See Tenant scoping for what tenantScope is for.