SQL 中的子查询和 CTE
发布: (2026年4月22日 GMT+8 04:51)
5 分钟阅读
原文: Dev.to
Source: Dev.to
在使用 SQL 时，你最终会遇到单个查询不足以解决的情况。你需要将问题拆分成多个部分，计算一个中间结果，然后在其他地方使用该结果。这时子查询和公共表表达式（CTE）就派上用场了。
它们解决类似的问题，但方式略有不同，选择使用哪一种会影响性能、可读性和可维护性。
子查询
子查询是写在另一个查询内部的查询。它产生的结果供外部查询使用——本质上是一个问题中的问题。
子查询的工作原理
- 位置：通常嵌入在
WHERE、SELECT或FROM子句中，具体取决于其作用。 - 使用场景：快速计算或过滤，仅在一次需要结果时使用。
子查询的类型
| Type | Description | Typical Use |
|---|---|---|
| Scalar subquery | 返回单个值 | 比较（例如 WHERE salary > (SELECT AVG(salary) FROM employees)） |
| Multi‑row subquery | 返回多个值 | 如 IN 操作符（例如 WHERE department_id IN (SELECT id FROM departments)） |
| Correlated subquery | 引用外部查询的列；对每行执行一次 | 复杂过滤；如果没有适当的索引可能会更慢 |
| Nested subquery | 子查询嵌套在另一个子查询内部 | 有效，但可能难以阅读；通常是需要重构的信号 |
何时使用子查询
- 逻辑相对简单。
- 中间结果只需要使用一次。
- 想要一个紧凑的查询，而不需要额外的命名。
公共表表达式 (CTE)
CTE 是在查询开头定义的具名临时结果集。你只需定义一次，然后在整个查询中通过名称引用它。
CTE 的优势
- 通过将逻辑划分为具名部分，提高可读性。
- 允许一步步构建查询；一个 CTE 可以依赖另一个 CTE。
- 可以多次复用同一中间结果。
- 支持递归，用于处理层级数据（例如组织结构图、分类树）。
CTE 的类型
- 简单 CTE – 为单一逻辑块提升可读性。
- 多个 CTE – 对复杂转换采用分层方式；每个 CTE 可以基于前一个构建。
- 递归 CTE – 通过反复引用自身来处理层级数据。
比较：子查询 vs. CTE
| 方面 | 子查询 | CTE（公共表表达式） |
|---|---|---|
| 可读性 | 随着嵌套层次增加，可能变得难以跟踪 | 结构化的自上而下流程；更易调试 |
| 性能 | 简单子查询通常经过良好优化；相关子查询可能代价高昂 | 可能会被物化，取决于 DBMS；性能因情况而异 |
| 可复用性 | 通常内联编写；若在多个位置需要则会出现重复 | 定义一次，可多次引用 |
| 复杂度 | 适用于简单、一次性的计算 | 更适合包含多步骤或递归的复杂查询 |
| 层次结构 | 对层次数据并不理想 | 递归 CTE 在层次查询中表现出色 |
实际上，性能更多取决于查询设计和索引，而不是子查询与 CTE 之间的选择。
何时使用每种方式？
- 子查询：当任务简单、结果只需要一次、并且你偏好紧凑的查询时选择。
- 公共表表达式（CTE）：当逻辑复杂、可读性重要、需要复用中间结果，或处理需要递归的层次数据时更适合。
子查询和 CTE 是互补的工具，而非直接竞争对手。通常会先使用子查询，随着查询复杂度提升再切换到 CTE，以获得更清晰、更易维护的 SQL。