Chapter 06

JOIN 与子查询

查询构建器层面的 JOIN——最贴近 SQL 的写法，适合复杂查询；下一章的 Relational Queries 则是更高层的 ORM 式 API。

6.1 innerJoin：最常用

// 查文章并带作者名
import { eq } from "drizzle-orm";

const rows = await db
  .select({
    post: posts,
    authorName: users.name,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id));

// rows: { post: Post; authorName: string }[]

6.2 LEFT / RIGHT / FULL JOIN

// leftJoin：左表全保留，右表缺失则 null
const rows = await db
  .select({
    user: users,
    post: posts,   // 可能为 null
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id));

// rightJoin / fullJoin 同理，MySQL 不支持 full join

返回类型中 null 的处理

// Drizzle 知道 posts 在 leftJoin 下可能是 null
for (const r of rows) {
  if (r.post) {
    console.log(r.post.title);   // TS 知道这里安全
  }
}

6.3 自连接：alias

同一张表连自己（组织架构、树）——用 alias：

import { alias } from "drizzle-orm/pg-core";

const manager = alias(users, "manager");

const rows = await db
  .select({
    employee: users.name,
    managerName: manager.name,
  })
  .from(users)
  .leftJoin(manager, eq(users.managerId, manager.id));

6.4 多表 JOIN

const rows = await db
  .select({
    post: posts,
    author: users,
    category: categories,
  })
  .from(posts)
  .innerJoin(users, eq(posts.authorId, users.id))
  .innerJoin(categories, eq(posts.categoryId, categories.id))
  .where(gt(posts.viewCount, 100));

6.5 聚合 + JOIN：每作者的文章数

import { count, eq } from "drizzle-orm";

const stats = await db
  .select({
    userId: users.id,
    name: users.name,
    postCount: count(posts.id),
  })
  .from(users)
  .leftJoin(posts, eq(posts.authorId, users.id))
  .groupBy(users.id, users.name)
  .orderBy(desc(count(posts.id)));

6.6 子查询

IN 子查询

// 作者是作品数超过 10 的用户
const topAuthorsQ = db
  .select({ id: posts.authorId })
  .from(posts)
  .groupBy(posts.authorId)
  .having(gt(count(), 10));

const topAuthors = await db
  .select().from(users)
  .where(inArray(users.id, topAuthorsQ));

FROM 子查询（derived table）

import { sql } from "drizzle-orm";

const sq = db
  .select({
    authorId: posts.authorId,
    total: count().as("total"),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .as("post_stats");           // .as 命名为子查询别名

const rows = await db
  .select({
    name: users.name,
    total: sq.total,
  })
  .from(users)
  .innerJoin(sq, eq(sq.authorId, users.id));

6.7 with：CTE（公共表表达式）

const q = db.$with("top_posts").as(
  db.select().from(posts).where(gt(posts.viewCount, 1000))
);

const rows = await db
  .with(q)
  .select().from(q).orderBy(desc(q.viewCount));

CTE 让复杂查询按"步骤"写，可读性大增。也支持递归 CTE（db.$withRecursive）。

6.8 EXISTS 子查询

import { exists, sql } from "drizzle-orm";

// 所有"有过发帖"的用户
const authors = await db
  .select().from(users)
  .where(exists(
    db.select(sql`1`).from(posts).where(eq(posts.authorId, users.id))
  ));

6.9 $dynamic：可复用的查询片段

需要根据条件组装查询，但又要保留类型推断？用 $dynamic()：

function withPagination<T extends PgSelect>(qb: T, page: number, size: number) {
  return qb.limit(size).offset(page * size);
}

const qb = db.select().from(users).$dynamic();
const rows = await withPagination(qb, 2, 20);

6.10 UNION / INTERSECT / EXCEPT

import { union, intersect, except } from "drizzle-orm/pg-core";

const combined = await union(
  db.select({ id: users.id, kind: sql`'user'` }).from(users),
  db.select({ id: admins.id, kind: sql`'admin'` }).from(admins),
);

6.11 LATERAL JOIN（Postgres）

import { sql } from "drizzle-orm";

// 每用户最近 3 篇文章
const latestPosts = db
  .select().from(posts)
  .where(eq(posts.authorId, users.id))
  .orderBy(desc(posts.createdAt))
  .limit(3)
  .as("lp");

const rows = await db
  .select()
  .from(users)
  .innerJoinLateral(latestPosts, sql`true`);

6.12 小结