1.2 SQL 查询语义、连接、聚合与执行计划

档案管理员交来一张“能运行但数字不对”的报表，要求你们从连接、空值和聚合语义里找出偏差。

SQL 是声明式语言：查询描述希望得到的 relation，optimizer 决定可行的物理执行方式。但“声明式”不等于“不用理解语义”。JOIN multiplicity、NULL 和 grouping 仍会让语法正确的查询返回错误答案。

本课沿用 1.1 的 vault-lab.db。

基础投影、筛选与排序

sql

SELECT item_id, name, value_cents
FROM vault_items
WHERE value_cents >= 50000
ORDER BY value_cents DESC, item_id ASC
LIMIT 3;

SELECT list 决定输出 expression；
WHERE 过滤输入行；
ORDER BY 是获得稳定顺序的唯一通用方式；没有 ORDER BY 时，row order 不受保证；
LIMIT 应与确定性的 ORDER BY 配合，尤其是 pagination。

SQL 表面书写顺序与逻辑处理顺序不同，可先用这个简化模型：

text

FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

optimizer 可以在保持语义的前提下改写和重排物理执行，不能把这个逻辑模型理解成引擎必须逐行按此运行。

predicate 与三值逻辑

sql

SELECT item_id, name
FROM vault_items
WHERE discovered_on IS NULL;

对 NULL 使用 =、<>、< 等普通比较通常得到 UNKNOWN。一个常见陷阱是 NOT IN：如果右侧集合含 NULL，结果可能没有任何 TRUE 行。

sql

-- Prefer NOT EXISTS when expressing anti-join semantics.
SELECT v.item_id, v.name
FROM vault_items AS v
WHERE NOT EXISTS (
    SELECT 1
    FROM quest_items AS qi
    WHERE qi.item_id = v.item_id
);

上例需要后文创建的 quest_items。重点是让 correlation predicate 明确表达“没有匹配行”。

建立多表关系

sql

CREATE TABLE adventurers (
    adventurer_id INTEGER PRIMARY KEY,
    name          TEXT NOT NULL UNIQUE,
    level         INTEGER NOT NULL CHECK (level >= 1)
);

CREATE TABLE quests (
    quest_id      INTEGER PRIMARY KEY,
    adventurer_id INTEGER NOT NULL,
    status        TEXT NOT NULL CHECK (
        status IN ('active', 'completed', 'cancelled')
    ),
    reward_cents  INTEGER NOT NULL CHECK (reward_cents >= 0),
    FOREIGN KEY (adventurer_id)
        REFERENCES adventurers(adventurer_id)
        ON DELETE RESTRICT
);

CREATE TABLE quest_items (
    quest_id INTEGER NOT NULL,
    item_id  INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (quest_id, item_id),
    FOREIGN KEY (quest_id) REFERENCES quests(quest_id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES vault_items(item_id) ON DELETE RESTRICT
);

INSERT INTO adventurers (adventurer_id, name, level)
VALUES (1, '艾琳', 15), (2, '马尔科', 22), (3, '苏禾', 9);

INSERT INTO quests (quest_id, adventurer_id, status, reward_cents)
VALUES
    (1, 1, 'completed', 50000),
    (2, 2, 'active',    30000),
    (3, 1, 'active',    15000);

INSERT INTO quest_items (quest_id, item_id, quantity)
VALUES (1, 3, 1), (1, 5, 2), (2, 1, 1), (3, 5, 3);

quests 与 vault_items 是 many-to-many，因此用 associative table quest_items 表达，并把 (quest_id, item_id) 设为 composite primary key。

INNER JOIN 与 row multiplication

sql

SELECT
    q.quest_id,
    a.name AS adventurer_name,
    q.status,
    v.name AS item_name,
    qi.quantity
FROM quests AS q
JOIN adventurers AS a
  ON a.adventurer_id = q.adventurer_id
JOIN quest_items AS qi
  ON qi.quest_id = q.quest_id
JOIN vault_items AS v
  ON v.item_id = qi.item_id
ORDER BY q.quest_id, v.item_id;

一项 quest 有两种 item，就会产生两行。这不是“数据库重复了数据”，而是 join result 的基数。如果随后对 quest reward 求和，却忘记 reward 会被每个 item row 重复一次，就会得到错误总额。

先确认每个 join key 的 cardinality：one-to-one、one-to-many 还是 many-to-many，再决定 aggregation level。

LEFT JOIN 与条件放置

查所有 adventurer，包括没有 quest 的苏禾：

sql

SELECT
    a.adventurer_id,
    a.name,
    q.quest_id,
    q.status
FROM adventurers AS a
LEFT JOIN quests AS q
  ON q.adventurer_id = a.adventurer_id
ORDER BY a.adventurer_id, q.quest_id;

如果只想连接 active quest，同时仍保留没有 active quest 的人，predicate 应放在 ON：

sql

SELECT a.name, q.quest_id
FROM adventurers AS a
LEFT JOIN quests AS q
  ON q.adventurer_id = a.adventurer_id
 AND q.status = 'active';

若把 q.status = 'active' 放进 WHERE，NULL-extended rows 会被过滤，结果在这个条件下接近 INNER JOIN。这是 outer join 最常见的语义错误之一。

GROUP BY 与 aggregation

sql

SELECT
    t.code,
    COUNT(*) AS item_count,
    MIN(v.value_cents) AS min_value_cents,
    MAX(v.value_cents) AS max_value_cents,
    ROUND(AVG(v.value_cents), 2) AS avg_value_cents
FROM vault_items AS v
JOIN item_types AS t
  ON t.item_type_id = v.item_type_id
GROUP BY t.item_type_id, t.code
HAVING AVG(v.value_cents) >= 50000
ORDER BY avg_value_cents DESC;

COUNT(*) 统计 group 中的行；
COUNT(column) 只统计该 expression 非 NULL 的行；
大多数 aggregation 忽略 NULL；
空输入上的 COUNT(*) 是 0，而 SUM/AVG 等通常是 NULL；
SELECT 中未 aggregation 的 column 应与 grouping key 保持语义一致。

SQLite 对非标准 grouping 有较宽松行为，不能因为查询“跑得动”就认为结果可移植或确定。

Subquery 与 CTE

找出高于全库平均价值的 item：

sql

SELECT item_id, name, value_cents
FROM vault_items
WHERE value_cents > (
    SELECT AVG(value_cents)
    FROM vault_items
)
ORDER BY value_cents DESC;

CTE 可以命名中间 relation：

sql

WITH quest_totals AS (
    SELECT
        q.adventurer_id,
        SUM(q.reward_cents) AS total_reward_cents
    FROM quests AS q
    WHERE q.status = 'completed'
    GROUP BY q.adventurer_id
)
SELECT
    a.name,
    COALESCE(qt.total_reward_cents, 0) AS total_reward_cents
FROM adventurers AS a
LEFT JOIN quest_totals AS qt
  ON qt.adventurer_id = a.adventurer_id
ORDER BY total_reward_cents DESC, a.adventurer_id;

CTE 是语义组织工具，不保证一定 materialize，也不保证一定更快。具体优化行为取决于数据库和版本。

Index 服务于查询，不是自动加速器

sql

CREATE INDEX idx_vault_items_type_value
ON vault_items (item_type_id, value_cents DESC);

EXPLAIN QUERY PLAN
SELECT item_id, name, value_cents
FROM vault_items
WHERE item_type_id = 1
ORDER BY value_cents DESC;

composite index 的 column order 要与 predicate、排序和选择性共同设计。创建 index 后，optimizer 仍可能选择 table scan，例如表很小、predicate 命中大部分行、统计信息不足，或 scan 成本更低。

index 的成本包括写放大、存储、cache 占用和 maintenance。先定义重要查询和 SLO，再用真实数据分布与 execution plan 验证。

EXPLAIN QUERY PLAN 是 SQLite 的高层计划摘要，不是跨数据库通用格式。PostgreSQL 的 EXPLAIN (ANALYZE, BUFFERS) 会实际执行查询；对写操作或昂贵查询使用 ANALYZE 前必须明确副作用与成本。

Pagination 边界

sql

SELECT item_id, name, value_cents
FROM vault_items
ORDER BY value_cents DESC, item_id DESC
LIMIT 20 OFFSET 10000;

大 OFFSET 可能仍需扫描/跳过大量记录，而且并发写入会让跨页结果漂移。稳定的 timeline/API 常使用 keyset pagination：

sql

SELECT item_id, name, value_cents
FROM vault_items
WHERE
    value_cents < :last_value
    OR (value_cents = :last_value AND item_id < :last_id)
ORDER BY value_cents DESC, item_id DESC
LIMIT 20;

参数命名是示意；实际 placeholder syntax 由 driver 决定。排序必须包含唯一 tie-breaker。

练习

查询每位 adventurer 的 active quest 数，保留零任务的人。
查询没有被任何 quest 引用的 item，分别用 NOT EXISTS 与 LEFT anti-join 实现。
故意把 active predicate 从 ON 移到 WHERE，对比苏禾是否仍出现。
给 quests(adventurer_id, status) 建 composite index，记录计划变化；再删除大部分测试数据，观察 optimizer 是否仍作同样选择。
在 transaction 中将一个 item 的类型改为不存在的 ID，证明 foreign key constraint 生效。

验收标准

[ ] 查询需要稳定顺序时显式 ORDER BY；
[ ] 能解释 INNER/LEFT JOIN 的 result cardinality；
[ ] 能区分 WHERE 与 ON 对 outer join 的影响；
[ ] 能解释 COUNT(*) 与 COUNT(column)；
[ ] 不把 optimizer 选用 index 当作建索引后的保证；
[ ] 应用输入通过 parameters 绑定；
[ ] 写操作用明确 transaction boundary 和 changed-row check。

本章小结

SQL 的难点不在记关键词，而在表达正确 relation。constraint 负责拒绝非法状态，transaction 负责划定变更边界，query 则通过 predicate、join 与 aggregation 得到结果。下一章会用关系代数给这些查询建立更精确的形式模型。

第1章逻辑

第2章集合论

第3章证明方法

第4章递归、归纳与递推

第5章关系与函数

第6章组合数学

第7章图论

第8章概率与统计

第9章数论

第10章线性代数

第11章微积分与优化

第12章信息论与数值计算