数据库基础体系 · 第 3/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

SQL 完整基础：查询逻辑顺序、连接、子查询、窗口函数与集合运算

SQL 的难点不在于记住若干关键字，而在于理解一个查询如何从关系中逐步产生结果：

从哪些行、哪些表开始；
如何连接和过滤；
何时分组、聚合；
子查询与外层查询如何传递值；
窗口函数为什么不会减少行数；
多个查询结果如何进行集合运算；
最终结果为什么还可能受事务隔离、NULL、重复行和执行计划影响。

本文以 PostgreSQL 当前稳定版本和 MySQL 8.4 官方公开语义为边界。除非特别说明，示例使用两者都支持的 SQL；需要区分的地方会明确指出。示例假设数据已经提交，并且查询在单个事务快照中执行。实际结果还会受到事务隔离级别、并发修改和部署拓扑影响。

一、先建立查询的基本模型

1. 表不是“有顺序的数组”

SQL 操作的基本对象是关系，但实际数据库通常允许重复行，因此更准确地说，普通查询处理的是 多重集合（bag），而不是自动去重的数学集合。

例如：

SELECT department_id
FROM employees;

如果有五名员工属于部门 10，结果中通常会出现五个 10。只有显式使用 DISTINCT，才会去重：

SELECT DISTINCT department_id
FROM employees;

这一区别会影响：

UNION 和 UNION ALL；
连接产生的重复行；
聚合前后的行数；
COUNT(*) 与 COUNT(DISTINCT ...)；
子查询中的 IN 和 EXISTS。

2. `NULL` 不是普通值

NULL 表示“未知”“不存在”或“没有适用值”，它不是空字符串，也不是数字零。

SQL 条件使用三值逻辑：

条件结果	含义
`TRUE`	条件成立
`FALSE`	条件不成立
`UNKNOWN`	无法确定

在 WHERE、JOIN ... ON、HAVING 中，只有 TRUE 的行会通过；FALSE 和 UNKNOWN 都不会通过。

例如：

SELECT *
FROM employees
WHERE manager_id = NULL;

这不会找到 manager_id 为空的员工，因为任何值与 NULL 比较，包括 NULL = NULL，结果都是 UNKNOWN。

正确写法是：

SELECT *
FROM employees
WHERE manager_id IS NULL;

可以用真值表理解常见情况：

表达式	结果
`TRUE AND UNKNOWN`	`UNKNOWN`
`FALSE AND UNKNOWN`	`FALSE`
`TRUE OR UNKNOWN`	`TRUE`
`FALSE OR UNKNOWN`	`UNKNOWN`
`NOT UNKNOWN`	`UNKNOWN`

因此，下面的条件也容易造成误解：

WHERE department_id <> 10

它不会包含 department_id IS NULL 的行，因为 NULL <> 10 是 UNKNOWN。如果业务要求“不是 10，或者没有部门”，必须明确写出：

WHERE department_id <> 10
   OR department_id IS NULL;

二、SQL 的逻辑查询顺序

SQL 的书写顺序与数据库理解查询的逻辑顺序不同。

常见的概念性逻辑顺序是：

FROM
JOIN ... ON
WHERE
GROUP BY
聚合计算
HAVING
窗口函数阶段
SELECT
DISTINCT
集合运算：UNION、INTERSECT、EXCEPT
ORDER BY
LIMIT / OFFSET 或 FETCH

不同数据库在规范文字和内部实现上可能有细节差异，但这个顺序足以解释绝大多数 SQL 语义问题。它是逻辑模型，不是执行计划中必须采用的物理步骤。

1. 示例数据

以下表结构可以在 PostgreSQL 和 MySQL 中直接使用：

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER NULL,
    salary DECIMAL(12, 2) NOT NULL,
    manager_id INTEGER NULL
);

INSERT INTO departments (department_id, department_name) VALUES
(10, 'Engineering'),
(20, 'Sales'),
(30, 'HR');

INSERT INTO employees
    (employee_id, employee_name, department_id, salary, manager_id)
VALUES
(1, 'Alice', 10, 12000.00, NULL),
(2, 'Bob',   10,  9000.00, 1),
(3, 'Carol', 20, 11000.00, NULL),
(4, 'Dave',  NULL, 7000.00, NULL);

现在看一个分组查询：

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS average_salary
FROM departments AS d
LEFT JOIN employees AS e
    ON e.department_id = d.department_id
WHERE d.department_id <> 30
GROUP BY d.department_name
HAVING COUNT(e.employee_id) >= 1
ORDER BY average_salary DESC;

逻辑上可以拆成以下步骤。

第一步：`FROM` 与 `LEFT JOIN`

以 departments 为保留侧，将员工按部门连接：

department_name	employee_id	salary
Engineering	1	12000
Engineering	2	9000
Sales	3	11000
HR	`NULL`	`NULL`

LEFT JOIN 保留左表中的所有部门，即使没有匹配员工。

第二步：`WHERE`

WHERE d.department_id <> 30

去掉 HR。剩余：

department_name	employee_id	salary
Engineering	1	12000
Engineering	2	9000
Sales	3	11000

第三步：`GROUP BY`

按 department_name 分成两组：

Engineering：两行；
Sales：一行。

第四步：聚合

Engineering：COUNT(e.employee_id) = 2，平均工资 10500；
Sales：COUNT(e.employee_id) = 1，平均工资 11000。

第五步：`HAVING`

两组都满足 COUNT(e.employee_id) >= 1。

第六步：`ORDER BY`

平均工资降序，结果为：

department_name	employee_count	average_salary
Sales	1	11000
Engineering	2	10500

2. 为什么 `SELECT` 别名不能普遍用于 `WHERE`

下面通常不成立：

SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 100000;

原因是逻辑上 WHERE 先于 SELECT，因此 annual_salary 尚未产生。应写成：

SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 100000;

或者使用派生表：

SELECT annual_salary
FROM (
    SELECT salary * 12 AS annual_salary
    FROM employees
) AS x
WHERE annual_salary > 100000;

在 ORDER BY 中，很多数据库允许使用 SELECT 别名，因为排序逻辑上发生在投影之后：

SELECT employee_name, salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;

但别名可见性在不同语句位置有边界，不能用“数据库会先执行哪一行 SQL”来简单解释。应该依据该子句的逻辑阶段和具体引擎规则判断。

3. 聚合函数与 `GROUP BY`

聚合函数把多行压缩成一个结果：

COUNT(*)：统计行数；
COUNT(column)：统计该列非 NULL 的行数；
SUM(column)：求和，忽略 NULL；
AVG(column)：对非 NULL 值求平均；
MIN、MAX：取非 NULL 值中的最小或最大值。

看这两个表达式：

SELECT
    COUNT(*) AS all_rows,
    COUNT(manager_id) AS rows_with_manager
FROM employees;

对于上面的数据，结果是：

all_rows	rows_with_manager
4	1

COUNT(*) 统计四行；只有 Bob 的 manager_id 非空，所以 COUNT(manager_id) 为一。

在有 GROUP BY 的查询中，非聚合列必须具有明确的分组语义：

SELECT department_id, employee_name, AVG(salary)
FROM employees
GROUP BY department_id;

这通常会报错，因为一个部门可能有多个 employee_name，数据库无法确定应该返回哪个名字。正确做法是：

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

或者改变问题，使名字也有明确聚合含义。

三、连接：从多个表构造行

1. 连接的形式化条件

连接的核心是对两个输入关系的行进行配对。

对于内连接：

R \bowtie_\theta S = \{(r,s)\mid r\in R,\ s\in S,\ \theta(r,s)=TRUE\}

其中：

R、S 是两个输入表或中间结果；
r、s 是各自的一行；
θ 是连接条件；
只有条件结果为 TRUE 的配对才会留下。

如果 R 有 3 行，S 有 4 行，理论上最多尝试 12 个配对；索引和优化器可能避免物理上枚举所有配对，但不会改变逻辑结果。

2. `INNER JOIN`

SELECT
    e.employee_name,
    d.department_name
FROM employees AS e
INNER JOIN departments AS d
    ON e.department_id = d.department_id;

只有两边都能匹配的员工才出现。Dave 的 department_id 是 NULL，所以不会出现在结果中。

JOIN 默认就是 INNER JOIN：

FROM employees AS e
JOIN departments AS d
    ON e.department_id = d.department_id

等价于上述写法。

3. 一对多连接会复制左表行

假设一个部门有两名员工：

departments:
10 Engineering

employees:
1 Alice 10
2 Bob   10

连接结果是两行：

Engineering | Alice
Engineering | Bob

这不是重复错误，而是一对多关系的自然结果。若随后统计部门数量：

SELECT COUNT(*)
FROM departments AS d
JOIN employees AS e
    ON e.department_id = d.department_id;

统计的是员工连接行数，而不是部门数。若要统计部门数量，应写：

SELECT COUNT(DISTINCT d.department_id)
FROM departments AS d
JOIN employees AS e
    ON e.department_id = d.department_id;

4. `LEFT JOIN`

LEFT JOIN 保留左表全部行：

SELECT
    d.department_name,
    e.employee_name
FROM departments AS d
LEFT JOIN employees AS e
    ON e.department_id = d.department_id;

HR 没有员工，但仍会出现：

department_name	employee_name
Engineering	Alice
Engineering	Bob
Sales	Carol
HR	`NULL`

右表列为 NULL 不表示右表真的有一条字段为空的记录，而是表示没有匹配行。

5. `ON` 与 `WHERE` 的关键区别

需求：列出所有部门及其工资超过 10000 的员工，没有符合条件的部门也要保留。

正确写法：

SELECT
    d.department_name,
    e.employee_name,
    e.salary
FROM departments AS d
LEFT JOIN employees AS e
    ON e.department_id = d.department_id
   AND e.salary > 10000;

结果类似：

department_name	employee_name	salary
Engineering	Alice	12000
Sales	Carol	11000
HR	`NULL`	`NULL`

如果写成：

SELECT
    d.department_name,
    e.employee_name,
    e.salary
FROM departments AS d
LEFT JOIN employees AS e
    ON e.department_id = d.department_id
WHERE e.salary > 10000;

逻辑过程变为：

先连接所有匹配部门的员工；
没有员工的 HR 由外连接补出 NULL；
WHERE e.salary > 10000 对 HR 行得到 UNKNOWN；
HR 被过滤掉。

这使得结果表现得像内连接。核心原则是：

ON 决定“哪些右表行可以匹配”；
WHERE 决定“连接完成后哪些结果行可以保留”。

如果业务要求保留左表，右表过滤条件通常应放在 ON 中，但仍需结合实际逻辑确认。

6. `RIGHT JOIN`、`FULL OUTER JOIN` 与自连接

RIGHT JOIN 是以右表为保留侧：

SELECT ...
FROM employees AS e
RIGHT JOIN departments AS d
    ON e.department_id = d.department_id;

多数情况下可交换表顺序改写成 LEFT JOIN，后者通常更易读。

FULL OUTER JOIN 同时保留两边无法匹配的行。PostgreSQL 支持：

SELECT ...
FROM employees AS e
FULL OUTER JOIN departments AS d
    ON e.department_id = d.department_id;

MySQL 8.4 没有原生 FULL OUTER JOIN 语法。可以用两个方向的外连接结合实现，但去重条件必须根据业务键设计，不能机械拼接：

SELECT
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM employees AS e
LEFT JOIN departments AS d
    ON e.department_id = d.department_id

UNION ALL

SELECT
    e.employee_id,
    e.employee_name,
    d.department_id,
    d.department_name
FROM departments AS d
LEFT JOIN employees AS e
    ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

第一部分包含所有员工；第二部分补充没有员工匹配的部门。WHERE e.employee_id IS NULL 排除了已经在第一部分出现的匹配部门。

自连接是表与自身连接，常用于层级关系：

SELECT
    e.employee_name AS employee,
    m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
    ON e.manager_id = m.employee_id;

这里必须使用别名，因为同一张表在一个查询中扮演两个角色。

7. 笛卡尔积与 `CROSS JOIN`

SELECT *
FROM departments
CROSS JOIN employees;

如果部门有 3 行、员工有 4 行，结果有 $3 \times 4 = 12$ 行。没有连接条件的逗号连接：

FROM departments, employees

在传统语法中也可能产生笛卡尔积。若本意是等值连接而遗漏条件，可能造成行数爆炸和严重性能问题。

四、子查询：把一个查询作为另一个查询的输入

子查询是出现在另一个 SQL 表达式中的查询。根据返回结果的形状，可分为：

标量子查询：最多返回一个值；
单列多行子查询：用于 IN、比较运算等；
表子查询：用于 FROM；
相关子查询：引用外层查询的列；
EXISTS 子查询：判断是否存在匹配行。

1. 标量子查询

查询高于全体员工平均工资的员工：

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

内层查询先产生一个标量：

\text{average\_salary} = \frac{12000+9000+11000+7000}{4} = 9750

外层再比较：

employee_name	salary
Alice	12000
Bob	9000
Carol	11000
Dave	7000

因此结果是 Alice 和 Carol。

标量子查询必须最多返回一行。下面的写法如果一个部门有多个员工，会报错：

SELECT
    d.department_name,
    (
        SELECT e.employee_name
        FROM employees AS e
        WHERE e.department_id = d.department_id
    ) AS employee_name
FROM departments AS d;

因为一个部门可能对应多个 employee_name。如果需求是显示任意一个名字，必须明确选择规则，例如使用聚合；如果需求是显示所有员工，应使用连接或数组聚合等适合当前数据库的方式。

2. `IN` 与 `NOT IN`

查询属于有员工部门的部门：

SELECT department_name
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
    WHERE department_id IS NOT NULL
);

逻辑上，内层产生一组部门编号，外层测试部门编号是否属于该组。

但 NOT IN 与 NULL 结合时非常危险：

SELECT department_name
FROM departments
WHERE department_id NOT IN (
    SELECT department_id
    FROM employees
);

如果子查询结果包含 NULL，对某个部门编号 x 的判断相当于：

x <> 10 AND x <> 20 AND x <> NULL

最后一项是 UNKNOWN，整个 AND 通常变成 UNKNOWN，导致结果可能为空或少于预期。

可以排除子查询中的 NULL：

SELECT department_name
FROM departments
WHERE department_id NOT IN (
    SELECT department_id
    FROM employees
    WHERE department_id IS NOT NULL
);

更稳妥的表达通常是 NOT EXISTS：

SELECT d.department_name
FROM departments AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
);

EXISTS 只关心是否存在至少一行，SELECT 1 的具体常量没有特殊含义。它不会因为匹配到多个员工而复制外层部门行。

3. 相关子查询

4. `ANY`、`ALL` 与集合比较

部分 SQL 方言支持：

SELECT employee_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department_id = 10
);

其含义是工资大于子查询返回的每一个工资。如果 Engineering 工资为 12000 和 9000，那么只有工资大于 12000 的员工符合。

> ALL 常等价于大于集合最大值，但空集语义和 NULL 语义需要特别处理。对生产查询，若使用 ALL、ANY，应确认目标数据库的具体规则和测试覆盖。

5. `FROM` 中的派生表

派生表是 FROM 中的子查询：

SELECT
    department_id,
    average_salary
FROM (
    SELECT
        department_id,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
) AS department_stats
WHERE average_salary >= 10000;

内层先把员工压缩成“每部门一行”，外层再过滤平均工资。派生表必须有别名；这是 SQL 语法和可读性上的重要边界。

6. CTE：命名中间结果

公共表表达式使用 WITH：

WITH department_stats AS (
    SELECT
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM department_stats
WHERE employee_count >= 2;

CTE 的核心作用是为子查询命名、分解复杂逻辑。它不应被简单理解为“必然物化到临时表”或“必然只执行一次”。不同数据库、版本和查询形态可能内联、物化或采用其他优化策略。

递归 CTE 可以处理树形结构，但递归终止条件、重复访问和循环数据必须由查询保证。它不属于普通子查询的简单替代。

五、窗口函数：在不减少行数的情况下进行分析

1. 窗口函数的定义

窗口函数在一组与当前行相关的行上计算结果，但通常不会像 GROUP BY 那样把多行压缩成一行。

基本形态：

函数(...) OVER (
    PARTITION BY ...
    ORDER BY ...
    frame_clause
)

三个部分含义不同：

PARTITION BY：把输入行分成互不影响的分区；
ORDER BY：规定分区内的逻辑顺序；
frame：规定当前行实际看到的窗口范围。

2. `GROUP BY` 与窗口函数的区别

查询每个部门的平均工资：

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

每个部门只返回一行。

查询每名员工，同时显示其部门平均工资：

SELECT
    employee_name,
    department_id,
    salary,
    AVG(salary) OVER (
        PARTITION BY department_id
    ) AS department_average
FROM employees;

结果仍然是一名员工一行：

employee_name	department_id	salary	department_average
Alice	10	12000	10500
Bob	10	9000	10500
Carol	20	11000	11000
Dave	`NULL`	7000	7000

GROUP BY 改变行粒度；窗口函数保留输入行粒度。

3. `ROW_NUMBER`、`RANK`、`DENSE_RANK`

对每个部门按工资降序排名：

SELECT
    employee_name,
    department_id,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC, employee_id
    ) AS row_number_in_department,
    RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS salary_rank,
    DENSE_RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS dense_salary_rank
FROM employees;

三个函数的差异：

假设工资为：

12000, 12000, 9000

则：

salary	`ROW_NUMBER`	`RANK`	`DENSE_RANK`
12000	1	1	1
12000	2	1	1
9000	3	3	2

ROW_NUMBER 为每行分配唯一序号；
RANK 相同值同名次，后续名次跳过；
DENSE_RANK 相同值同名次，但后续名次不跳过。

如果业务要求稳定、可复现的唯一排序，ROW_NUMBER 的窗口 ORDER BY 应加入唯一键，例如 employee_id。否则工资相同的行之间没有确定顺序。

4. 取每个分区的第一行

窗口函数不能直接写在 WHERE 中：

SELECT *
FROM employees
WHERE ROW_NUMBER() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
) = 1;

这是因为窗口计算发生在 WHERE 之后。应先计算，再在外层过滤：

SELECT
    employee_id,
    employee_name,
    department_id,
    salary
FROM (
    SELECT
        e.*,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC, employee_id
        ) AS rn
    FROM employees AS e
) AS ranked
WHERE rn = 1;

这得到每个部门工资最高的一名员工。若并列最高者都应保留，应使用 RANK()：

SELECT *
FROM (
    SELECT
        e.*,
        RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rnk
    FROM employees AS e
) AS ranked
WHERE rnk = 1;

5. 累计和与窗口 frame

查询按工资降序的累计工资：

SELECT
    employee_name,
    salary,
    SUM(salary) OVER (
        ORDER BY salary DESC, employee_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_salary
FROM employees;

其中：

UNBOUNDED PRECEDING：从分区第一行开始；
CURRENT ROW：当前行；
ROWS：按物理行数量定义 frame。

ROWS 与 RANGE 不完全相同。若排序键存在相同值，RANGE 通常会把排序键相同的“同组行”一起纳入 frame，而 ROWS 按单独行计算。对于累计值、排名和分页等对边界敏感的查询，应明确指定排序唯一性和 frame 类型，而不要依赖默认 frame。

6. `LAG`、`LEAD` 与相邻行比较

SELECT
    employee_id,
    employee_name,
    salary,
    LAG(salary) OVER (
        ORDER BY salary DESC, employee_id
    ) AS previous_salary
FROM employees;

LAG 读取窗口顺序中前一行的值，第一行没有前值，结果为 NULL。LEAD 则读取后一行。

这类函数适用于：

计算相邻月份增长；
比较当前事件与前一事件；
检测状态变化；
计算时间序列差值。

7. 窗口函数与聚合的组合

可以先分组，再在分组结果上排名：

SELECT
    department_id,
    employee_count,
    RANK() OVER (
        ORDER BY employee_count DESC
    ) AS department_rank
FROM (
    SELECT
        department_id,
        COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
) AS stats;

这里窗口函数看到的不是原始员工行，而是分组后的部门统计行。这正是“逻辑阶段”对表达式可用性的影响。

六、集合运算：合并多个查询结果

集合运算要求参与运算的查询具有兼容的结果结构。通常至少需要：

列数相同；
对应列的数据类型可以兼容；
列的语义位置一致。

列名通常由第一个查询决定。不要只因为数据类型可转换，就认为两个结果具有正确的业务含义。

1. `UNION ALL`

UNION ALL 直接拼接两个结果，并保留重复行：

SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10

UNION ALL

SELECT employee_id, employee_name
FROM employees
WHERE department_id = 20;

如果某行同时被两个分支选出，它会出现两次。

2. `UNION`

UNION 在拼接后去重：

SELECT employee_name
FROM employees
WHERE salary >= 9000

UNION

SELECT employee_name
FROM employees
WHERE department_id = 10;

同一个员工若满足两个条件，只保留一行。

去重需要额外的逻辑处理，通常可能涉及排序或哈希。不要用 UNION 代替 UNION ALL 来“碰巧消除重复”；首先应判断重复是否代表真实的一对多关系或查询错误。

3. `INTERSECT`

INTERSECT 返回两个结果都出现的行：

SELECT employee_id
FROM employees
WHERE salary >= 9000

INTERSECT

SELECT employee_id
FROM employees
WHERE department_id = 10;

结果是同时满足“工资至少 9000”和“属于部门 10”的员工编号。

PostgreSQL 支持 INTERSECT。MySQL 8.4 也支持集合运算语法，但实际使用时仍应针对目标版本测试复杂组合、类型转换和优先级。

4. `EXCEPT`

EXCEPT 返回出现在左侧、但不出现在右侧的行：

SELECT employee_id
FROM employees

EXCEPT

SELECT employee_id
FROM employees
WHERE department_id = 10;

结果是非部门 10 员工的编号。

PostgreSQL 支持 EXCEPT。在 MySQL 版本和部署边界明确时，应确认目标版本是否支持所需集合运算；若需要兼容更老的 MySQL 版本，可以使用 NOT EXISTS 改写：

SELECT e.employee_id
FROM employees AS e
WHERE NOT EXISTS (
    SELECT 1
    FROM employees AS x
    WHERE x.employee_id = e.employee_id
      AND x.department_id = 10
);

5. 集合运算的去重粒度

去重依据是参与集合运算的整行，而不是某个主键。

例如：

左侧：
(1, 'Alice')

右侧：
(1, 'Alice')

UNION 只保留一行。但如果是：

左侧：
(1, 'Alice')

右侧：
(1, 'Alice Smith')

两行并不相同，仍会保留两行。

因此，集合运算前必须确认“行的身份”是什么。若业务只按 employee_id 去重，不能直接依赖 UNION，因为它会比较所有输出列。

6. 集合运算与排序

整体排序应放在集合运算之后：

SELECT employee_id, employee_name
FROM employees
WHERE department_id = 10

UNION ALL

SELECT employee_id, employee_name
FROM employees
WHERE department_id = 20

ORDER BY employee_name;

如果需要分别限制每个分支，必须使用括号和派生表，否则 LIMIT 的作用范围可能不是预期的分支：

(
    SELECT employee_id, employee_name
    FROM employees
    WHERE department_id = 10
    ORDER BY salary DESC
    LIMIT 1
)
UNION ALL
(
    SELECT employee_id, employee_name
    FROM employees
    WHERE department_id = 20
    ORDER BY salary DESC
    LIMIT 1
);

不同数据库对集合运算中括号、分支级 ORDER BY 和 LIMIT 的具体语法存在差异，跨数据库部署时应执行目标引擎测试。

七、把连接、子查询和窗口函数组合起来

一个常见需求是：找出每个部门工资最高的员工，并显示部门平均工资。

可分为两个逻辑问题：

计算每个员工在部门内的排名；
保留排名第一的员工；
同时计算部门平均工资。

SELECT
    employee_id,
    employee_name,
    department_name,
    salary,
    department_average
FROM (
    SELECT
        e.employee_id,
        e.employee_name,
        d.department_name,
        e.salary,
        AVG(e.salary) OVER (
            PARTITION BY e.department_id
        ) AS department_average,
        RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS salary_rank
    FROM employees AS e
    JOIN departments AS d
        ON d.department_id = e.department_id
) AS x
WHERE salary_rank = 1;

每一步的语义是：

JOIN 将员工编号转换为部门名称；
AVG(...) OVER (...) 保留每名员工，同时附加部门平均值；
RANK() 处理并列第一；
外层查询过滤排名；
如果使用 ROW_NUMBER()，每部门只保留一人；使用 RANK()，并列最高者全部保留。

如果要保留没有员工的部门，就不能从 employees 出发做内连接，而应从 departments 出发使用外连接，并明确“没有员工时排名和平均值”的业务显示规则。

八、常见失败表现与诊断方法

1. 结果行数突然增加

常见原因：

一对多连接被误认为一对一；
连接条件缺失或不完整；
连接键不是唯一键；
多个一对多连接相乘。

诊断时先只查询键：

SELECT
    e.employee_id,
    COUNT(*) AS joined_rows
FROM employees AS e
JOIN departments AS d
    ON e.department_id = d.department_id
GROUP BY e.employee_id
HAVING COUNT(*) > 1;

如果本来期望每名员工最多一行，却出现多行，需要检查 departments.department_id 是否确实是唯一约束，以及连接条件是否遗漏了租户、日期或版本列。

2. `LEFT JOIN` 结果丢失左表数据

优先检查右表列条件是否写进了 WHERE：

-- 容易把外连接变成类似内连接的效果
LEFT JOIN t2 ON ...
WHERE t2.status = 'active'

根据需求改成：

LEFT JOIN t2
    ON ...
   AND t2.status = 'active'

但如果需求本来就是“只保留有符合条件右表记录的左表”，那么 WHERE 可能是正确的。问题不在语法，而在保留侧的业务语义是否明确。

3. `NOT IN` 返回空结果

检查子查询是否可能产生 NULL：

SELECT COUNT(*)
FROM employees
WHERE department_id IS NULL;

以及：

SELECT COUNT(*)
FROM employees
WHERE department_id NOT IN (...);

通常可改写为 NOT EXISTS，并用相关条件明确表达“不存在匹配行”。

4. 窗口函数过滤报错

窗口函数通常不能直接出现在 WHERE 中。应使用：

派生表；
CTE；
在 PostgreSQL 中使用 QUALIFY 不应直接假设可用，因为 PostgreSQL 当前公开 SQL 语义并不以通用 QUALIFY 为基础；
MySQL 也不能把窗口函数直接放在 WHERE 中。

通用改写：

WITH ranked AS (
    SELECT
        e.*,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC, employee_id
        ) AS rn
    FROM employees AS e
)
SELECT *
FROM ranked
WHERE rn <= 3;

5. `ORDER BY` 不稳定

表本身没有自然顺序。即使本次执行看起来按插入顺序返回，也不构成语义保证：

SELECT *
FROM employees
LIMIT 2;

结果可能随着索引、统计信息、并发、执行计划或数据页变化而改变。分页或取 Top-N 时，应使用确定性排序：

SELECT *
FROM employees
ORDER BY salary DESC, employee_id
LIMIT 2;

6. 逻辑正确但性能很差

逻辑 SQL 与物理执行是两层概念。数据库优化器可能选择：

Nested Loop；
Hash Join；
Merge Join；
索引扫描；
全表扫描；
子查询改写；
CTE 内联或物化；
聚合的排序或哈希实现。

可以使用目标数据库的执行计划工具检查实际路径：

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

-- MySQL
EXPLAIN ANALYZE
SELECT ...;

EXPLAIN ANALYZE 会实际执行语句，因此对 UPDATE、DELETE 或包含副作用的操作必须先确认事务、锁和回滚方案。只看估算计划可使用不执行的 EXPLAIN。

重点比较：

估算行数与实际行数；
连接前后的基数；
过滤条件是否尽早生效；
是否发生意外笛卡尔积；
是否扫描了大量无关数据；
排序、聚合或窗口阶段是否消耗大量内存和临时空间。

执行计划问题通常与关联表的主键、外键、唯一约束、数据分布和统计信息有关，而不是简单由“用了子查询”或“用了窗口函数”决定。

九、事务与部署边界

SQL 查询看到的是某个事务语义下的数据，而不是脱离并发环境的静态文件。

例如：

事务 A 查询部门员工数；
事务 B 插入一名员工并提交；
事务 A 再次查询。

第二次查询是否看到新员工，取决于数据库、事务隔离级别以及事务是否复用同一快照。PostgreSQL 和 MySQL 的默认隔离级别、快照行为和锁实现存在差异，不能仅凭 SQL 文本推断。

在读写分离部署中，还可能出现：

写入主库已提交；
随后读取路由到复制延迟的只读副本；
查询暂时看不到刚写入的数据。

这不是 JOIN 或子查询语义变化，而是部署拓扑和一致性边界变化。需要按业务选择主库读取、因果一致性策略或等待复制追赶。

同时，DDL、索引创建、统计信息更新也可能改变执行计划，但不应改变满足 SQL 语义的结果。若结果变化，优先检查：

未指定的排序；
并发事务；
NULL；
重复数据；
非确定的分组或连接条件；
数据库版本或 SQL 模式差异。

十、与 Schema 设计的关系

查询语义经常暴露 Schema 设计问题。

1. 主键与唯一约束决定连接基数

如果 departments.department_id 是主键，那么：

employees.department_id = departments.department_id

从员工连接部门时，理论上每名员工最多匹配一个部门。若部门编号没有唯一约束，查询就不能假设这一点。

2. 外键保证引用关系，但不决定是否允许 `NULL`

下面的外键列可以为空：

department_id INTEGER NULL

这表示员工可以暂时没有部门；外键只约束非空值必须引用存在的部门。若业务要求每名员工必须属于部门，应声明：

department_id INTEGER NOT NULL

然后再添加外键约束。

3. 约束能减少查询中的不确定性

PRIMARY KEY：行的唯一身份；
UNIQUE：列或列组不重复；
NOT NULL：消除空值分支；
FOREIGN KEY：约束引用完整性；
CHECK：限制业务范围。

约束不仅用于写入校验，也帮助工程师正确推断连接结果和基数。查询中依赖的“每个部门只有一行”“每个订单只有一个客户”等事实，最好由数据库约束表达，而不是只写在代码注释里。

十一、跨 PostgreSQL 与 MySQL 的使用边界

两者都实现大量标准 SQL，但以下差异值得在跨数据库项目中显式处理：

FULL OUTER JOIN：PostgreSQL 原生支持；MySQL 8.4 没有同名原生语法。
集合运算的具体支持、括号规则和类型转换细节应以目标版本为准。
字符串、日期、布尔值、标识符引用和隐式类型转换规则存在差异。
GROUP BY 对非分组列的容忍程度可能受 SQL 模式影响；不要依赖“随便返回某一行”的行为。
NULL 排序位置、字符排序规则和大小写比较可能不同。
窗口 frame、日期类型和函数参数的细节必须在目标引擎中验证。
CTE 是否物化、子查询如何改写属于实现和版本相关行为，不能据此推断通用性能结论。

因此，跨引擎 SQL 的可靠验证至少应包含：

相同 Schema 和约束；
相同测试数据，尤其包括重复值和 NULL；
相同事务边界；
相同排序要求；
对边界结果和执行计划分别测试。

SQL 的逻辑语义负责定义“哪些行应该出现”；Schema 约束负责减少不确定性；事务负责定义“查询在并发世界中看到什么”；优化器负责选择“用什么物理方式得到这些行”。理解这四个层次，才能准确使用连接、子查询、窗口函数和集合运算，而不会把某次执行结果、某个索引路径或某种数据库习惯误认为 SQL 的普遍规则。

系列导航与关联阅读

官方资料

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。

SQL 完整基础：查询逻辑顺序、连接、子查询、窗口函数与集合运算

一、先建立查询的基本模型

1. 表不是“有顺序的数组”

2. NULL 不是普通值

二、SQL 的逻辑查询顺序

1. 示例数据

第一步：FROM 与 LEFT JOIN

第二步：WHERE

第三步：GROUP BY