import { kysely } from "https://dotland.deno.dev/x/kysely_postgrs_js_dialect@v0.27.4/deps.ts";
const { DeleteQueryBuilder } = kysely;
Joins another table to the query using an inner join.
Examples
Simple usage 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')
.execute()
result[0].id
result[0].name
The generated SQL (PostgreSQL):
select "person"."id", "pet"."name"
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
You can give an alias for the joined table like this:
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
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
, onExists
etc.). You can do all the same things
with the on
method that you can with the corresponding where
method. See the where
method documentation for more examples.
await db.selectFrom('person')
.innerJoin(
'pet',
(join) => join
.onRef('pet.owner_id', '=', 'person.id')
.on('pet.name', '=', 'Doggo')
)
.selectAll()
.execute()
The generated SQL (PostgreSQL):
select *
from "person"
inner join "pet"
on "pet"."owner_id" = "person"."id"
and "pet"."name" = $1
You can join a subquery by providing a select query (or a callback) as the first argument:
await db.selectFrom('person')
.innerJoin(
qb.selectFrom('pet')
.select(['owner_id', 'name'])
.where('name', '=', 'Doggo')
.as('doggos'),
'doggos.owner_id',
'person.id',
)
.selectAll()
.execute()
The generated SQL (PostgreSQL):
select *
from "person"
inner join (
select "owner_id", "name"
from "pet"
where "name" = $1
) as "doggos"
on "doggos"."owner_id" = "person"."id"