---
title: Joins & Aliases
description: Join tables, use left joins, and self-join safely with aliases.
---

# Joins & Aliases

## Inner join

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

```ts
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.

```ts
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.

```ts
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.

<!--
Sitemap

URL: https://tsql-docs.pages.dev/v0.5.0/guides/quickstart.md
Title: Quickstart
Description: Define migrations, run them safely, and write typed SQL in application code.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/connecting.md
Title: Connecting
Description: Create a database instance from schema, dialect, and runtime connection.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/dialect-and-compilation.md
Title: Dialect & SQL Compilation
Description: Configure SQL dialect behavior and compile schema/query SQL.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/grouping-and-aggregates.md
Title: Grouping & Aggregates
Description: Use groupBy, having, and aggregate helpers for analytical queries.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/joins-and-aliases.md
Title: Joins & Aliases
Description: Join tables, use left joins, and self-join safely with aliases.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/migration-runtime.md
Title: Migration Runtime
Description: Understand db.migrate behavior, tracking, and failure semantics.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/mutations.md
Title: Mutations
Description: Insert, update, and delete data with compile or execute.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/predicates-and-expressions.md
Title: Predicates & Expressions
Description: Use Expr methods, logical operators, null checks, and expression composition.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/raw-sql.md
Title: Raw SQL
Description: Use raw templates and expression templates when fluent APIs are not enough.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/schema-and-migrations.md
Title: Schema & Migrations
Description: Define tables, columns, and indexes with ordered migration history.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/select-basics.md
Title: Select Basics
Description: Build typed select queries with from, where, order, limit, and select.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/transactions.md
Title: Transactions
Description: Use explicit begin, commit, rollback, and await using for safety.

URL: https://tsql-docs.pages.dev/v0.5.0/reference/type-utilities.md
Title: Type Utilities
Description: Derive row, insert, and update types directly from schema.
-->