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" = $1)

{
  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" = $1)

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 membership over runtime values (not subqueries), use .in / .inList on the query builder. The builder picks the form for the dialect: Postgres binds one array parameter (id = ANY($1)), so the statement text is the same for every list length, and MySQL and SQLite bind one parameter per value (id in ($1, $2)).

In a raw sql string, in(...) splices a parenthesized list and works on every dialect. On Postgres you can bind one array value instead with array(...) and write the operator yourself:

where id in ($1, $2, $3)   -- in(ids)
where id = any($1)           -- array(ids), Postgres

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

select * from "sub_users" as "sub_users_ref_1" where "sub_users_ref_1"."name" = ANY($1)

{
  // Iterable form. Duplicates are removed. One array parameter.
  val ids = List(1, 2, 3, 3)
  Query[SubUser]
    .where(_.id)
    .inList(ids)
    .build
    .sql
}

select * from "sub_users" as "sub_users_ref_1" where "sub_users_ref_1"."id" = ANY($1)

{
  val ids = List(1, 2, 3)
  sql"select * from sub_users where id in ${in(ids)}".sql
}

select * from sub_users where id in ($1, $2, $3)

{
  val ids = List(1, 2, 3)
  val a   = sql"select * from sub_users where id = any(${array(ids)})".sql
  val b   = sql"select * from sub_users where name = any(${array("Alice", "Bob")})".sql
  (a, b)
}

(select * from sub_users where id = any($1),select * from sub_users where name = any($1))

in removes duplicates, like IN itself. array keeps values as given, so the same helper can store an array column. notIn / notInList are symmetric. inSubquery / notInSubquery (renamed from in/notIn on SelectQuery) cover the subquery case shown above.

Empty collections

The query builder treats an empty collection as false for inList and true for notInList on every dialect: = ANY('{}') on Postgres, and a constant predicate elsewhere. A raw in(...) cannot rewrite the SQL around it, so an empty in(...) fails with InvalidStatement before a connection is checked out.

{
  val built = Query[SubUser].where(_.id).inList(List.empty[Int]).build
  (built.sql.toLowerCase, built.issues.isEmpty)
}

(select * from "sub_users" as "sub_users_ref_1" where "sub_users_ref_1"."id" = any($1),true)

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" = $1)

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" > $1) as "high_value" where "high_value"."userId" > $2

{
  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" > $1) 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" = $1))

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" <= $1 and ("sub_timeout_rows_ref_1"."claimedBy" is null or "sub_timeout_rows_ref_1"."claimedUntil" < $2)

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" > $1 and ("sub_timeout_rows_ref_1"."claimedBy" is null or "sub_timeout_rows_ref_1"."claimedUntil" < $2 or "sub_timeout_rows_ref_1"."deadline" > $3)

{
  // 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" > $1 and ("sub_timeout_rows_ref_1"."claimedBy" is not null and "sub_timeout_rows_ref_1"."claimedUntil" > $2)

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

#EXISTS Subqueries

#Correlated Subqueries

#Derived Tables

#Complex Nested Subqueries

#Operator Reference

#Complex WHERE with OR and Grouping

#Building Complex Conditions