Sub-queries (IN / NOT IN / EXISTS)

inSubquery / notInSubquery / exists / notExists let a WHERE predicate reference the results of another query. Use when you'd otherwise pull a list of IDs to the app and filter client-side.

#Quick start

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

// Find every post by an actively-banned user
const blockedUserIds = queryFor(database.blockedUsers).select('userId')

const banned = await ctx.store.query(
  queryFor(database.posts)
    .where(inSubquery('userId', blockedUserIds))
    .descriptor,
)

Compiles to:

SELECT * FROM "posts" WHERE "userId" IN (SELECT "userId" FROM "blockedUsers")

The sub-query runs as part of the same SQL statement — one round- trip, the DB optimiser decides whether to materialise the inner set or use a hash semi-join.

#inSubquery / notInSubquery

Both expect the sub-query to project a single column (use .select('colName') on the inner query). The predicate matches rows whose specified outer column appears (or doesn't) in the inner result set.

// Not in
const visibleUsers = await ctx.store.query(
  queryFor(database.users)
    .where(notInSubquery('id', queryFor(database.blockedUsers).select('userId')))
    .descriptor,
)

Empty inner set semantics: inSubquery against an empty set matches NOTHING (no row's column is "in" an empty list). notInSubquery against an empty set matches EVERYTHING (every row's column is "not in" an empty list). The framework matches SQL exactly.

#exists / notExists

Doesn't bind to a specific outer column — the predicate's truth depends only on whether the sub-query produces any rows at all.

import { exists, queryFor } from '@voltro/database'

// Users who have at least one post
const authors = await ctx.store.query(
  queryFor(database.users)
    .where(exists(queryFor(database.posts).where(eq('userId', 'placeholder'))))
    .descriptor,
)

exists is typically faster than inSubquery when the inner set is large but you only need yes-or-no — the DB stops after the first match.

#Composing with and / or

Sub-query predicates compose with the regular boolean combinators:

import { and, or, eq, inSubquery } from '@voltro/database'

queryFor(database.users).where(
  and(
    eq('tenantId', tenantId),
    inSubquery('id', queryFor(database.bannedUsers).select('userId')),
  ),
)

#Limitations (v1)

Non-correlated only. The inner query can NOT reference outer-row columns like WHERE inner.userId = users.id. For correlated sub-queries (a common shape: "user who has at least one post created in the last hour") use a Self-join or an Eager-load — both can express the same query without the correlation reference.

#Reactivity

Sub-query predicates ARE reactive. The matcher tracks BOTH the outer descriptor's table AND every sub-query's table. A write to the inner table re-evaluates the outer query.

In the in-memory store the framework pre-materialises every sub-query before evaluating the outer predicate (one pass per sub-query, not per row). Same fast-path applies to SQL stores via the standard IN (SELECT ...) query plan.

#Cross-dialect

IN (SELECT ...) and EXISTS (SELECT ...) are standard SQL on every dialect we ship. No per-dialect dispatch.

#See also

• Aggregations — sub-queries paired with count() etc. for "count of X where Y belongs to Z"
• Self-joins — when the relationship can be expressed as a join instead
• CTEs — for naming a sub-query you reuse multiple times in the same outer query