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 寫對了」？