罗罗编程学习记录
本页内容 展开查看

05.7.1 查询语法与执行顺序

所属:全栈开发面试题库 / 05 数据库与存储 / 05.7 SQL 语言与查询技巧

覆盖范围

  • SQL 子句的书写顺序与逻辑执行顺序
  • FROM/JOIN、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT 的处理时机
  • WHERE 与 HAVING 的本质区别与执行阶段
  • 聚合、分组与 NULL 在分组中的语义
  • LIMIT/OFFSET 与分页的执行位置
  • 执行顺序对别名、子查询、列引用的约束

高频题速览

编号 题目 难度 频率
05.7.1-001 SQL 的逻辑执行顺序是怎样的 基础 ★★★★★
05.7.1-002 WHERE 和 HAVING 有什么区别 基础 ★★★★★
05.7.1-003 为什么 WHERE 里不能用 SELECT 里定义的列别名 进阶 ★★★★
05.7.1-004 GROUP BY 与 NULL、聚合函数对 NULL 的处理 进阶 ★★★★
05.7.1-005 ORDER BY 和 LIMIT 在什么阶段执行 进阶 ★★★★
05.7.1-006 SELECT DISTINCT 与 ORDER BY 列的约束 高级 ★★★
05.7.1-007 JOIN 顺序与优化器重排的关系 进阶 ★★★

逻辑执行主线

书写顺序:FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT
逻辑执行:
  1. FROM/JOIN  :确定数据源,生成笛卡尔积/连接中间结果
  2. WHERE       :行级过滤(此时还没有分组、没有聚合)
  3. GROUP BY    :按分组键折叠成行组
  4. HAVING      :组级过滤(可使用聚合结果)
  5. SELECT      :计算列表达式、聚合函数、生成输出列
  6. DISTINCT    :对输出结果去重
  7. ORDER BY    :对最终结果排序
  8. LIMIT/OFFSET:截取行

05.7.1-001 SQL 语句的逻辑执行顺序是什么?和书写顺序有何不同?

频率 ★★★★★ | 难度 基础

一句话结论

书写顺序是 SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT,但逻辑执行顺序是 FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT;SELECT 写在最前却在 WHERE/GROUP BY 之后才执行,这是理解别名、过滤、聚合约束的关键。

概念解释

  • 书写顺序:面向人的阅读习惯,SELECT 先出现。
  • 逻辑执行顺序:面向数据的处理流程,先确定"从哪取数",再"过滤/分组/聚合",最后"投影列、排序、截取"。
  • 物理执行顺序:优化器会基于代价重排连接、谓词下推,不必严格按逻辑顺序执行,但语义等价。

原理流程

  1. FROM/JOIN:读取表与连接,形成行集合。
  2. WHERE:对行集合做行级过滤,无法使用聚合。
  3. GROUP BY:按分组键折叠。
  4. HAVING:对分组结果过滤,可使用 COUNT/SUM 等聚合。
  5. SELECT:计算输出列与表达式。
  6. DISTINCT:去重。
  7. ORDER BY:排序。
  8. LIMIT/OFFSET:截取。

代码 / 示例

SELECT dept_id, COUNT(*) AS cnt
FROM employees
WHERE status = 'active'          -- 先过滤行
GROUP BY dept_id                 -- 再分组
HAVING COUNT(*) > 5              -- 再过滤组
ORDER BY cnt DESC                -- 最后排序
LIMIT 10;

常见误区

  • 误区:SELECT 最先执行。正解:逻辑上 SELECT 在 WHERE/GROUP BY 之后,所以 WHERE 不能用 SELECT 别名。
  • 误区:物理执行顺序就是逻辑顺序。正解:优化器可做谓词下推、连接重排,只要结果等价即可。

追问

  • 问:为什么了解执行顺序有助于写 SQL?答:能判断哪些过滤该放 WHERE、哪些放 HAVING,以及为什么某些列引用报错。

05.7.1-002 WHERE 和 HAVING 有什么区别?能否互换?

频率 ★★★★★ | 难度 基础

一句话结论

WHERE 在分组前对原始行做行级过滤,不能使用聚合函数;HAVING 在 GROUP BY 后对组做组级过滤,可以使用聚合函数;能下推到 WHERE 的条件应尽量放 WHERE,以减少参与分组的数据量。

概念解释

  • WHERE:行级过滤,作用于单条记录,发生在聚合之前。
  • HAVING:组级过滤,作用于分组后的结果,发生在聚合之后。
  • 下推收益:过滤条件放 WHERE 可提前减少行数,降低分组与聚合成本。

原理流程

  1. WHERE 先把不需要的行剔除。
  2. 剩下的行进入 GROUP BY 聚合。
  3. HAVING 再用聚合结果(如 COUNT(*)>5)过滤组。

代码 / 示例

-- 正确:部门状态过滤放 WHERE,组规模过滤放 HAVING
SELECT dept_id, COUNT(*)
FROM employees
WHERE status = 'active'      -- 行级:先剔除离职员工
GROUP BY dept_id
HAVING COUNT(*) > 5;        -- 组级:再剔除人数少的部门

-- 错误:WHERE 中不能用聚合
-- WHERE COUNT(*) > 5        -- 报错:聚合此时还未发生

常见误区

  • 误区:把所有过滤都写 HAVING 结果一样。正解:对非聚合列的条件放 HAVING 也能过滤,但会多处理无用行,性能差。
  • 误区:WHERE 里能写 SUM(x)>100。正解:语义上不合法,因为此时尚未聚合。

追问

  • 问:没有 GROUP BY 时 HAVING 能用吗?答:可以,此时整个结果视为一个隐式组,HAVING 可对聚合结果过滤。

05.7.1-003 为什么 WHERE 里不能直接使用 SELECT 中定义的列别名?

频率 ★★★★ | 难度 进阶

一句话结论

因为逻辑执行顺序中 WHERE 在 SELECT 之前,别名在 WHERE 执行时还未被计算;但部分方言(如 MySQL)允许在 WHERE/GROUP BY 中引用别名作为扩展,标准 SQL 不允许,ORDER BY 则普遍允许使用别名。

概念解释

  • 别名作用域:SELECT 列表中的别名在 WHERE 阶段尚不可见,因为 SELECT 还没执行。
  • 方言扩展:MySQL、PostgreSQL 等对 ORDER BY/GROUP BY 引用别名有宽松处理,跨库时行为不一致。

原理流程

  1. 优化器先解析 FROM/WHERE。
  2. 此时 SELECT 别名还不存在。
  3. 到 SELECT 阶段才生成别名。

代码 / 示例

-- 标准 SQL:报错
SELECT salary * 12 AS annual
FROM employees
WHERE annual > 100000;   -- 标准 SQL:WHERE 看不到 annual

-- 写法一:重复表达式
WHERE salary * 12 > 100000

-- 写法二:用子查询/CTE 把别名物化后再过滤
SELECT * FROM (
  SELECT salary * 12 AS annual FROM employees
) t
WHERE annual > 100000;

-- ORDER BY 普遍允许别名(执行顺序靠后)
SELECT salary * 12 AS annual FROM employees ORDER BY annual;

常见误区

  • 误区:MySQL 里 WHERE 能用别名,所以标准 SQL 也行。正解:那是 MySQL 的方言扩展,迁移到其他库会报错。
  • 误区:GROUP BY 不能用别名。正解:PostgreSQL、MySQL 允许 GROUP BY 引用 SELECT 别名,SQL Server 不允许,跨库要小心。

追问

  • 问:如何写一个跨方言都安全的"用别名过滤"?答:用子查询或 CTE 包裹一层,把别名先算出来。

05.7.1-004 GROUP BY 对 NULL 如何处理?聚合函数如何对待 NULL?

频率 ★★★★ | 难度 进阶

一句话结论

GROUP BY 把所有 NULL 分到同一组(NULL 视为相等),COUNT(*) 统计行数包含 NULL 行,COUNT(列) 忽略该列 NULL,SUM/AVG/MAX/MIN 忽略 NULL 值,绝大多数聚合默认结果忽略 NULL。

概念解释

  • 分组 NULL:GROUP BY col 时所有 col IS NULL 的行归为一组。
  • COUNT(*):统计行数,不看列值。
  • COUNT(列):统计该列非 NULL 行数。
  • SUM/AVG:跳过 NULL,AVG 只对非 NULL 值求平均。

原理流程

  1. 按分组键折叠,NULL 归一组。
  2. 聚合函数逐行处理,遇到 NULL 按各函数语义跳过或计入。

代码 / 示例

-- dept_id 为 NULL 的员工会形成一个组
SELECT dept_id, COUNT(*), COUNT(dept_id), AVG(salary)
FROM employees
GROUP BY dept_id;

-- COUNT(*) vs COUNT(列)
-- COUNT(*)        = 5(含 dept_id 为 NULL 的行)
-- COUNT(dept_id)  = 4(忽略了 dept_id 为 NULL 的行)

-- AVG 忽略 NULL:AVG(salary) = 非 NULL salary 的平均,不是除总行数

常见误区

  • 误区:NULL 在 GROUP BY 里分散到不同组。正解:所有 NULL 是同一组。
  • 误区:COUNT(列) 等于 COUNT(*)。正解:列有 NULL 时二者不等。
  • 误区:AVG 会把 NULL 当 0。正解:AVG 跳过 NULL,不会补 0;要补 0 用 COALESCE。

追问

  • 问:如何让 NULL 单独显示为"(无部门)"?答:GROUP BY COALESCE(dept_id, -1) 或在 SELECT 中 COALESCE(dept_id,'未分配')。

05.7.1-005 ORDER BY 和 LIMIT 在什么阶段执行?对查询性能有何影响?

频率 ★★★★ | 难度 进阶

一句话结论

ORDER BY 在 SELECT/DISTINCT 之后、LIMIT 之前执行,是对最终结果集排序;LIMIT 最后截取,因此"先排序再截取"意味着排序仍可能需要处理全量结果,利用索引有序性可避免额外排序。

概念解释

  • 排序阶段:ORDER BY 在所有过滤、分组、投影之后执行。
  • Top-N 优化:若排序列命中索引顺序,数据库可边扫边取前 N 行,避免全量排序。
  • filesort:无法利用索引时需在内存/磁盘排序大结果集。

原理流程

  1. 完成 FROM/WHERE/GROUP BY/HAVING/SELECT。
  2. 对结果集按 ORDER BY 排序。
  3. LIMIT 截取前 N 行。

代码 / 示例

-- 利用索引 (dept_id, salary DESC):可避免 filesort
SELECT * FROM employees
WHERE dept_id = 10
ORDER BY salary DESC
LIMIT 20;

-- 无索引时:数据库需对匹配行全量排序后再截取

常见误区

  • 误区:LIMIT 20 就只排序 20 行。正解:若不能利用索引顺序,仍需对全量匹配行排序。
  • 误区:ORDER BY 可以用 WHERE 里不存在的列。正解:ORDER BY 的列必须在结果集可见范围内(DISTINCT 时有额外约束)。

追问

  • 问:深分页 LIMIT 100000, 20 为什么慢?答:要扫描并丢弃前 100000 行,且排序成本高;用游标/seek 分页可缓解。

05.7.1-006 SELECT DISTINCT 时 ORDER BY 有什么约束?

频率 ★★★ | 难度 高级

一句话结论

当 SELECT 使用 DISTINCT 时,ORDER BY 的排序列必须出现在 SELECT 列表中,否则数据库无法确定该列在去重后的取值;这是因为去重后每行是一组相同投影列的折叠。

概念解释

  • DISTINCT 语义:对 SELECT 列表的整行组合去重。
  • 排序可见性:ORDER BY 的列必须在去重后的投影中存在,否则同一组内该列可能有多个值,排序不确定。

代码 / 示例

-- 合法:ORDER BY 列在 SELECT 列表中
SELECT DISTINCT dept_id
FROM employees
ORDER BY dept_id;

-- 报错/不确定:salary 不在 SELECT 列表,去重后同 dept_id 对应多个 salary
-- SELECT DISTINCT dept_id FROM employees ORDER BY salary;

常见误区

  • 误区:DISTINCT 后还能按未选出的列排序。正解:标准 SQL 禁止,行为不确定或报错。

追问

  • 问:想按业务字段排序又要去重怎么办?答:改用 GROUP BY 或窗口函数 ROW_NUMBER() 取每组代表行。

05.7.1-007 JOIN 的书写顺序和优化器的连接顺序是什么关系?

频率 ★★★ | 难度 进阶

一句话结论

书写顺序只影响可读性,优化器会基于统计信息和代价模型重新选择连接顺序(join reorder)、连接算法(Nested Loop/Hash Join/Merge Join);但非确定性的连接写法(如 MySQL 旧版 STRAIGHT_JOIN、SQL Server FORCE ORDER)会强制固定顺序。

概念解释

  • 逻辑连接:FROM 子句中表的书写顺序不决定执行顺序。
  • 代价优化:优化器枚举多种连接顺序,选择代价最小者。
  • 连接算法:小表驱动大表用 Nested Loop,大表用 Hash Join,有序大表用 Merge Join。

原理流程

  1. 优化器收集表的行数、索引、过滤后基数。
  2. 枚举连接顺序与算法组合。
  3. 选代价最低的执行计划。

代码 / 示例

-- 书写顺序不决定执行顺序
SELECT *
FROM orders o JOIN customers c ON o.cid = c.id
WHERE o.amount > 100;

-- MySQL 可用 STRAIGHT_JOIN 强制顺序(慎用)
SELECT * FROM orders o STRAIGHT_JOIN customers c ON o.cid = c.id;

常见误区

  • 误区:把小表写在 FROM 后面就能当驱动表。正解:优化器会自己重排;要强制需用提示。
  • 误区:LEFT JOIN 顺序也能被重排。正解:外连接语义约束了保留表,优化器不能随意交换 LEFT JOIN 的左右表。

追问

  • 问:为什么多表连接超过一定数量优化器会变慢?答:连接顺序组合数随表数指数增长,优化器会用启发式剪枝(如 PostgreSQL 限制 join_collapse_limit)。

待整理清单

  • [ ] 谓词下推(predicate pushdown)在各执行阶段的具体表现
  • [ ] CTE 与子查询在执行顺序上的差异
  • [ ] 窗口函数在逻辑执行顺序中的位置(详见 05.7.2)
  • [ ] 各方言对别名引用(WHERE/GROUP BY/ORDER BY)的差异对照表