Joining tables

Join

For the join examples below, consider the following tables:

package org.example.tables

import org.jetbrains.exposed.v1.core.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.v1.core.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.v1.core.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:

class JoinExamples {
    fun joinAndCount() {
        ActorsIntIdTable.join(
            StarWarsFilmsIntIdTable,
            JoinType.INNER,
            onColumn = ActorsIntIdTable.sequelId,
            otherColumn = StarWarsFilmsIntIdTable.sequelId
        )

Instead of specifying onColumn and otherColumn, additionalConstraint can be used, which allows specifying additional types of join conditions.

    fun useAdditionalConstraint() {
        ActorsIntIdTable.join(
            StarWarsFilmsIntIdTable,
            JoinType.INNER,
            additionalConstraint = { StarWarsFilmsIntIdTable.sequelId eq ActorsIntIdTable.sequelId }
        )

Join on a foreign key

When joining on a foreign key, the more concise innerJoin can be used:

    fun innerJoin() {
        (ActorsIntIdTable innerJoin RolesTable)
            .select(RolesTable.characterName.count(), ActorsIntIdTable.name)

This is equivalent to using a .join() with a JoinType.INNER:

    fun join() {
        ActorsIntIdTable.join(RolesTable, JoinType.INNER, onColumn = ActorsIntIdTable.id, otherColumn = RolesTable.actorId)
            .select(RolesTable.characterName.count(), ActorsIntIdTable.name)

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] }

04 November 2026