Join
- Kotlin course
- Advanced
- Lesson 35/83
- 5 min
Learn table joins with join.
Join operation
A join combines related records from two tables. Exposed offers options like innerJoin and leftJoin.
Example: Users.innerJoin(Orders).select { Orders.userId eq Users.id }. This call combines each user with their orders. innerJoin brings matching records; leftJoin brings all records from the left table. The condition is written in the select { ... } block. To walk the combined result use .forEach { row -> ... }. A foreign key relationship between tables is enough for a join.
Reading data with joins
The result of a join query is a Query. You walk each row with .forEach and read fields with row[Users.name] and row[Orders.id].
Example: Users.innerJoin(Orders).select { Orders.userId eq Users.id }.forEach { row -> println("${row[Users.name]} - ${row[Orders.id]}") }. This prints each user and order pair. Non matching rows are skipped by innerJoin. To filter results use .where { ... }. Join is a powerful feature of relational databases; it gathers data spread across multiple tables into a single query.
Practice Join two tables
Define object Users : Table() and object Orders : Table(). In Users add val id = integer("id") and val name = varchar("name", 50); in Orders add val id = integer("id") and val userId = integer("user_id"). In main open Database.connect, in a transaction block call Users.innerJoin(Orders).select { Orders.userId eq Users.id }, then print println("Join ready").
Starter code
// Join
// write here
import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction
fun main() {
}Sample solution
object Users : Table() {
val id = integer("id")
val name = varchar("name", 50)
}
object Orders : Table() {
val id = integer("id")
val userId = integer("user_id")
}
fun main() {
Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")
transaction {
Users.innerJoin(Orders).select { Orders.userId eq Users.id }
println("Join ready")
}
}Expected output
Join ready
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Complete the innerJoin call
Users.____(Orders).select { Orders.userId eq Users.id }Users.innerJoin(Orders).select { Orders.userId eq Users.id }Explanation: innerJoin combines two tables.
2 What is the difference between leftJoin and innerJoin?
- leftJoin is faster
- innerJoin only looks at the left table
- leftJoin brings all rows from the left table Correct answer
- There is no difference
Explanation: leftJoin brings all rows from the left table.
3 Match join parts
| Term | Meaning |
|---|---|
innerJoin |
Matching rows |
leftJoin |
Left table |
select |
Condition |
.forEach |
Row iteration |