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.

Numeric branch types. ClickHouse has no common type for some numeric pairs (UInt64 and Int64, Int64 and Float64): it makes the column a Variant, which ORDER BY refuses, or, with use_variant_as_common_type = 0, rejects the union. So on ClickHouse the union’s orderBy sorts a numeric column whose branches may differ in type (different declared types, or different arithmetic: Id + 1 is UInt64, Id - 1 is Int64) by CAST(x AS Nullable(Float64)) and then accurateCastOrNull(x, 'Int128'), which keeps integers past 2^53 in exact order. The values themselves are not converted, so an INSERT ... SELECT or an IN over the union keeps them exact. A query over the union (fromUnion) that orders by such a column, or any other use that needs one type, should cast it in each branch.

(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", { external: true, columns: { Name: CH.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 an external table (external: true) whose name matches it and start the query there. That is what gives you typed accessors over the CTE’s columns; being external, it carries no DDL, so effect-orm generate never tries to create it.

(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", { external: true, columns: { Name: CH.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.