Joins and subqueries

Joining a table

Each join takes the table, an alias, and an ON callback receiving both sides:

const query = CH.from(Events, "e")
	.innerJoin(Services, "s", (main, joined) => main.Name.eq(joined.Name).and(main.OrgId.eq(joined.OrgId)))
	.select(($) => ({ name: $.Name, team: $.s.Team }))
	.where(($) => [$.OrgId.eq("org_123")])

// SELECT e.Name AS name, s.Team AS team
// FROM events AS e
// INNER JOIN services AS s ON (e.Name = s.Name AND e.OrgId = s.OrgId)
// WHERE e.OrgId = 'org_123'

Once a join exists, the accessor gains a key per alias. Main-table columns stay at the top level ($.Name), joined columns sit under their alias ($.s.Team). Alias the main table in from() so its references qualify too.

The tenant-key equality prevents same-named services from another tenant joining the result. Filtering only e.OrgId does not constrain an independently joined tenant table.

Available: innerJoin, leftJoin, crossJoin (which takes no ON callback).

(Backed by docs/joins-and-subqueries.md > Joining a table.)

`leftJoin` nullability

leftJoin wraps the joined side in NullableColumnDefs, so its columns infer as T | null in the output row. Derived codecs accept NULL when join_use_nulls=1 and the column defaults ClickHouse emits with join_use_nulls=0. This applies to leftJoinQuery too. An explicitly declared rowSchema must also accept the nullable joined columns.

Joining a subquery

innerJoinQuery, leftJoinQuery, and crossJoinQuery take a CHQuery instead of a table. The inner query's output shape becomes the joined column set, so aliases you selected are what you join on:

const perTeam = CH.from(Services)
	.select(($) => ({ name: $.Name, team: $.Team }))
	.where(($) => [$.OrgId.eq("org_123")])

CH.from(Events, "e")
	.innerJoinQuery(perTeam, "s", (main, joined) => main.Name.eq(joined.name))
	.select(($) => ({ team: $.s.team }))
	.where(($) => [$.OrgId.eq("org_123")])

Subquery in `FROM`

fromQuery(query, alias) starts a new query over another query's output:

const inner = CH.from(Events)
	.select(($) => ({ name: $.Name, ms: $.DurationMs }))
	.where(($) => [$.OrgId.eq("org_123")])

const outer = CH.fromQuery(inner, "sub")
	.select(($) => ({ name: $.name, worst: CH.max($.ms) }))
	.groupBy("name")

// SELECT name AS name, max(ms) AS worst
// FROM (SELECT Name AS name, DurationMs AS ms FROM events WHERE OrgId = 'org_123') AS sub
// GROUP BY name

Accessors are flat. Inside the outer query the inner columns are $.name and $.ms — not $.sub.name. The alias names the derived table in SQL; it does not namespace the accessor. Reaching for $.sub.name throws at runtime.

The outer query inherits the inner query's tenant scope: a scoped subquery cannot leak other tenants' rows, so the outer stays "single-tenant" even with no WHERE of its own. See Tenant scoping.

(Backed by docs/joins-and-subqueries.md > Subquery in FROM uses flat accessors and > A scoped subquery keeps the outer query scoped.)

Subquery conditions

inSubquery(expr, subquery) → expr IN (…)
notInSubquery(expr, subquery) → expr NOT IN (…), i.e. an anti-join as a predicate
exists(subquery) → EXISTS (…)
outerRef<T>(name) — reference an outer column from inside the inner query, e.g. outerRef("e.TraceId")

Pass the query itself. It is compiled where it is spliced, so its params, table names and column types stay checked:

const excluded = CH.from(Events)
	.select(($) => ({ n: $.Name }))
	.where(($) => [$.OrgId.eq(CH.param.string("orgId"))])

const query = CH.from(Services, "s")
	.select(($) => ({ team: $.Team }))
	.where(($) => [$.OrgId.eq(CH.param.string("orgId")), CH.notInSubquery($.Team, excluded)])

const compiled = CH.compileUnsafe(query, { orgId: "org_123" })
// … WHERE OrgId = 'org_123' AND Team NOT IN (SELECT Name AS n FROM events WHERE OrgId = 'org_123')

Params inside the subquery resolve from the outer param set: placeholders survive the splice and are substituted in one pass at the end, so orgId above is passed once and reaches both.

For correlated subqueries, outerRef names a column of the enclosing query:

const inner = CH.from(Events)
	.select(($) => ({ n: $.Name }))
	.where(($) => [$.OrgId.eq("org_123"), $.Name.eq(CH.outerRef("s.Name"))])

CH.from(Services, "s")
	.select(($) => ({ team: $.Team }))
	.where(($) => [$.OrgId.eq("org_123"), CH.exists(inner)])

All three also accept a pre-compiled SQL string, for the rare case where that is all you have.

A subquery never scopes its outer query. WHERE x IN (SELECT y FROM t WHERE OrgId = 'a') does not confine the outer read to org a — nothing stops org b holding the same y. The outer query still needs its own tenant predicate, or a row source that is itself scoped. This is true whether you pass a query or a string.

Membership and NULLs. Do not assume another SQL database's NOT IN behavior applies. ClickHouse membership semantics depend on transform_null_in; with its default of 0, NULL does not match another NULL in a set. Use non-nullable keys or define your NULL policy explicitly. See ClickHouse NULL processing.

Splicing a subquery where there is no syntax for one

exists / inSubquery / notInSubquery put a subquery where SQL expects a subquery. Sometimes you need its SQL text somewhere the builder has no syntax for — inside an aggregate, a tuple comparison, a hand-written predicate:

Timestamp >= (SELECT min(ts) FROM (<a cheap scan>))

That is what subqueryExpr is for. It takes the inner query, the column type its result decodes as, and a wrap function that receives the inner SQL and returns the expression text:

import { subqueryCond, subqueryExpr } from "@maple-dev/effect-clickhouse"

// Stage 1: a cheap scan reading only the sort column.
const cheapScan = CH.from(Events)
	.select(($) => ({ ts: $.Timestamp }))
	.where(($) => [$.OrgId.eq(CH.param.string("orgId"))])
	.orderBy(["ts", "desc"])
	.limit(100)

const cutoff = subqueryExpr(cheapScan, T.dateTime, (sql) => `(SELECT min(ts) FROM (${sql}))`)

// Stage 2: the heavy columns, read only for rows at or after the cutoff.
const query = CH.from(Events)
	.select(($) => ({ name: $.Name, attrs: $.Attributes }))
	.where(($) => [$.OrgId.eq(CH.param.string("orgId")), $.Timestamp.gte(cutoff)])

subqueryCond is the same thing as a predicate, and untypedSubqueryExpr the same thing for a value that never becomes a row (which costs the query its row schema if selected — see Decoding results):

CH.from(Events)
	.select(($) => ({ name: $.Name }))
	.where(($) => [
		$.OrgId.eq(CH.param.string("orgId")),
		subqueryCond(cheapScan, (sql) => `Timestamp IN (SELECT ts FROM (${sql}))`),
	])

The inner query is compiled by the outer compile, not when you build the expression. That is the whole point of these three, and the reason not to reach for compileUnsafe(inner).sql and a template string of your own:

Params inside the spliced SQL resolve from the outer param set, in the same single substitution pass as everything else — exactly like the subquery conditions above.
A value the inner query cannot encode fails the outer compile, so it lands in the error channel where a route can catchTag it. Compiling the inner query yourself runs the compiler while the outer query is still being built — outside any Effect — so the same bad value is a synchronous throw from your query-definition function instead.

You are assembling SQL text inside wrap: interpolate only values you control, and route user-supplied string literals through compile(str(value)) from the /sql subpath. str() returns a fragment that must be compiled before interpolation into SQL text. Keep identifiers and SQL structure under application control; see the escaped SQL example.

(Backed by docs/joins-and-subqueries.md > subqueryExpr splices an inner query, compiled by the outer compile.)

Membership helpers

inList(expr, values) — expr IN ('a', 'b') for a string list
inExprList(expr, exprs) — same, for expression lists
notInList(expr, values) — available from the root and /expr subpath

These predate .in_() and remain useful when you have an array in hand rather than varargs.

Join size and duplicate matches

A normal join can emit several rows for one left-hand row when multiple right-hand rows match. If you need one service row per key, aggregate or deduplicate the right side according to your business rule before joining. The typed join helpers do not expose ANY/SEMI/ANTI strictness or an algorithm selector; compilation alone does not choose a faster algorithm for your data.

Filter derived tables before joining when practical, especially tenant and time filters. Inspect the actual plan and pass join_algorithm through your client's settings when needed. Check filtering and algorithm choices against your workload. ANY semantics are appropriate only when one match is sufficient. See ClickHouse join guidance.

Joins and subqueries

On this page