Skip to main content
Kysely supports all common SQL join types: inner join, left join, right join, full join, cross join, and lateral join. All join methods are available on select, update, and delete query builders.

​
Inner Join

​
Simple Inner Join

Simple inner joins can be done by providing a table name and two columns to join: 
const result = await db
  .selectFrom('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  // `select` needs to come after the call to `innerJoin` so
  // that you can select from the joined table.
  .select(['person.id', 'pet.name as pet_name'])
  .execute()
The generated SQL (PostgreSQL): 
select "person"."id", "pet"."name" as "pet_name"
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"

​
Aliased Inner Join

You can give an alias for the joined table: 
await db.selectFrom('person')
  .innerJoin('pet as p', 'p.owner_id', 'person.id')
  .where('p.name', '=', 'Doggo')
  .selectAll()
  .execute()
The generated SQL (PostgreSQL): 
select *
from "person"
inner join "pet" as "p"
on "p"."owner_id" = "person"."id"
where "p"."name" = $1

​
Complex Join Conditions

You can provide a function as the second argument to get a join builder for creating more complex joins: 
await db.selectFrom('person')
  .innerJoin(
    'pet',
    (join) => join
      .onRef('pet.owner_id', '=', 'person.id')
      .on('pet.name', '=', 'Doggo')
      .on((eb) => eb.or([
        eb('person.age', '>', 18),
        eb('person.age', '<', 100)
      ]))
  )
  .selectAll()
  .execute()
The generated SQL (PostgreSQL): 
select *
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
and "pet"."name" = $1
and (
  "person"."age" > $2
  OR "person"."age" < $3
)

​
Subquery Join

You can join a subquery by providing a callback: 
const result = await db.selectFrom('person')
  .innerJoin(
    (eb) => eb
      .selectFrom('pet')
      .select(['owner_id as owner', 'name'])
      .where('name', '=', 'Doggo')
      .as('doggos'),
    (join) => join
      .onRef('doggos.owner', '=', 'person.id'),
  )
  .selectAll('doggos')
  .execute()
The generated SQL (PostgreSQL): 
select "doggos".*
from "person"
inner join (
  select "owner_id" as "owner", "name"
  from "pet"
  where "name" = $1
) as "doggos"
on "doggos"."owner" = "person"."id"

​
Left Join

Left join works exactly like inner join but adds a LEFT JOIN instead: 
const result = await db
  .selectFrom('person')
  .leftJoin('pet', 'pet.owner_id', 'person.id')
  .selectAll()
  .execute()
The generated SQL (PostgreSQL): 
select *
from "person"
left join "pet"
on "pet"."owner_id" = "person"."id"
All the same patterns work: 
// With alias
await db.selectFrom('person')
  .leftJoin('pet as p', 'p.owner_id', 'person.id')
  .selectAll()
  .execute()

// With complex conditions
await db.selectFrom('person')
  .leftJoin('pet', (join) => join
    .onRef('pet.owner_id', '=', 'person.id')
    .on('pet.name', '=', 'Doggo')
  )
  .selectAll()
  .execute()

// With subquery
await db.selectFrom('person')
  .leftJoin(
    (eb) => eb.selectFrom('pet').select('owner_id').as('pet_owners'),
    (join) => join.onRef('pet_owners.owner_id', '=', 'person.id')
  )
  .selectAll()
  .execute()

​
Right Join

Right join works exactly like inner join but adds a RIGHT JOIN instead: 
const result = await db
  .selectFrom('person')
  .rightJoin('pet', 'pet.owner_id', 'person.id')
  .selectAll()
  .execute()
The generated SQL (PostgreSQL): 
select *
from "person"
right join "pet"
on "pet"."owner_id" = "person"."id"

​
Full Join

Full join is supported by PostgreSQL, MS SQL Server, and SQLite: 
const result = await db
  .selectFrom('person')
  .fullJoin('pet', 'pet.owner_id', 'person.id')
  .selectAll()
  .execute()
The generated SQL (PostgreSQL): 
select *
from "person"
full join "pet"
on "pet"."owner_id" = "person"."id"

​
Cross Join

Cross join produces a Cartesian product of rows: 
const result = await db
  .selectFrom('person')
  .crossJoin('pet')
  .selectAll()
  .execute()
The generated SQL (PostgreSQL): 
select *
from "person"
cross join "pet"

​
Lateral Join

Lateral joins allow subqueries to reference columns from preceding tables: 
const result = await db
  .selectFrom('person')
  .innerJoinLateral(
    (eb) => eb
      .selectFrom('pet')
      .select('pet.name')
      .whereRef('pet.owner_id', '=', 'person.id')
      .orderBy('pet.name')
      .limit(1)
      .as('first_pet'),
    (join) => join.onTrue()
  )
  .select(['person.id', 'first_pet.name'])
  .execute()
The generated SQL (PostgreSQL): 
select "person"."id", "first_pet"."name"
from "person"
inner join lateral (
  select "pet"."name"
  from "pet"
  where "pet"."owner_id" = "person"."id"
  order by "pet"."name"
  limit 1
) as "first_pet" on true
You can also use leftJoinLateral: 
const result = await db
  .selectFrom('person')
  .leftJoinLateral(
    (eb) => eb
      .selectFrom('pet')
      .select('pet.name')
      .whereRef('pet.owner_id', '=', 'person.id')
      .limit(1)
      .as('latest_pet'),
    (join) => join.onTrue()
  )
  .selectAll()
  .execute()

​
Join Builder Methods

The join builder (the callback function in complex joins) has the following methods:

​
on

Add a condition to the ON clause: 
.innerJoin('pet', (join) => join
  .on('pet.species', '=', 'dog')
)

​
onRef

Compare two columns in the ON clause: 
.innerJoin('pet', (join) => join
  .onRef('pet.owner_id', '=', 'person.id')
)

​
onTrue

Add on true (useful for lateral joins): 
.innerJoinLateral(subquery, (join) => join.onTrue())
You can combine multiple conditions: 
.innerJoin('pet', (join) => join
  .onRef('pet.owner_id', '=', 'person.id')
  .on('pet.species', '=', 'dog')
  .on((eb) => eb.or([
    eb('pet.age', '>', 5),
    eb('pet.age', '<', 1)
  ]))
)

​
Joins in Update Queries

​
PostgreSQL: From Clause

await db
  .updateTable('person')
  .from('pet')
  .set((eb) => ({
    first_name: eb.ref('pet.name')
  }))
  .whereRef('pet.owner_id', '=', 'person.id')
  .execute()

​
MySQL: Direct Joins

await db
  .updateTable(['person', 'pet'])
  .set('person.first_name', 'Updated')
  .whereRef('person.id', '=', 'pet.owner_id')
  .execute()

​
PostgreSQL: Using Join Methods

await db
  .updateTable('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .set('person.first_name', 'John')
  .where('pet.name', '=', 'Doggo')
  .execute()

​
Joins in Delete Queries

await db
  .deleteFrom('person')
  .using('person')
  .innerJoin('pet', 'pet.owner_id', 'person.id')
  .where('pet.name', '=', 'Doggo')
  .execute()

​
API Reference

​
Join Methods

  • innerJoin(table, column1, column2) - Simple inner join
  • innerJoin(table, callback) - Inner join with complex conditions
  • leftJoin(table, column1, column2) - Simple left join
  • leftJoin(table, callback) - Left join with complex conditions
  • rightJoin(table, column1, column2) - Simple right join
  • rightJoin(table, callback) - Right join with complex conditions
  • fullJoin(table, column1, column2) - Simple full join
  • fullJoin(table, callback) - Full join with complex conditions
  • crossJoin(table) - Cross join
  • innerJoinLateral(callback, joinCallback) - Lateral inner join
  • leftJoinLateral(callback, joinCallback) - Lateral left join

​
Join Builder Methods

  • on(column, operator, value) - Add ON condition
  • onRef(column1, operator, column2) - Compare two columns
  • onTrue() - Add ON true

Build docs developers (and LLMs) love

Get started for freeTalk to us