4、子查询与派生表：标量子查询、列子查询、行子查询、表子查询、EXISTS与IN的对比、相关子查询与非相关子查询

子查询（Subquery）是SQL中嵌套在另一个查询内部的查询，它允许我们将一个查询的结果作为另一个查询的输入条件或数据源。在《SQL数据库查询与优化实战》中，子查询与派生表是构建复杂查询逻辑、实现多表间关联分析的核心工具。本章将系统拆解子查询的四种返回类型（标量、列、行、表），深入对比 EXISTS 与 IN 的性能与语义差异，并厘清相关子查询与非相关子查询的执行机制。理解这些概念不仅有助于写出功能正确的SQL，更是后续进行查询改写与执行计划优化的基础。

子查询与派生表：类型与执行逻辑总览 主查询 (Outer Query) 标量子查询 列子查询 行子查询 表子查询/派生表 返回: 单行单列 用于比较运算 =, >, < 返回: 单列多行 用于 IN / ANY / ALL 返回: 单行多列 用于行构造器比较 返回: 多行多列 用于 FROM 子句 / JOIN 执行逻辑分类 非相关子查询 (Nested) 先执行子查询，结果缓存 再执行外层查询 执行次数: 1次 适合: 子查询结果集小且固定 相关子查询 (Correlated) 外层每处理一行，子查询执行一次 子查询引用外层列 执行次数: N次 (N=外层行数) 适合: 需要逐行关联判断的场景 EXISTS 通常比 IN 更优（当子查询结果集大时），但需结合索引与数据分布判断

4.1 标量子查询：单值返回与上下文约束

核心原理：标量子查询（Scalar Subquery）是返回且仅返回一行一列的子查询，其输出结果在语义上等同于一个普通值。它可以用在任何期望单个值出现的SQL表达式中，例如 SELECT 列表、WHERE 比较条件、ORDER BY 排序键，甚至 VALUES 子句中。标量子查询的关键约束是：结果集必须严格为单行单列，否则数据库会抛出运行时错误（如 ORA-01427 或类似错误）。

判断方法与操作流程：在编写标量子查询时，需要确保子查询的 SELECT 列表只有一个表达式，并且通过聚合函数（如 MAX、MIN）或 WHERE 条件保证返回唯一行。如果业务逻辑上可能返回多行，应使用聚合函数包裹，或改用其他子查询类型。

实战场景：查询员工及其所在部门的平均薪资

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

此查询中，内层子查询针对每个员工所属部门计算平均薪资，返回单值。若部门表中存在多个匹配行，则子查询会报错；因此实际开发中需确保 department_id 唯一，或使用 AVG 聚合保证单行输出。

常见误区：初学者容易忽略标量子查询对“单行”的严格要求。当子查询返回多行时，数据库不会自动取第一行，而是直接报错。此外，标量子查询在 SELECT 列表中使用时，若外层表数据量极大，子查询会被反复执行，造成性能瓶颈。此时应优先考虑使用 JOIN 或窗口函数替代。

4.2 列子查询与行子查询：多值比较与行构造器

核心原理：列子查询（Column Subquery）返回单列多行数据，通常与 IN、ANY、ALL 操作符配合使用，用于判断外层表达式的值是否匹配子查询结果集中的任意值。行子查询（Row Subquery）则返回单行多列，常与行构造器（Row Constructor）结合，用于多字段同时比较的场景，例如 (col1, col2) = (SELECT col1, col2 FROM ...)。

操作流程与案例拆解：列子查询最典型的应用是 WHERE department_id IN (SELECT department_id FROM departments WHERE location = '北京')。而行子查询则适用于复合条件匹配，例如查找与指定员工在相同部门和相同职位的所有同事：

SELECT employee_name, department_id, job_id
FROM employees
WHERE (department_id, job_id) = (
    SELECT department_id, job_id
    FROM employees
    WHERE employee_id = 200
);

此查询中，内层返回一行两列，外层通过行构造器进行等值匹配。这种写法比分别写两个子查询更简洁，且能保证两个条件同时满足。

子查询类型返回形状常用操作符典型场景
标量子查询单行单列=, >, <, <=, >=计算列、比较条件
列子查询单列多行IN, ANY, ALL集合成员判断
行子查询单行多列行构造器 =, >, <复合条件匹配
表子查询多行多列FROM, JOIN派生表、临时数据集

常见误区：行子查询在比较时，字段顺序必须与行构造器完全一致，且数据库对行比较的支持程度不同（MySQL支持，Oracle支持，SQL Server部分支持）。列子查询使用 IN 时，若子查询结果包含 NULL，则整个 IN 判断可能返回“未知”而非“真”，导致查询结果不符合预期。建议在子查询中显式过滤 NULL 值。

4.3 表子查询与派生表：FROM 子句中的数据源

核心原理：表子查询（Table Subquery）返回多行多列，通常出现在 FROM 子句中，作为一个派生表（Derived Table）供外层查询引用。派生表必须拥有别名，且其列可以在外层查询中直接使用。表子查询是SQL中实现“先过滤、再关联”或“先聚合、再连接”的重要工具，能够有效减少中间结果集的大小。

操作流程与实战场景：假设我们需要统计每个部门中薪资高于部门平均值的员工数量。直接使用 GROUP BY 无法一步完成，此时可以借助派生表：

SELECT d.department_name, COUNT(e.employee_id) AS high_salary_cnt
FROM departments d
LEFT JOIN (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
) dept_avg ON d.department_id = dept_avg.department_id
LEFT JOIN employees e ON d.department_id = e.department_id 
    AND e.salary > dept_avg.avg_sal
GROUP BY d.department_name;

此查询中，派生表 dept_avg 预先计算了每个部门的平均薪资，外层再通过两次关联完成统计。这种写法将复杂问题拆解为“先聚合、后关联”两个步骤，逻辑清晰且便于优化。

经验提醒：派生表在MySQL中会被物化（Materialize），即子查询结果会先写入临时表再参与外层查询。如果派生表数据量极大，物化成本会很高。在MySQL 5.7+中，优化器会将部分派生表合并到外层查询（Derived Condition Pushdown），但并非所有场景都适用。对于复杂查询，建议使用 EXPLAIN 查看执行计划，确认派生表是否被物化。

常见误区：派生表不能直接引用外层查询的列（即派生表本身是非相关的），如果需要在子查询中引用外层列，应使用相关子查询或 LATERAL JOIN（PostgreSQL、MySQL 8.0+ 支持）。此外，派生表在 WHERE 子句中不能直接使用，必须通过 FROM 或 JOIN 引入。

4.4 EXISTS 与 IN 的深度对比

核心原理：EXISTS 和 IN 都用于判断外层表达式与子查询结果集的关系，但执行逻辑截然不同。IN 会先执行子查询，将结果集物化（或哈希）后，再与外层每一行进行匹配；而 EXISTS 则采用“半连接”（Semi-Join）语义，对外层每一行，子查询只需找到第一条匹配记录即返回真，不再继续扫描。因此，EXISTS 在子查询结果集很大、外层表较小的情况下通常性能更优。

对比分析表：

维度INEXISTS
执行方式先物化子查询结果，再逐行匹配逐行驱动，子查询找到匹配即停止
NULL 处理子查询含 NULL 时可能导致结果不准确不受 NULL 影响，仅判断是否存在
子查询引用外层列通常不支持（非相关）天然支持相关子查询
性能倾向子查询结果集小且稳定时较好子查询结果集大、外层表小时较好
典型场景静态集合判断动态关联存在性检查

案例拆解：查询有订单的客户

-- 使用 IN
SELECT customer_name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

-- 使用 EXISTS
SELECT c.customer_name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

在 orders 表数据量极大且 customer_id 有索引时，EXISTS 版本通常更快，因为数据库可以在找到第一条匹配订单后立即停止扫描。而 IN 版本需要先扫描整个 orders 表生成去重后的客户ID列表，再与 customers 表关联。但若 orders 表很小，IN 的物化成本可忽略，两者性能差异不大。

风险提示：在 EXISTS 子查询中，SELECT 列表通常写 SELECT 1 或 SELECT *，数据库优化器会忽略选择列表，因此无需担心列开销。但切勿在 EXISTS 子查询中使用聚合函数（如 COUNT(*)），因为聚合后总是返回一行，导致 EXISTS 恒为真。

4.5 相关子查询与非相关子查询：执行机制与性能差异

核心原理：非相关子查询（Nested Subquery）的执行顺序是“由内到外”，子查询独立执行一次，结果集被缓存供外层使用。相关子查询（Correlated Subquery）则相反，子查询引用了外层查询的列，导致外层每处理一行，子查询就需要重新执行一次。因此，相关子查询的执行次数等于外层结果集的行数，性能开销通常远高于非相关子查询。

判断方法：检查子查询的 WHERE 条件中是否引用了外层表的列名。如果引用了，即为相关子查询；否则为非相关。例如：

-- 非相关：子查询独立
SELECT * FROM employees 
WHERE department_id IN (SELECT department_id FROM departments WHERE location = '上海');

-- 相关：子查询引用外层 e.department_id
SELECT e.employee_name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

实战场景与优化策略：相关子查询常用于“逐行比较”场景，如查找高于本部门平均薪资的员工。但若部门数量少、员工数量多，相关子查询会执行上万次子查询，性能堪忧。优化方案有两种：一是改写为派生表 + JOIN（如4.3节案例）；二是使用窗口函数 AVG() OVER(PARTITION BY department_id) 一次性计算部门均值，避免逐行子查询。

案例复盘：从相关子查询到窗口函数的优化

初始SQL（相关子查询，执行耗时约 2.3 秒，employees 表 10 万行）：

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

优化后SQL（窗口函数，执行耗时约 0.4 秒）：

SELECT employee_name, salary
FROM (
    SELECT employee_name, salary,
           AVG(salary) OVER(PARTITION BY department_id) AS dept_avg
    FROM employees
) t
WHERE salary > dept_avg;

优化原理：窗口函数只需扫描一次表，计算所有部门的平均值，而相关子查询需要为每个员工单独扫描一次部门数据。在数据量大的场景下，性能差距可达数倍甚至数十倍。

常见误区与总结：不要盲目认为 EXISTS 一定比 IN 快，也不要认为相关子查询一定不可用。关键在于数据分布、索引情况和结果集大小。实际开发中，应通过 EXPLAIN 分析执行计划，观察子查询的执行次数（如 MySQL 的 DEPENDENT SUBQUERY 标记）和扫描行数。对于相关子查询，优先考虑改写为 JOIN 或窗口函数；对于非相关子查询，确保子查询结果集有索引支持，避免全表扫描。

子查询与派生表是SQL语言中极具表达力的特性，掌握其类型划分、执行逻辑与优化手段，是迈向高级SQL开发者的关键一步。在实际业务中，建议遵循“先正确、再优化”的原则，先确保查询逻辑准确，再通过执行计划定位性能瓶颈，选择合适的改写策略。

← 上一章 📖 返回目录 下一章 →