SQL 核心概念

ORM 给 query 加上类型。Postgres 求值的仍然是一个 relation：rows 进去，rows 出来，逻辑顺序固定。Schema、RLS 与 migrations 是接线 —— 用 Hono、Drizzle、Zod OpenAPI 与 SST 打造 Backend APIs 和用 Hono、Better Auth、Drizzle 与 Postgres RLS 打造 Multi-Tenant 后端。这篇 note 讲的是 statement。

例子共用一家 shop。Constraints 写在 CREATE TABLE 里。Indexes 等到讲 cost。

sql

CREATE TABLE customers (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email      text NOT NULL UNIQUE,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL REFERENCES customers (id),
  status      text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id   uuid NOT NULL REFERENCES orders (id),
  sku        text NOT NULL,
  qty        integer NOT NULL CHECK (qty > 0),
  unit_cents integer NOT NULL CHECK (unit_cents >= 0),
  UNIQUE (order_id, sku)
);

Pattern 地图

Pattern	Typical input	Reach for it when
Exists	Outer row，related table	需要「至少有一条」/「一条都没有」，又不想把 outer row 复制出来
Keyset pagination	Ordered list	从已知的 `(created_at, id)` 取下一页，而不是 `OFFSET`
Upsert	可能碰撞的 insert	Unique key 已经存在 —— 用 `ON CONFLICT`，不要 check-then-insert
Top-N-per-group	按 key 分区的 rows	每个 customer 最新的 order、每个 order 的 top SKUs —— `ROW_NUMBER()`

1. Relation 进去，relation 出来

SELECT 是一个吃进 relations、吐出 relation 的表达式 —— 不是一段去拜访 table 的 procedure。

Table 是一个有名字的 relation：一袋 rows，每行是一组 columns 的 tuple。结果的形状由 statement 决定。
Key 是对「哪些 rows 可以存在」的 constraint，不是给 planner 的 hint。PRIMARY KEY 标识这一行。customers.email 上的 UNIQUE 禁止 duplicates。REFERENCES 禁止点名缺失 customer 的 order。
NULL 不是一个值。 它是值的缺席。与 NULL 比较得到 unknown，而 WHERE 只留下 true。
Failure: WHERE email = NULL 永远不为 true；predicate 是 IS NULL。Subquery 可能返回 NULL 时，NOT IN (SELECT …) 会变成陷阱。NOT EXISTS 才是仍然说它所声称之事的 anti-join。

2. 逻辑求值顺序

Postgres 并不按书写从左到右跑 statement。它跑一条 logical pipeline。Physical execution —— hash vs nested loop、index vs seq scan —— 是稍后的选择，必须保住这个含义。

text

FROM / JOIN → WHERE → GROUP BY → HAVING → SELECT → WINDOW → ORDER BY → LIMIT

FROM / JOIN 造出工作中的 row set。WHERE 过滤 rows。GROUP BY 折叠 groups；HAVING 过滤 groups。SELECT 给 columns 命名。Windows 计算但不继续折叠。ORDER BY 排序。LIMIT 切断。
WITH CTE 是插进 FROM 的 named subquery。它不是另一套 engine。
SELECT alias 对 WHERE 不可见。Window function 不能出现在 WHERE 或 HAVING —— 那些 clauses 已经跑过了。
Failure: 对 window alias 写 WHERE order_count > 0 或 WHERE n = 1。用 subquery 或 CTE 过滤 window —— 再来一次 FROM。

3. JOIN

Join 是 FROM 造出更宽 row 的方式。Inner 留下 matches。Left 留下每一行 left row，没有 match 时把右侧填成 NULL。Anti-join 留下没有 match 的 left rows。

sql

SELECT c.email, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

SELECT c.email
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);

Left join 会按每个 order 把 customer 复制一次。Join 之后不做 grouping 就 aggregate，会把 customer 算成他们有多少 orders 那么多次。
EXISTS / NOT EXISTS 在第一次 match 就停，并让 outer row 保持完整。
Failure: 把 LEFT JOIN … WHERE right.id IS NULL 当成第一反应的 anti-join。它会复制，而 NULL 比较很容易写错。JOIN 再加 DISTINCT 是在撤销你自己引入的 cardinality explosion。

4. Aggregation

GROUP BY 就是折叠。WHERE 之后，剩下的 rows 按 grouping keys 分区。每个 group 变成一行 output。

sql

SELECT
  c.id,
  c.email,
  count(o.id) AS order_count,
  coalesce(sum(oi.qty * oi.unit_cents), 0) AS revenue_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.email
HAVING count(o.id) >= 1;

未分组的 columns 不能出现在 SELECT，除非包进 aggregate —— Postgres 会拒绝 statement，而不是随便挑一个值。
HAVING 是 groups 的 WHERE。WHERE 看不见 count(*)。HAVING 可以。
count(o.id) 忽略 left join 带来的 NULLs，所以没有 orders 的 customers 是 0，并从 HAVING 掉出去。count(*) 会把那行空的 joined row 算成一。
Failure: 把 count(col) 与 count(*) 当成同义词。你选的 aggregate 是在陈述哪些 rows 存在。

5. Windows

Window function 跨相关 rows 计算，但不折叠它们。PARTITION BY 是 group。OVER 里的 ORDER BY 是那个 group 内部的顺序。结果是每个 input row 一个值。

sql

SELECT
  o.customer_id,
  o.id AS order_id,
  row_number() OVER (
    PARTITION BY o.customer_id
    ORDER BY o.created_at DESC, o.id DESC
  ) AS recency
FROM orders o;

Aggregation 回答「这个 customer 的 sum 是多少？」Windows 回答「这一行在该 customer 的 orders 里排第几？」并且仍然返回每一笔 order。
row_number() 是最核心的 window。在 outer query 里过滤 recency = 1，得到每个 customer 最新的 order。Running sum() OVER (PARTITION BY … ORDER BY …) 是另一半：随每一行长大的 frame。
先把 line items aggregate 完 再上 window，免得把 row set 炸开。Window 都不该出现在 WHERE。

6. Patterns

同一家 shop 上的四种 production 形状：exists、keyset pagination、upsert、top-N-per-group。

Exists。 「至少有一笔 shipped order」是 semi-join。EXISTS 在第一次 match 就停，并让 outer row 保持完整。

sql

SELECT c.email
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
    AND o.status = 'shipped'
);

Keyset pagination。 OFFSET n 仍会走过 n 行。Keyset seek 从你已经有的最后一行之后开始。Sort key 必须 unique —— (created_at, id) —— 否则共享同一个 timestamp 的 rows 会跳过或重复。

sql

SELECT id, customer_id, created_at
FROM orders
WHERE (created_at, id) < (:cursor_created_at, :cursor_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Upsert。 Check-then-insert 会 race。ON CONFLICT 是对着 schema 已经声明的 unique key 的一条 statement。

sql

INSERT INTO customers (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT (email) DO UPDATE
SET name = excluded.name
RETURNING id;

Top-N-per-group。 GROUP BY 无法在不做第二遍的情况下保住「top」那一行的非分组 columns。给 rows 编号，然后留下 n <= 2。

sql

SELECT customer_id, order_id, created_at
FROM (
  SELECT
    o.customer_id,
    o.id AS order_id,
    o.created_at,
    row_number() OVER (
      PARTITION BY o.customer_id
      ORDER BY o.created_at DESC, o.id DESC
    ) AS n
  FROM orders o
) ranked
WHERE n <= 2;

7. Cost

Index 是让 lookup 便宜、write 稍贵一点的数据结构。它不是 constraint。

sql

CREATE INDEX orders_customer_id_created_at_idx
  ON orders (customer_id, created_at DESC, id DESC);

EXPLAIN SELECT id, status
FROM orders
WHERE customer_id = '11111111-1111-1111-1111-111111111111';

-- Index Scan using orders_customer_id_created_at_idx
--   Index Cond: (customer_id = '11111111-…')

强制 customers.email 的 unique index 既是 constraint 也是 access path。orders.customer_id 上的 plain index 只是 access path。种类 map —— B-tree、hash、LSM、geospatial、inverted —— 见 System Design 里的 DB Indexing。
没有那条 path，WHERE customer_id = $1 就是 sequential scan：笔记本上瞬间完成，production 里随数据线性变慢。
同一个 composite index 也能覆盖 keyset seek：customer_id 上等值，再走 created_at DESC, id DESC。
Failure: 凭本地数据猜测，然后把 seq scan 送上线。EXPLAIN 检查你想要的 access path。EXPLAIN (ANALYZE) 对照真实 row counts。

8. Failure

两个 requests 读 qty，都加一，都 write。每个 transaction 看起来都正确。这一行跳过了一个数字。Isolation 没有神秘地失败 —— database 从未被要求把这次改动做成 atomic。

sql

-- lost update if two sessions do: read qty, add 1, write qty
UPDATE order_items
SET qty = qty + 1
WHERE id = :id;

忘了 unique constraint 是 data bug，事后再仔细写 WHERE 也修不回来。
Check-then-act 是 lost update。用 UPDATE … SET qty = qty + 1，或用拒绝 duplicate insert 的 UNIQUE。Check-then-insert 会 race；ON CONFLICT 是一条 statement。
Schema 才是 API。Application filters 是习惯。
Isolation、RLS 与 tenant context：用 Hono、Better Auth、Drizzle 与 Postgres RLS 打造 Multi-Tenant 后端。Typed schema、pool 与 migrations：用 Hono、Drizzle、Zod OpenAPI 与 SST 打造 Backend APIs。

Recap Q&A

阅读下一篇笔记

用 Hono、Better Auth、Drizzle 与 Postgres RLS 打造 Multi-Tenant 后端

SQL 核心概念

Pattern 地图

1. Relation 进去，relation 出来

2. 逻辑求值顺序

3. JOIN

4. Aggregation

5. Windows

6. Patterns

7. Cost

8. Failure

Recap Q&A

1. Postgres 按什么逻辑顺序求值一条 SELECT？

2. INNER、LEFT 与 anti-join 差在哪，为什么 LEFT JOIN 之后在右表上写 WHERE 是陷阱？

3. 什么时候 GROUP BY，什么时候该用 window？

4. Index 是做什么的，怎么知道 planner 在用它？

5. 哪些 SQL failure modes 看起来像「query 写对了」？