Skip to content
Saasfly Docs

Kysely Join

Examples of queries that use joins.

Simple inner join

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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()

Result

SELECT
  "person"."id",
  "pet"."name" AS "pet_name"
FROM
  "person"
  INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"

Aliased inner join

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

await db.selectFrom('person')
  .innerJoin('pet as p', 'p.owner_id', 'person.id')
  .where('p.name', '=', 'Doggo')
  .selectAll()
  .execute()

Result

SELECT
  *
FROM
  "person"
  INNER JOIN "pet" AS "p" ON "p"."owner_id" = "person"."id"
WHERE
  "p"."name" = $1


-- Parameters
-- [1] Doggo

Complex join

You can provide a function as the second argument to get a join builder for creating more complex joins. The join builder has a bunch of on* methods for building the on clause of the join. There’s basically an equivalent for every where method (on, onRef etc.).

You can do all the same things with the on method that you can with the corresponding where method (like OR expressions for example). See the where method documentation for more examples.

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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()

Result

SELECT
  *
FROM
  "person"
  INNER JOIN "pet" ON "pet"."owner_id" = "person"."id"
  AND "pet"."name" = $1
  AND (
    "person"."age" > $2
    OR "person"."age" < $3
  )


-- Parameters
-- [1] Doggo
-- [2] 18
-- [3] 100

Subquery join

Schema

import { Generated } from 'kysely'


declare global {
  interface DB {
    person: PersonTable
    pet: PetTable
  }


  interface PersonTable {
    id: Generated<string>
    first_name: string
    last_name: string | null
    created_at: Generated<Date>
    age: number
  }


  interface PetTable {
    id: Generated<string>
    name: string
    owner_id: string
    species: 'cat' | 'dog'
    is_favorite: boolean
  }
}

Querying

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()

Result

SELECT
  "doggos".*
FROM
  "person"
  INNER JOIN (
    SELECT
      "owner_id" AS "owner",
      "name"
    FROM
      "pet"
    WHERE
      "name" = $1
  ) AS "doggos" ON "doggos"."owner" = "person"."id"


-- Parameters
-- [1] Doggo