Query Operations

Subqueries

Correlated subqueries and nested queries

You can use a combine filter and map functions to create correlated subqueries. For example, let's say we want to find all the people over the average age:

val q = sql {
  Table<Person>().filter { p -> p.age > Table<Person>().map { it.age }.avg() }
}
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.age > (SELECT avg(p1.age) FROM Person p1)

Correlated Subqueries in Select Clauses

Correlated subqueries can also be used within sql.select blocks in where(), sortBy(), and groupBy() clauses.

Correlated Subqueries in `where()` Clauses

You can filter based on the existence of related data or aggregate values from other tables:

data class Person(val id: Int, val name: String, val age: Int)
data class Order(val id: Int, val personId: Int, val amount: Double)

// Find people who have at least one order (EXISTS pattern)
val q = sql.select {
  val p = from(Table<Person>())
  where { Table<Order>().filter { o -> o.personId == p.id }.isNotEmpty() }
  p
}
//> SELECT p.id, p.name, p.age FROM Person p
//> WHERE EXISTS (SELECT 1 FROM Order o WHERE o.personId = p.id)

// Using the 'in' operator with a subquery
val q2 = sql {
  Table<Person>().filter { p -> p.id in Table<Order>().map { o -> o.personId } }
}
//> SELECT p.id, p.name, p.age FROM Person p
//> WHERE p.id IN (SELECT o.personId FROM Order o)

Existence Checks (EXISTS / NOT EXISTS)

ExoQuery provides .isNotEmpty() and .isEmpty() to check if a subquery returns any rows. These translate directly to SQL EXISTS and NOT EXISTS patterns.

Use .isNotEmpty() to filter rows where related data exists:

// Find people who have at least one order
val q = sql {
  Table<Person>().filter { p ->
    Table<Order>().filter { o -> o.personId == p.id }.isNotEmpty()
  }
}
//> SELECT p.id, p.name, p.age FROM Person p
//> WHERE EXISTS (
//>   SELECT o.id AS id, o."personId" AS personId, o.total AS total
//>   FROM "Order" o
//>   WHERE o."personId" = p.id
//> )

NOT EXISTS - Check for Missing Data

Use .isEmpty() to filter rows where related data does NOT exist:

// Find people who have no orders
val q = sql {
  Table<Person>().filter { p ->
    Table<Order>().filter { o -> o.personId == p.id }.isEmpty()
  }
}
//> SELECT p.id, p.name, p.age FROM Person p
//> WHERE NOT EXISTS (SELECT 1 FROM Order o WHERE o.personId = p.id)

Complex Existence Conditions

You can combine existence checks with other conditions:

// Find adults with no orders
val q = sql {
  Table<Person>().filter { p ->
    p.age >= 18 && Table<Order>().filter { o -> o.personId == p.id }.isEmpty()
  }
}
//> SELECT p.id, p.name, p.age FROM Person p
//> WHERE p.age >= 18 AND NOT EXISTS (SELECT 1 FROM Order o WHERE o.personId = p.id)

Correlated Subqueries in `sortBy()` Clauses

You can sort results by values computed in a subquery. Note the use of .value() to treat the subquery result as a scalar:

// Sort people by the total sum of their orders
val q = sql.select {
  val p = from(Table<Person>())
  sortBy(
    Table<Order>()
      .filter { o -> o.personId == p.id }
      .map { o -> o.amount }
      .sum()
      .value() to Ord.Asc
  )
  p
}
//> SELECT p.id, p.name, p.age FROM Person p
//> ORDER BY (SELECT sum(o.amount) FROM Order o WHERE o.personId = p.id) ASC

Correlated Subqueries in `groupBy()` Clauses

Subqueries can even define grouping criteria:

// Group by a calculated value from a subquery
val q = sql.select {
  val p = from(Table<Person>())
  val orderCount = Table<Order>().filter { o -> o.personId == p.id }.count().value()
  groupBy(orderCount)
  orderCount to count(p.id)
}
//> SELECT (SELECT count(*) FROM Order o WHERE o.personId = p.id), count(p.id)
//> FROM Person p GROUP BY (SELECT count(*) FROM Order o WHERE o.personId = p.id)

Complex Aggregations with `.value()`

Recall that you could table the .map function with table aggregations. Let's say you want to get not average but also use that together with another aggreagtor (e.g. the average minus the minimum). Normally you could use an expression avg(p.age) + min(p.age) with a the .map function.

val customExpr: SqlQuery<Double> = sql {
    Table<Person>().map { p -> avg(p.age) + min(p.age) } 
  }

If you want to use a statement like this inside of a correlated subquery, we can use the .value() function inside of a capture block to convert a SqlQuery<T> into just T.

val q = sql {
  Table<Person>().filter { p -> 
    p.age > Table<Person>().map { p -> avg(p.age) + min(p.age) }.value() 
      && p.name == "Joe"
  }
}
q.buildFor.Postgres().runOn(myDatabase)
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.age > (SELECT avg(p1.age) + min(p1.age) FROM Person p1)

Forcing Subqueries with `.nested()`

ExoQuery automatically tries to flatten queries whenever possible to produce more efficient SQL. However, there are situations where you need to explicitly force a query to be treated as a subquery rather than being flattened. This is where the .nested() function comes in.

Why Use `.nested()`?

Use .nested() when you need to:

Control operation ordering: Some databases require certain operations to occur in a specific order. For example, in SQLite, ORDER BY must come after UNION operations so you can use .nested() to wrap the union query so that it comes first.
Force subquery boundaries: When you want to ensure that filtering or other operations are contained within a subquery before being used in an outer query.
Preserve query structure: When automatic flattening would change the semantics of your query.

Basic Example

Here's a comparison showing the difference between a query with and without .nested():

data class Person(val id: Int, val name: String, val age: Int)
data class Address(val ownerId: Int, val street: String, val city: String)
data class Robot(val ownerId: Int, val name: String, val model: String)

// Without .nested() - query is flattened
val peopleFlat = sql {
  Table<Person>().flatMap { p ->
    sql.select {
      val a = from(Table<Address>().filter { a -> a.ownerId == p.id })
      val r = join(Table<Robot>()) { r -> r.ownerId == p.id }
      Triple(p, a, r)
    }
  }
}
//> SELECT p.id, p.name, p.age, a.ownerId, a.street, a.city, r.ownerId, r.name, r.model
//>   FROM Person p, Address a
//>   INNER JOIN Robot r ON r.ownerId = p.id
//>   WHERE a.ownerId = p.id

// With .nested() - Address query remains a subquery
val peopleNested = sql {
  Table<Person>().flatMap { p ->
    sql.select {
      val a = from(Table<Address>().filter { a -> a.ownerId == p.id }.nested())
      val r = join(Table<Robot>()) { r -> r.ownerId == p.id }
      Triple(p, a, r)
    }
  }
}
//> SELECT p.id, p.name, p.age, a.ownerId, a.street, a.city, r.ownerId, r.name, r.model
//>   FROM Person p,
//>   (SELECT a.ownerId, a.street, a.city FROM Address a WHERE a.ownerId = p.id) AS a
//>   INNER JOIN Robot r ON r.ownerId = p.id

Notice how .nested() forces the Address query to remain as a subquery (SELECT ... FROM Address a WHERE ...) instead of being flattened into the main query.

Using `.nested()` with Simple Queries

You can also use .nested() to force simple queries like map operations to become subqueries:

// Force a map operation to be a subquery
val names = sql { Table<Person>().map { p -> p.name }.nested() }

val result = sql.select {
  val n = from(names)
  val r = join(Table<Robot>()) { r -> n == r.model }
  n to r
}
//> SELECT n.value, r.ownerId, r.name, r.model
//>   FROM (SELECT p.name AS value FROM Person p) AS n
//>   INNER JOIN Robot r ON n.value = r.model

Multiple Levels of Nesting

You can even apply .nested() multiple times to create multiple levels of subquery nesting:

val deeplyNested = sql {
  Table<Person>().map { p -> p.name }.nested().nested()
}
//> Produces nested subqueries: SELECT * FROM (SELECT * FROM (SELECT p.name FROM Person p))

Key Points

Automatic flattening: By default, ExoQuery flattens queries for better performance
Explicit control: Use .nested() when you need to prevent flattening and maintain subquery structure
Database-specific needs: Some SQL operations require specific ordering (e.g., SQLite's UNION + ORDER BY)
Type preservation: .nested() returns the same SqlQuery<T> type, so it integrates seamlessly into query composition

Correlated Subqueries in Select Clauses

Correlated Subqueries in where() Clauses

Existence Checks (EXISTS / NOT EXISTS)

EXISTS - Check for Related Data

NOT EXISTS - Check for Missing Data

Complex Existence Conditions

Correlated Subqueries in sortBy() Clauses

Correlated Subqueries in groupBy() Clauses

Complex Aggregations with .value()

Forcing Subqueries with .nested()

Why Use .nested()?