Joining tables
Last modified: 03 March 2025Join
For the join examples below, consider the following tables:
package org.example.tables
import org.jetbrains.exposed.dao.id.IntIdTable
object StarWarsFilmsIntIdTable : IntIdTable("star_wars_films_table") {
val sequelId = integer("sequel_id").uniqueIndex()
val name = varchar("name", MAX_VARCHAR_LENGTH)
val director = varchar("director", MAX_VARCHAR_LENGTH)
}package org.example.tables
import org.jetbrains.exposed.dao.id.IntIdTable
const val MAX_NAME_LENGTH = 50
object ActorsIntIdTable : IntIdTable("actors") {
val sequelId = integer("sequel_id").uniqueIndex()
val name = varchar("name", MAX_NAME_LENGTH)
}package org.example.tables
import org.jetbrains.exposed.dao.id.IntIdTable
const val MAX_CHARACTER_NAME_LENGTH = 50
object RolesTable : IntIdTable() {
val sequelId = integer("sequel_id")
val actorId = reference("actor_id", ActorsIntIdTable)
val characterName = varchar("name", MAX_CHARACTER_NAME_LENGTH)
}In the following example, the .join() function is used to count how many actors star in each movie:
ActorsIntIdTable.join(
StarWarsFilmsIntIdTable,
JoinType.INNER,
onColumn = ActorsIntIdTable.sequelId,
otherColumn = StarWarsFilmsIntIdTable.sequelId
)
.select(ActorsIntIdTable.name.count(), StarWarsFilmsIntIdTable.name)
.groupBy(StarWarsFilmsIntIdTable.name)Instead of specifying onColumn and otherColumn, additionalConstraint can be used, which allows specifying additional types of join conditions.
ActorsIntIdTable.join(
StarWarsFilmsIntIdTable,
JoinType.INNER,
additionalConstraint = { StarWarsFilmsIntIdTable.sequelId eq ActorsIntIdTable.sequelId }
)
.select(ActorsIntIdTable.name.count(), StarWarsFilmsIntIdTable.name)
.groupBy(StarWarsFilmsIntIdTable.name)Join on a foreign key
When joining on a foreign key, the more concise innerJoin can be used:
(ActorsIntIdTable innerJoin RolesTable)
.select(RolesTable.characterName.count(), ActorsIntIdTable.name)
.groupBy(ActorsIntIdTable.name)
.toList()This is equivalent to using a .join() with a JoinType.INNER:
ActorsIntIdTable.join(RolesTable, JoinType.INNER, onColumn = ActorsIntIdTable.id, otherColumn = RolesTable.actorId)
.select(RolesTable.characterName.count(), ActorsIntIdTable.name)
.groupBy(ActorsIntIdTable.name)
.toList()Union
To combine the results of multiple queries, use the .union() function. Per the SQL specification, the queries must have the same number of columns, and not be marked for update. Subqueries may be combined when supported by the database.
val lucasDirectedQuery =
StarWarsFilmsIntIdTable.select(StarWarsFilmsIntIdTable.name).where { StarWarsFilmsIntIdTable.director eq "George Lucas" }
val abramsDirectedQuery =
StarWarsFilmsIntIdTable.select(StarWarsFilmsIntIdTable.name).where { StarWarsFilmsIntIdTable.director eq "J.J. Abrams" }
val filmNames = lucasDirectedQuery.union(abramsDirectedQuery).map { it[StarWarsFilmsIntIdTable.name] }Include duplicates
By default, .union() returns only unique rows. To include duplicates in the results, use the .unionAll() function instead.
val lucasDirectedQuery =
StarWarsFilmsIntIdTable.select(StarWarsFilmsIntIdTable.name).where { StarWarsFilmsIntIdTable.director eq "George Lucas" }
val originalTrilogyQuery =
StarWarsFilmsIntIdTable.select(StarWarsFilmsIntIdTable.name).where { StarWarsFilmsIntIdTable.sequelId inList (3..5) }
val allFilmNames = lucasDirectedQuery.unionAll(originalTrilogyQuery).map { it[StarWarsFilmsIntIdTable.name] }