SQL Subquery Guide: Types, Examples, and Refactoring SQL 子查询完整指南:类型、示例与重构方法

What is a subquery in SQL? SQL 子查询是什么?

SELECT product_id, price
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

The inner query returns one average and the outer query compares every product with it. This shape is concise because the inner result has a guaranteed scalar grain. Before using any subquery, write its contract: which outer values it can see, how many rows and columns it may return, and how zero rows or NULL should affect the answer.

内层查询返回一个平均值,外层查询将每个产品与该值比较。由于内层结果具有确定的标量粒度,这种写法很清晰。使用任何子查询前,都应写出它的契约:能够引用哪些外层值、可能返回多少行列,以及零行或 NULL 应如何影响答案。

Choose a subquery type from the question you need to answer 根据问题选择子查询类型

Subqueries are not one technique. A scalar subquery supplies one value. A row subquery supplies a tuple where the database supports row comparison. A set subquery feeds IN , ANY , or ALL . An existence subquery answers whether at least one qualifying record exists. A derived table creates an intermediate rowset that can be joined, filtered, or aggregated. A correlated subquery reads values from the current outer row.

子查询并不是单一技术。标量子查询提供一个值;数据库支持行比较时,行子查询可提供一个元组;集合子查询为 IN 、 ANY 或 ALL 提供数据;存在性子查询判断是否至少有一条合格记录;派生表创建可继续连接、过滤或聚合的中间记录集;相关子查询则引用当前外层记录的值。

Choose the shape that expresses intent directly. Returning full child rows and later deduplicating them is a poor substitute for EXISTS when the question is purely existential. Conversely, EXISTS cannot provide child attributes that belong in the output. Clarity about the contract prevents accidental grain changes.

应选择能够直接表达意图的形式。问题只关心是否存在时,先返回全部子记录再去重不如 EXISTS 清晰;反过来,如果输出需要子级属性, EXISTS 又无法提供。明确返回契约可以防止结果粒度被意外改变。

Make scalar subqueries truly single-valued 确保标量子查询真正只返回一个值

A scalar subquery can appear in a comparison, projection, calculation, or conditional expression. Zero returned rows usually becomes NULL; more than one row normally raises an error. The dangerous pattern is relying on accidental uniqueness or adding an unordered LIMIT 1 . That suppresses the error but does not define which related row is correct.

标量子查询可以出现在比较、投影、计算或条件表达式中。返回零行时通常得到 NULL,返回多行时通常报错。危险做法是依赖偶然唯一性,或随意增加无排序的 LIMIT 1 :它虽然压制错误,却没有定义哪条关联记录才正确。

SELECT
  c.customer_id,
  (
    SELECT s.status
    FROM customer_status s
    WHERE s.customer_id = c.customer_id
    ORDER BY s.effective_at DESC, s.status_id DESC
    FETCH FIRST 1 ROW ONLY
  ) AS latest_status
FROM customers c;

The secondary key makes ties deterministic, but verify that "latest" is the right business rule and that late-arriving corrections are modeled correctly. If several scalar subqueries independently probe the same child table, calculate the desired child row once with a lateral join, windowed derived table, or named stage instead of repeating the access path.

第二排序键使并列结果确定,但仍要确认"最新"符合业务规则,并正确处理延迟到达的修正。如果多个标量子查询反复访问同一张子表,应通过横向连接、带窗口函数的派生表或命名阶段一次选出目标记录,而不是重复访问。

Use IN and EXISTS with deliberate NULL semantics 有意识地处理 IN 与 EXISTS 的 NULL 语义

IN asks whether a value equals any value returned by a subquery. EXISTS asks whether the subquery returns at least one row, and the selected columns inside it are irrelevant. Optimizers can often transform both into semi-join strategies, so choose by meaning first. The largest correctness difference appears with negation and NULL.

IN 判断某个值是否等于子查询返回集合中的任一值; EXISTS 只判断子查询是否至少返回一行,内部选择哪些字段并不重要。优化器经常可以把两者转换为半连接策略,因此首先应依据语义选择。两者最大的正确性差异出现在否定和 NULL 场景。

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'open'
);

NOT IN can become unknown for every outer value when the returned set contains NULL, producing no rows. You can filter nulls deliberately, but NOT EXISTS usually communicates anti-existence more safely. Test a null outer key, a null inner key, an empty inner set, and a mixture of matching and nonmatching rows. Do not assume constraints exist unless the schema actually enforces them.

如果返回集合中包含 NULL, NOT IN 对每个外层值都可能得到 unknown,最终不返回任何记录。虽然可以明确过滤 NULL,但 NOT EXISTS 通常更安全地表达"不存在"。应测试外层空键、内层空键、空集合以及匹配与不匹配混合场景,并确认数据库确实实施了相关约束。

Recognize correlation and repeated-work risk 识别相关性与重复执行风险

A correlated subquery references a column from the outer query. Logically, it is evaluated for the current outer row, although an optimizer may decorrelate it into a join, semi-join, aggregate, or other set operation. Correlation is useful when the inner predicate naturally depends on each entity, such as comparing an employee with the average of that employee's department.

相关子查询引用外层查询字段。从逻辑上说,它会针对当前外层记录求值,但优化器可能把它去相关为连接、半连接、聚合或其他集合操作。当内部条件天然依赖每个实体时,相关子查询很实用,例如把员工薪资与其所在部门平均薪资比较。

SELECT e.employee_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(x.salary)
  FROM employees x
  WHERE x.department_id = e.department_id
);

Inspect the actual plan rather than assuming one execution per row or perfect decorrelation. Warning signs include a large outer input, millions of inner loops, repeated scans, volatile functions, range predicates, and weak indexes. A pre-aggregated department stage joined once may be clearer and faster, but it can change NULL or empty-group behavior, so prove equivalence with edge cases.

不能假设每行一定执行一次,也不能假设优化器总能完美去相关,必须检查实际计划。风险信号包括外层输入很大、内层循环数达到数百万、重复扫描、易变函数、范围条件和弱索引。先按部门聚合再连接可能更清晰更快,但会改变 NULL 或空组行为,因此必须用边界样本证明等价。

Use derived tables to establish a controlled intermediate grain 使用派生表建立可控的中间粒度

A subquery in FROM creates a derived table. This is valuable when a stage must filter, aggregate, rank, or reshape data before another relationship. Name every output column clearly and document what one row represents. If the derived table aggregates orders to one row per customer, the outer join should rely on that grain rather than assuming it.

FROM 中的子查询会创建派生表,适合在后续关系前完成过滤、聚合、排序或重塑。应明确命名每个输出字段,并说明一行代表什么。如果派生表把订单聚合为每位客户一行,外层连接就应明确依赖这一粒度,而不是默默假设。

SELECT c.customer_id, x.order_count, x.revenue
FROM customers c
JOIN (
  SELECT
    customer_id,
    COUNT(*) AS order_count,
    SUM(order_total) AS revenue
  FROM orders
  GROUP BY customer_id
) x ON x.customer_id = c.customer_id;

This prevents order rows from multiplying another customer-level relationship, but it also removes customers without orders because the outer join is inner. Choose join preservation independently from aggregation. Some engines materialize a stage, while others inline it; treat a derived table as a semantic boundary first, not a guaranteed performance barrier.

这种写法可以防止订单明细放大另一条客户级关系,但由于外层使用内连接,没有订单的客户会被删除。连接的保留语义与聚合方式应分别决定。某些数据库会物化阶段,另一些会内联,因此首先把派生表视为语义边界,而不是保证存在的性能屏障。

Limit nesting that hides grain and decision points 限制会隐藏粒度与决策点的深层嵌套

Deep nesting is not automatically slow, but it increases review cost. Readers must track aliases, correlated references, aggregation levels, preservation rules, and filters across several scopes. Repeated subqueries can also calculate the same intermediate result more than once. A useful refactoring extracts stages when they have a nameable business meaning, a reusable result, or a validation invariant.

深层嵌套并不必然缓慢,但会显著增加评审成本。读者需要跨多个作用域追踪别名、相关引用、聚合层级、保留规则和过滤条件;重复子查询还可能多次计算同一中间结果。当某个阶段具有可命名业务含义、可复用结果或可验证不变量时,就适合提取出来。

1. Annotate grain. Write what one row means after every subquery.
2. Mark outer references. Identify correlation and expected probe counts.
3. Find repeated logic. Compare predicates, joins, and aggregates structurally.
4. Extract named stages. Use a CTE or view when the stage deserves an identity.
5. Validate boundaries. Measure rows, distinct keys, nulls, and totals at each stage.
6. Inspect the final plan. Confirm whether the engine inlines, materializes, or repeats work.

1. 标注粒度。 写明每个子查询后的一行代表什么。
2. 标出外层引用。 识别相关性与预期探测次数。
3. 发现重复逻辑。 从结构上比较条件、连接与聚合。
4. 提取命名阶段。 阶段值得独立命名时使用 CTE 或视图。
5. 验证边界。 测量每阶段行数、不同键、NULL 和总量。
6. 检查最终计划。 确认数据库是内联、物化还是重复执行。

Diagnose subquery performance with execution evidence 使用执行证据诊断子查询性能

Start by locating work that grows with the outer row count. In an actual plan, inspect loops, rows per loop, total rows, buffer reads, elapsed time, sorts, spills, and remote calls. A cheap inner probe repeated ten times may be fine; the same probe repeated ten million times is not. Parameter-sensitive selectivity and skew can make one execution fast and another pathological.

首先定位会随外层行数增长的工作。在实际执行计划中检查循环次数、每次循环行数、总行数、缓冲读取、耗时、排序、溢写和远程调用。一次成本很低的内部探测重复十次没有问题,但重复一千万次就不可接受。参数选择性与数据倾斜还可能让一次执行很快、另一次极慢。

Do not optimize by syntax folklore. Replacing every subquery with a join can introduce row multiplication; forcing materialization can add I/O; flattening a scalar check can change zero-row behavior. Benchmark the original and rewrite with identical parameters and validate result equivalence before comparing runtime.

不要依据语法传说优化。把所有子查询改成连接可能引入行数膨胀;强制物化可能增加 I/O;展开标量检查可能改变零行行为。应使用相同参数测试原查询与改写,并在比较性能前验证结果等价。

Validate subquery contracts before production 上线前验证子查询契约

• State whether zero, one, or many rows are allowed.
• Test empty results, one result, several results, and NULL values.
• For scalar selection, define a deterministic business rule instead of arbitrary limiting.
• For NOT IN , prove the returned expression cannot be NULL or use a safer form.
• Measure correlated loop counts with realistic outer populations.
• Compare row counts, distinct keys, and metrics before and after any rewrite.
• Review access permissions and remove sensitive literals before using external tools.
• Keep a regression fixture for ties, late data, duplicate keys, and empty groups.

• 明确允许返回零行、一行还是多行。
• 测试空结果、单结果、多结果和 NULL 值。
• 标量选择应定义确定性业务规则,不要随意限制一行。
• 使用 NOT IN 时证明返回表达式不可能为 NULL,或采用更安全形式。
• 使用真实外层总体测量相关子查询循环次数。
• 任何重构前后都比较行数、不同键和指标。
• 检查访问权限,使用外部工具前移除敏感字面值。
• 为并列、延迟数据、重复键和空组保留回归样本。

Trace a subquery incident from symptom to mechanism 从症状追踪子查询故障机制

Suppose a customer dashboard suddenly reports no eligible accounts after a release. The visible predicate uses account_id NOT IN (SELECT blocked_account_id FROM blocks) . A new ingestion path has introduced one row whose blocked key is NULL. Because comparison with that set becomes unknown, the outer predicate rejects every account. The immediate repair may filter NULL or use NOT EXISTS , but a complete review also asks why a supposedly identifying field accepted NULL and whether other consumers share the same assumption.

假设某次发布后,客户仪表板突然显示没有任何合格账户。页面条件使用 account_id NOT IN (SELECT blocked_account_id FROM blocks) ,而新的数据摄取路径引入了一条屏蔽键为 NULL 的记录。由于与该集合比较会得到 unknown,外层条件拒绝了所有账户。立即修复可以过滤 NULL 或改用 NOT EXISTS ,但完整复盘还必须追问:为什么本应识别实体的字段允许为空,以及其他使用者是否依赖同一假设。

Build the proof in stages. Preserve the original row counts, list null and duplicate keys in the inner result, evaluate the predicate for a tiny set of representative accounts, and compare the repaired query with an independent anti-join control. Then inspect the new actual plan: a semantically safer form can still need an index or updated statistics. Save the failing NULL fixture as a regression test and monitor both blocked-key null rate and eligible-account count. This workflow fixes the mechanism instead of merely restoring today's dashboard.

应分阶段建立证据:保存原始行数,列出内层结果中的 NULL 与重复键,针对少量代表性账户计算条件结果,并把修复后的查询与独立反连接控制查询比较。随后检查新的实际计划,因为语义更安全的形式仍可能需要索引或更新统计信息。最后把触发故障的 NULL 样本保存为回归测试,同时监控屏蔽键空值率与合格账户数量。这样修复的是故障机制,而不只是恢复当天的仪表板。

SQL subquery frequently asked questions SQL 子查询常见问题

Continue with a related guide 继续阅读相关指南

Use these closely related guides to verify the next layer of the workflow without mixing separate troubleshooting or decision intents. 可继续阅读以下紧密相关的指南,验证工作流的下一层,同时避免混淆不同的排障或决策意图。

• Nested SQL queries 嵌套 SQL 查询
• Correlated subqueries 相关子查询
• Common table expressions SQL 公用表表达式