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

一条 SQL 能运行，只说明语法和执行没有中止，不说明报表数字正确。连接基数、NULL 和聚合语义都可能让结果安静地偏离。

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

本课沿用 1.1 的 catalog-lab.db 和 SQLite 3.37.0+。重新打开 CLI 后，先在事务外执行 PRAGMA foreign_keys = ON；外键开关不会随数据库文件保存。SQL 块按前后依赖执行，带 :参数 的分页片段需要驱动绑定。

基础投影、筛选与排序

sql

SELECT item_id, name, value_cents
FROM catalog_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 catalog_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 catalog_items AS v
WHERE NOT EXISTS (
    SELECT 1
    FROM task_items AS qi
    WHERE qi.item_id = v.item_id
);

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

建立多表关系

sql

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name          TEXT NOT NULL UNIQUE,
    level         INTEGER NOT NULL CHECK (level >= 1)
) STRICT;

CREATE TABLE tasks (
    task_id      INTEGER PRIMARY KEY,
    customer_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 (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE RESTRICT
) STRICT;

CREATE TABLE task_items (
    task_id INTEGER NOT NULL,
    item_id  INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (task_id, item_id),
    FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES catalog_items(item_id) ON DELETE RESTRICT
) STRICT;

INSERT INTO customers (customer_id, name, level)
VALUES (1, '客户 A', 15), (2, '客户 B', 22), (3, '客户 C', 9);

INSERT INTO tasks (task_id, customer_id, status, reward_cents)
VALUES
    (1, 1, 'completed', 50000),
    (2, 2, 'active',    30000),
    (3, 1, 'active',    15000);

INSERT INTO task_items (task_id, item_id, quantity)
VALUES (1, 3, 1), (1, 5, 2), (2, 1, 1), (3, 5, 3);

tasks 与 catalog_items 是 many-to-many，因此用 associative table task_items 表达，并把 (task_id, item_id) 设为 composite primary key。

INNER JOIN 与 row multiplication

sql

SELECT
    q.task_id,
    a.name AS customer_name,
    q.status,
    v.name AS item_name,
    qi.quantity
FROM tasks AS q
JOIN customers AS a
  ON a.customer_id = q.customer_id
JOIN task_items AS qi
  ON qi.task_id = q.task_id
JOIN catalog_items AS v
  ON v.item_id = qi.item_id
ORDER BY q.task_id, v.item_id;

这组初始数据应返回 4 行：task 1 有两种 item，task 2、3 各一种。一项 task 有两种 item，就会产生两行。这不是“数据库重复了数据”，而是 join result 的基数。如果随后对 task reward 求和，却忘记 reward 会被每个 item row 重复一次，就会得到错误总额。

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

LEFT JOIN 与条件放置

查所有 customer，包括没有 task 的客户 C：

sql

SELECT
    a.customer_id,
    a.name,
    q.task_id,
    q.status
FROM customers AS a
LEFT JOIN tasks AS q
  ON q.customer_id = a.customer_id
ORDER BY a.customer_id, q.task_id;

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

sql

SELECT a.name, q.task_id
FROM customers AS a
LEFT JOIN tasks AS q
  ON q.customer_id = a.customer_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 catalog_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 对非标准分组有较宽松行为；未聚合的列若不由分组键决定，查询能执行也不代表取值确定或可移植。SQLite SELECT 文档说明了裸列的取值规则。

Subquery 与 CTE

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

sql

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

CTE 可以命名中间 relation：

sql

WITH task_totals AS (
    SELECT
        q.customer_id,
        SUM(q.reward_cents) AS total_reward_cents
    FROM tasks AS q
    WHERE q.status = 'completed'
    GROUP BY q.customer_id
)
SELECT
    a.name,
    COALESCE(qt.total_reward_cents, 0) AS total_reward_cents
FROM customers AS a
LEFT JOIN task_totals AS qt
  ON qt.customer_id = a.customer_id
ORDER BY total_reward_cents DESC, a.customer_id;

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

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

sql

CREATE INDEX idx_catalog_items_type_value
ON catalog_items (item_type_id, value_cents DESC);

EXPLAIN QUERY PLAN
SELECT item_id, name, value_cents
FROM catalog_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 catalog_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 catalog_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。Keyset 能减少跨过前面所有行的工作，但不冻结数据：若两次请求之间 value_cents 改变，记录仍可能跨过游标而重复或漏读。需要固定全集的导出，应另外定义快照或不可变排序键。

练习

查询每位 customer 的 active task 数，保留零任务的人。
查询没有被任何 task 引用的 item，分别用 NOT EXISTS 与 LEFT anti-join 实现。
故意把 active predicate 从 ON 移到 WHERE，对比零任务测试客户是否仍出现。
给 tasks(customer_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章信息论与数值计算