Join
Learn how to perform SQL joins in tSQL including inner joins, left joins, and complex join conditions with type-safe query building.
Join clauses combine rows from two or more tables based on a related column. Kysely supports inner, left, right, and full joins.
Basic Inner Join
db.selectFrom("users")
.innerJoin("orders", "orders.user_id", "users.id")
.select(["users.id", "orders.total"])
.execute();Left Join
db.selectFrom("users")
.leftJoin("orders", "orders.user_id", "users.id")
.select(["users.name", (eb) => eb.fn.count("orders.id").as("order_count")])
.groupBy("users.id")
.execute();Multiple Conditions
db.selectFrom("users")
.innerJoin("orders", (join) =>
join
.onRef("orders.user_id", "=", "users.id")
.on("orders.status", "=", "completed")
)
.select(["users.id", "orders.total"])
.execute();Subquery Join
db.selectFrom("users")
.innerJoin(
(eb) =>
eb
.selectFrom("orders")
.select(["user_id", "total"])
.where("status", "=", "completed")
.as("recent_orders"),
"recent_orders.user_id",
"users.id"
)
.select(["users.name", "recent_orders.total"])
.execute();Table Aliases
db.selectFrom("users as u")
.innerJoin("orders as o", "o.user_id", "u.id")
.select(["u.name", "o.total"])
.execute();