Set Operations

ExoQuery supports SQL set operations to combine results from multiple queries. Both queries must return the same type.

Union

Use union() to combine results from two queries, removing duplicates:

val adults = sql { Table<Person>().filter { p -> p.age >= 18 } }
val namedBob = sql { Table<Person>().filter { p -> p.name == "Bob" } }

val q = sql { adults.union(namedBob) }
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.age >= 18
//> UNION
//> SELECT p1.id, p1.name, p1.age FROM Person p1 WHERE p1.name = 'Bob'

If a row matches both conditions (e.g., Bob is 25), it appears only once in the result.

Union All

Use unionAll() to combine results without removing duplicates:

val adults = sql { Table<Person>().filter { p -> p.age >= 18 } }
val seniors = sql { Table<Person>().filter { p -> p.age >= 65 } }

val q = sql { adults.unionAll(seniors) }
//> SELECT p.id, p.name, p.age FROM Person p WHERE p.age >= 18
//> UNION ALL
//> SELECT p1.id, p1.name, p1.age FROM Person p1 WHERE p1.age >= 65

A 70-year-old matches both queries and appears twice in the result. Use unionAll() when you know there are no duplicates or when duplicates are intentional, it's faster than union() since the database skips deduplication.

Chaining Multiple Unions

Chain multiple union() or unionAll() calls:

val q1 = sql { Table<Person>().filter { p -> p.name == "Alice" } }
val q2 = sql { Table<Person>().filter { p -> p.name == "Bob" } }
val q3 = sql { Table<Person>().filter { p -> p.name == "Charlie" } }

val q = sql { q1.union(q2).union(q3) }
//> SELECT ... WHERE p.name = 'Alice'
//> UNION
//> SELECT ... WHERE p.name = 'Bob'
//> UNION
//> SELECT ... WHERE p.name = 'Charlie'

Combining with Other Operations

You can chain sortedBy(), limit(), offset(), and other operations after a union. ExoQuery automatically wraps the union in a subquery:

val q1 = sql { Table<Person>().filter { p -> p.age > 30 } }
val q2 = sql { Table<Person>().filter { p -> p.name == "Bob" } }

val q = sql { q1.union(q2).sortedBy { p -> p.name }.limit(10) }
//> SELECT p.id, p.name, p.age FROM (
//>   SELECT ... WHERE p.age > 30
//>   UNION
//>   SELECT ... WHERE p.name = 'Bob'
//> ) AS p ORDER BY p.name ASC LIMIT 10

Using with sql.select

Queries built with sql.select work with union operations:

val bostonPeople = sql.select {
  val p = from(Table<Person>())
  val a = join(Table<Address>()) { a -> a.ownerId == p.id }
  where { a.city == "Boston" }
  p
}

val seniors = sql { Table<Person>().filter { p -> p.age >= 65 } }

val q = sql { bostonPeople.union(seniors) }
//> SELECT p.id, p.name, p.age FROM Person p 
//>   INNER JOIN Address a ON a.owner_id = p.id WHERE a.city = 'Boston'
//> UNION
//> SELECT p1.id, p1.name, p1.age FROM Person p1 WHERE p1.age >= 65

Type Requirements

Both queries in a union must return the same type. The following will not compile:

val people = sql { Table<Person>() }
val addresses = sql { Table<Address>() }

// WRONG - different types
val q = sql { people.union(addresses) }  // Compile error

If you need to combine different tables, project them to a common type first:

@Serializable
data class NameOnly(val name: String)

val personNames = sql { Table<Person>().map { p -> NameOnly(p.name) } }
val robotNames = sql { Table<Robot>().map { r -> NameOnly(r.name) } }

val q = sql { personNames.union(robotNames) }
//> SELECT p.name FROM Person p
//> UNION
//> SELECT r.name FROM Robot r

Not Supported

ExoQuery does not currently support INTERSECT or EXCEPT set operations.