Joins & Aliases

Inner join

Use .join(source, on) for inner joins.

const rows = await db
	.from("posts")
	.join("users", ({ posts, users }) => posts.authorId.eq(users.id, db.dialect))
	.select(({ posts, users }) => ({
		title: posts.title,
		authorEmail: users.email,
	}))
	.execute();

Left join

Use .leftJoin(source, on) for optional related rows.

const rows = await db
	.from("users")
	.leftJoin("posts", ({ users, posts }) => users.id.eq(posts.authorId, db.dialect))
	.select(({ users, posts }) => ({
		email: users.email,
		postTitle: posts.title,
	}))
	.execute();

In left joins, joined columns are nullable in TypeScript.

Aliases and self joins

Use alias(table, as) when the same table appears multiple times.

import { alias } from "@bms/tsql";

const rows = await db
	.from(alias("users", "employees"))
	.leftJoin(alias("users", "managers"), ({ employees, managers }) =>
		employees.managerId.eq(managers.id, db.dialect),
	)
	.select(({ employees, managers }) => ({
		employeeEmail: employees.email,
		managerEmail: managers.email,
	}))
	.execute();

You can also pass { table: "users", as: "employees" } directly.