Joins
Inner, left, and anti joins, plus joining against subqueries and FINAL sources.
Quarry supports the standard join shapes plus the ClickHouse-specific extras you need on real analytics tables.
Simple join
const rows = await db
.selectFrom("users as u")
.innerJoin("event_logs as e", "u.id", "e.user_id")
.select("u.id", "u.email", "e.event_type")
.execute();The third and fourth arguments are typed column references. Both must be in scope (i.e. they must belong to a table you have already added to the query).
Multi-condition join
When the join condition needs more than one equality, pass an expression callback:
const rows = await db
.selectFrom("users as a")
.innerJoin("users as b", (eb) =>
eb.and([
eb.cmpRef("a.id", "=", "b.id"),
eb.cmpRef("a.email", "=", "b.email"),
]),
)
.select("a.id", "a.email")
.execute();eb.cmpRef(left, op, right) compares two column references. Both sides must
resolve to columns that exist in the current scope.
LEFT JOIN
const rows = await db
.selectFrom("users as u")
.leftJoin("event_logs as e", "u.id", "e.user_id")
.select("u.id", "u.email", "e.event_type")
.execute();ClickHouse LEFT JOIN does not produce null for unmatched rows by
default. See
Runtime semantics
before relying on the result shape.
LEFT ANTI JOIN
Use leftAntiJoin(...) when you want rows from the left side only if no row on
the right side matches the join condition.
const rows = await db
.selectFrom("users as u")
.leftAntiJoin("event_logs as e", "u.id", "e.user_id")
.select("u.id", "u.email")
.execute();This is often a better match for ClickHouse SQL than manually rewriting the
query as NOT IN (...).
Like LEFT JOIN, ClickHouse still allows selecting right-side columns from a
LEFT ANTI JOIN, and unmatched rows come back as type defaults rather than
null unless join_use_nulls = 1 is enabled.
FINAL on a joined table source
When you need FINAL on the right-hand side of a join, build the source
explicitly with db.table(...).as(...).final():
const rows = await db
.selectFrom("users as u")
.innerJoin(
db.table("event_logs").as("e").final(),
"u.id",
"e.user_id",
)
.select("u.id", "u.email", "e.event_type")
.execute();This is the same builder you use for top-level selectFrom(...) sources, just
passed into innerJoin directly.
Joining against a subquery
A select query can be aliased and used as a join source:
const downloads = db
.selectFrom(db.table("event_logs").as("e").final())
.selectExpr((eb) => ["e.user_id", eb.fn.count().as("event_count")])
.groupBy("e.user_id")
.as("downloads");
const rows = await db
.selectFrom("users as u")
.leftJoin(downloads, "downloads.user_id", "u.id")
.select("u.id", "u.email", "downloads.event_count")
.execute();The aliased subquery contributes its columns to the surrounding scope, so
"downloads.event_count" is type-checked exactly like a real table column.
The same aliased-subquery shape also works with leftAntiJoin(...).