Set operations

Combine the result rows of two or more compiled queries. union dedupes, unionAll doesn't, intersect keeps rows present in both, except subtracts. The engine renumbers placeholders so the merged query has one shared parameter list.

#The six helpers

Export	SQL	Notes
`union(parts)`	`A UNION B`	Deduplicates.
`unionAll(parts)`	`A UNION ALL B`	Preserves duplicates. Cheaper.
`intersect(parts)`	`A INTERSECT B`	Rows present in both.
`intersectAll(parts)`	`A INTERSECT ALL B`	Multiset intersection.
`except(parts)`	`A EXCEPT B`	Rows in A not in B.
`exceptAll(parts)`	`A EXCEPT ALL B`	Multiset difference.

Each helper takes a CompiledQuery[] with at least two parts. Pass three or more and they fold left to right.

#Compiling a query without executing it

Set operators consume CompiledQuery — get one with .toSQL() on the relational builder, or by using helpers like search() that already return a compiled query.

14 lines


1import { unionAll } from 'zanith';
2 
3const adminsQ = db.user.query()
4  .select(({ user }) => ({ id: user.id, email: user.email }))
5  .where(({ user }) => user.role.eq('ADMIN'))
6  .toSQL();
7 
8const moderatorsQ = db.user.query()
9  .select(({ user }) => ({ id: user.id, email: user.email }))
10  .where(({ user }) => user.role.eq('MODERATOR'))
11  .toSQL();
12 
13const merged = unionAll([adminsQ, moderatorsQ]);
14const { rows } = await adapter.execute(merged);

#Parameter renumbering

Each part's $1, $2, … placeholders get shifted so the merged query has one continuous list. The merged params is just [...partA.params, ...partB.params] — no de-duplication, no re-ordering.

4 lines


1// partA.params = ['ADMIN'],     sql contains $1
2// partB.params = ['MODERATOR'], sql contains $1
3// merged.params = ['ADMIN', 'MODERATOR']
4// merged.sql contains $1 (from A) and $2 (renumbered from B)

#Dialect support

Each operator is capability-gated. setUnion, setIntersect, and setExcept are flags on the dialect — Postgres has all three; SQLite ships UNION and INTERSECT but not the ALL variants for them in older versions. Calling an unsupported operator throws.

#When to reach for set ops

Use case	Operator
Merge results from two unrelated tables that share a shape (audit log + system events).	`unionAll`
Combine a recommendation list with a manually curated list, dedup by id.	`union` + DISTINCT ON
Show users who exist in both paying and active cohorts.	`intersect`
Find users in signed_up but not in converted.	`except`
Avoid `IN (...)` on huge id lists — sometimes a `UNION ALL` of small SELECTs plans better.	`unionAll`