Subqueries

The Query builder supports type-safe subqueries for IN, NOT IN, EXISTS, and derived tables.

IN Subqueries

Use .select(_.column) to create a typed subquery, then pass it to .in():

{
  // Type-safe IN subquery - column types must match
  val activeUserIds = Query[SubOrder]
    .where(_.status)
    .eq("active")
    .select(_.userId) // Returns SelectQuery[Int]

  Query[SubUser]
    .where(_.id)
    .inSubquery(activeUserIds) // Compiles: both are Int
    .build
    .sql
}

select * from sub_users as sub_users_ref_1 where sub_users_ref_1.id in (select userId from sub_orders as sub_orders_ref_1 where sub_orders_ref_1.status = ?)

{
  val activeUserIds = Query[SubOrder]
    .where(_.status)
    .eq("active")
    .select(_.userId)

  // NOT IN subquery
  Query[SubUser]
    .where(_.id)
    .notInSubquery(activeUserIds)
    .build
    .sql
}

select * from sub_users as sub_users_ref_1 where sub_users_ref_1.id not in (select userId from sub_orders as sub_orders_ref_1 where sub_orders_ref_1.status = ?)

The type safety is enforced at compile time — if the column types don't match, it won't compile. The snippet below tries to match an Int column against a SelectQuery[String]; the real compiler error is shown beneath it:

import saferis.*
import saferis.postgres.given

@tableName("sub_users_fail")
case class SubUser(@generated @key id: Int, name: String) derives Table

@tableName("sub_orders_fail")
case class SubOrder(@generated @key id: Int, userId: Int, status: String) derives Table

// .select(_.status) is SelectQuery[String]; _.id is an Int column — mismatch.
val statuses = Query[SubOrder].where(_.userId).gt(0).select(_.status)
Query[SubUser].where(_.id).inSubquery(statuses).build.sql

Found:    (statuses : saferis.SelectQuery[String])
Required: saferis.SelectQuery[Int]
  Query[SubUser].where(_.id).inSubquery(statuses).build.sql

IN Literal Collections

For IN-clauses over runtime values (not subqueries), use in (varargs) for inline literals or inList for any Iterable[T]. Both work in the typed Query DSL and via the top-level in(...) helper inside sql"..." interpolation.

Query[SubUser]
  .where(_.name)
  .in("Alice", "Bob")
  .build
  .sql

select * from sub_users as sub_users_ref_1 where sub_users_ref_1.name in (?, ?)

{
  // Iterable form — for runtime collections (List, Set, Vector, LinkedHashSet, ranges, ...)
  val ids = List(1, 2, 3, 3) // duplicates are removed automatically
  Query[SubUser]
    .where(_.id)
    .inList(ids)
    .build
    .sql
}

select * from sub_users as sub_users_ref_1 where sub_users_ref_1.id in (?, ?, ?)

{
  // Same helpers in raw sql"..." interpolation:
  val ids = List(1, 2, 3)
  val a   = sql"select * from sub_users where id in ${in(ids)}".sql
  val b   = sql"select * from sub_users where name in ${in("Alice", "Bob")}".sql
  (a, b)
}

(select * from sub_users where id in (?, ?, ?),select * from sub_users where name in (?, ?))

notIn / notInList are symmetric. inSubquery / notInSubquery (renamed from in/notIn on SelectQuery) cover the subquery case shown above.

Empty collections fail at construction, not in the database

An empty (or all-duplicates-collapse-to-empty) input would produce invalid SQL (IN ()) on every supported dialect. Saferis does not throw at the call site — instead the resulting fragment carries one FragmentIssue.EmptyCollection per offending helper. When the fragment is run, execution fails with SaferisError.InvalidStatement(issues) before any JDBC call.

The recommended recovery pattern catches InvalidStatement and substitutes an empty result — no DB round-trip:

{
  // Recovery pattern for possibly-empty collections — no DB round-trip on failure:
  val ids2      = List.empty[Int]
  val recovered =
    xa.run(Query[SubUser].where(_.id).inList(ids2).query[SubUser])
      .catchSome { case _: SaferisError.InvalidStatement => ZIO.succeed(Chunk.empty[SubUser]) }
  recovered.either
}

Right(Chunk())

Without that recovery, running the empty-collection query fails with SaferisError.InvalidStatement — and crucially it fails before any JDBC call. The example below inspects the failure to confirm both facts (the output is shown beneath it):

xa.run(Query[SubUser].where(_.id).inList(List.empty[Int]).query[SubUser]).either.map {
  case Left(_: SaferisError.InvalidStatement) => "Failed with InvalidStatement (no JDBC call made)"
  case Left(other)                            => s"Failed with ${other.getClass.getSimpleName}"
  case Right(rows)                            => s"Unexpectedly succeeded with ${rows.size} rows"
}

Failed with InvalidStatement (no JDBC call made)

If you want to surface issues independently of execution, call fragment.validate on any SqlFragment — it succeeds with the fragment if there are no issues, fails with InvalidStatement(issues) otherwise. Multiple offending splices in a single statement accumulate into the same InvalidStatement, so you fix them all at once.

EXISTS Subqueries

Use whereExists() or whereNotExists():

Query[SubUser]
  .whereExists(Query[SubOrder].all)
  .build
  .sql

select * from sub_users as sub_users_ref_1 where exists (select * from sub_orders as sub_orders_ref_1)

Query[SubUser]
  .whereNotExists(Query[SubOrder].where(_.status).eq("cancelled"))
  .build
  .sql

select * from sub_users as sub_users_ref_1 where not exists (select * from sub_orders as sub_orders_ref_1 where sub_orders_ref_1.status = ?)

Correlated Subqueries

For correlated subqueries, use sql"..." to reference outer table columns:

{
  val users = Table[SubUser]

  // Correlated EXISTS - find users who have at least one order
  Query[SubUser]
    .whereExists(
      Query[SubOrder].where(sql"userId = ${users.id}")
    )
    .build
    .sql
}

select * from sub_users as sub_users_ref_1 where exists (select * from sub_orders as sub_orders_ref_1 where userId = id)

Derived Tables

Use subqueries in the FROM clause with Query.from():

{
  // Create a typed subquery
  val highValueOrders = Query[DerivedOrder]
    .where(_.amount)
    .gt(BigDecimal(100))
    .selectAll[OrderSummary] // Returns SelectQuery[OrderSummary]

  // Use as derived table with explicit alias
  Query
    .from(highValueOrders, "high_value")
    .where(_.userId)
    .gt(0)
    .build
    .sql
}

select * from (select * from sub_derived_orders as sub_derived_orders_ref_1 where sub_derived_orders_ref_1.amount > ?) as high_value where high_value.userId > ?

{
  val highValueOrders = Query[DerivedOrder]
    .where(_.amount)
    .gt(BigDecimal(100))
    .selectAll[OrderSummary]

  // Derived table with join
  Query
    .from(highValueOrders, "summary")
    .innerJoin[DerivedUser]
    .on(_.userId)
    .eq(_.id)
    .all
    .build
    .sql
}

select * from (select * from sub_derived_orders as sub_derived_orders_ref_1 where sub_derived_orders_ref_1.amount > ?) as summary inner join sub_derived_users as sub_derived_users_ref_1 on summary.userId = sub_derived_users_ref_1.id

Complex Nested Subqueries

Subqueries can be arbitrarily complex - they support joins, nested subqueries, and all Query features:

{
  // Nested subquery: find users who ordered electronics
  val electronicProductIds = Query[ComplexProduct]
    .where(_.category)
    .eq("electronics")
    .select(_.id)

  val usersWithElectronics = Query[ComplexOrder]
    .where(_.productId)
    .inSubquery(electronicProductIds)
    .select(_.userId)

  Query[ComplexUser]
    .where(_.id)
    .inSubquery(usersWithElectronics)
    .build
    .sql
}

select * from sub_complex_users as sub_complex_users_ref_1 where sub_complex_users_ref_1.id in (select userId from sub_complex_orders as sub_complex_orders_ref_1 where sub_complex_orders_ref_1.productId in (select id from sub_complex_products as sub_complex_products_ref_1 where sub_complex_products_ref_1.category = ?))

Operator Reference

All available operators in Operator:

Operator	SQL	Notes
`Eq`	`=`	Standard equality
`Neq`	`<>`	Standard inequality
`Lt`	`<`	Less than
`Lte`	`<=`	Less than or equal
`Gt`	`>`	Greater than
`Gte`	`>=`	Greater than or equal
`Like`	`like`	Pattern matching
`ILike`	`ilike`	Case-insensitive LIKE (PostgreSQL)
`SimilarTo`	`similar to`	Regex pattern (PostgreSQL)
`RegexMatch`	`~`	Regex match (PostgreSQL)
`RegexMatchCI`	`~*`	Case-insensitive regex (PostgreSQL)
`IsNull`	`is null`	Null check
`IsNotNull`	`is not null`	Non-null check

Complex WHERE with OR and Grouping

For queries with complex OR logic, use andWhere with a lambda:

{
  // Find rows that are due AND either unclaimed or with expired claims
  val now = java.time.Instant.now()
  Query[TimeoutRow]
    .where(_.deadline)
    .lte(now)
    .andWhere(w => w(_.claimedBy).isNull.or(_.claimedUntil).lt(Some(now)))
    .build
    .sql
}

select * from sub_timeout_rows as sub_timeout_rows_ref_1 where sub_timeout_rows_ref_1.deadline <= ? and (sub_timeout_rows_ref_1.claimedBy is null or sub_timeout_rows_ref_1.claimedUntil < ?)

This generates: select ... where deadline <= ? and (claimed_by is null or claimed_until < ?)

The parentheses are automatically added around the grouped conditions.

Building Complex Conditions

The andWhere lambda provides a fluent builder:

{
  // Multiple OR conditions
  val now = java.time.Instant.now()
  Query[TimeoutRow]
    .where(_.id)
    .gt(0)
    .andWhere(w =>
      w(_.claimedBy).isNull
        .or(_.claimedUntil)
        .lt(Some(now))
        .or(_.deadline)
        .gt(now)
    )
    .build
    .sql
}

select * from sub_timeout_rows as sub_timeout_rows_ref_1 where sub_timeout_rows_ref_1.id > ? and (sub_timeout_rows_ref_1.claimedBy is null or sub_timeout_rows_ref_1.claimedUntil < ? or sub_timeout_rows_ref_1.deadline > ?)

{
  // AND within the group
  val now = java.time.Instant.now()
  Query[TimeoutRow]
    .where(_.id)
    .gt(0)
    .andWhere(w =>
      w(_.claimedBy).isNotNull
        .and(_.claimedUntil)
        .gt(Some(now))
    )
    .build
    .sql
}

select * from sub_timeout_rows as sub_timeout_rows_ref_1 where sub_timeout_rows_ref_1.id > ? and (sub_timeout_rows_ref_1.claimedBy is not null and sub_timeout_rows_ref_1.claimedUntil > ?)

Available operations in the andWhere builder:

Method	Description
`w(_.column)`	Start condition on a column
`.eq(value)`	Equality check
`.neq(value)`	Not equal
`.lt(value)` / `.lte(value)`	Less than (or equal)
`.gt(value)` / `.gte(value)`	Greater than (or equal)
`.isNull` / `.isNotNull`	Null checks
`.or(_.column)`	Chain with OR
`.and(_.column)`	Chain with AND

Subqueries

#IN Subqueries

#IN Literal Collections

#Empty collections fail at construction, not in the database

#EXISTS Subqueries

#Correlated Subqueries

#Derived Tables

#Complex Nested Subqueries

#Operator Reference

#Complex WHERE with OR and Grouping

#Building Complex Conditions