Set operations (UNION / INTERSECT / EXCEPT)

union / unionAll / intersect / except combine two or more queries that produce the same column shape. The result is one unified row set you can sort, paginate, and aggregate against.

#Quick start

import { union, eq, queryFor } from '@voltro/database'

// Active + archived tickets for an org, treated as one list
const all = await ctx.store.query(
  union(
    queryFor(database.activeTickets).where(eq('orgId', oid)),
    queryFor(database.archivedTickets).where(eq('orgId', oid)),
  ).orderBy('createdAt', 'desc').limit(50).descriptor,
)

PostgreSQL example:

(SELECT * FROM "activeTickets" WHERE "orgId" = $1)
UNION
(SELECT * FROM "archivedTickets" WHERE "orgId" = $2)
ORDER BY "createdAt" DESC
LIMIT 50

The outer .orderBy() / .limit() / .offset() apply to the combined result. Each input keeps its own filter, ordering and pagination. An input limit therefore selects rows before the set operation; only the outer ordering determines the order of the final result.

#The four operations

Helper	Semantics
union(...)	Rows from any input, deduplicated
unionAll(...)	Rows from any input, NO dedup (faster + preserves duplicates)
intersect(...)	Rows present in EVERY input
except(...)	Rows in the FIRST input, NOT in any subsequent input

Each accepts 2+ queries. One input throws (requires at least two queries).

#When to reach for each

• union — "show this user's items from two different sources, deduped" (a notifications feed mixed with system messages).
• unionAll — same as union but you know there are no duplicates OR you specifically want to keep them. Skipping the dedup pass is meaningfully faster on large inputs.
• intersect — "users who exist in BOTH the paying-customers list AND the active-this-week list".
• except — "all users EXCEPT those who unsubscribed". Use notInSubquery if you only need a column-level check; except when you're operating on full row shapes.

#Three-way and beyond

All four accept any number of inputs (≥ 2). The compiler chains them with the appropriate keyword:

const combined = union(
  queryFor(database.eventsA),
  queryFor(database.eventsB),
  queryFor(database.eventsC),
)
// (SELECT * FROM "eventsA") UNION (SELECT * FROM "eventsB") UNION (SELECT * FROM "eventsC")

#Column shape requirement

Every input MUST produce the same column shape. The framework doesn't enforce this at TypeScript level — the DB throws at query time if shapes don't line up. To narrow each input, use .select(...cols) on the inner queries so they project the same column set.

#Cross-dialect

SQLite-family clients wrap each input in SELECT * FROM (...), preserving branch-local ordering and limits, including nested set operations. An outer .offset() without .limit() uses SQLite’s unlimited-limit form. PostgreSQL retains parenthesized input queries as shown above.

#Reactivity

Reactive — coarsely. The engine registers the subscription against every branch's source table, so a write to any branch (the UNION / INTERSECT / EXCEPT side) re-runs the combined query. It re-queries on any contributing-table change rather than pre-filtering per column, so keep the branches' result sets bounded.

#See also

• Sub-queries — notInSubquery for the column-level "in A but not in B" case
• Aggregations — count() etc. on a set-op result is a common pattern
• CTEs — name a complex set-op result so you can reference it in a larger query