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:

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

Last modified: 03 July 2025