SQL 子查询：综合指南

SQL 子查询是数据库管理中的强大工具，可实现更复杂且高效的数据检索。本指南将带您了解 SQL 子查询的基础知识，并提供其实用场景与进阶技巧。无论您是初学者还是有经验的从业者，掌握子查询都能显著提升您的 SQL 技能。

如果您是 SQL 新手，建议先从我们的 Intermediate SQL 课程开始，夯实基础。此外，我认为可下载的 SQL Basics Cheat Sheet 是非常实用的参考资料，因为它涵盖了最常用的 SQL 函数。最后我想说明，子查询是常见的 SQL 面试题，如果您正在准备面试，这里正是复习的理想之地。

什么是 SQL 子查询？

子查询让 SQL 查询更具模块化，能够处理原本需要多条独立查询才能完成的任务。

定义与用途

SQL 子查询是嵌套在另一条 SQL 查询中的查询，用于执行需要多步或复杂逻辑的操作。子查询在 SQL 中的作用包括：

• 基于相关表的数据筛选记录。
• 动态聚合数据并执行计算。
• 在表之间交叉引用数据以获取特定洞察。
• 在无需显式连接或外部代码逻辑的情况下有条件地选择行。

看起来内容很多，但随着我们在教程中逐步探索，您会逐渐理解。

子查询的类型

也许会让您惊讶，子查询其实有不同类型。这些类型按不同的数据检索需求分组并各有所长。您可以根据要执行的操作，从以下子查询类型中选择：

标量子查询（Scalar）

标量子查询只返回单个值，例如一行一列。它们常用于期望单个值的场景，如在 SELECT 或 WHERE 子句中的计算、比较或赋值。

在下面的示例中，标量子查询 (SELECT AVG(salary) FROM employees) 返回一个单值（平均薪资），并将其与每位员工的薪资进行比较。

-- Example of Scalar Subquery 
-- Compares each salary to the average salary
SELECT employee_name, 
       salary,
       (SELECT AVG(salary) FROM employees) AS average_salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);技术支持

列子查询（Column）

列子查询返回单列但多行。此类子查询常与 IN 或 ANY 等运算符配合使用，外层查询会将值与多行结果进行比较。

例如，下面的子查询返回位于纽约的各部门 ID，主查询据此筛选这些部门中的员工。

-- Example of Column Subquery 
-- Filters based on departments in New York
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');技术支持

行子查询（Row）

行子查询返回一行多列。通常与可比较整行数据的运算符一起使用，如 = 或 IN，当需要比较多个值时尤为适用。

以下子查询检索某位经理的部门与职位名称，外层查询据此查找匹配的员工。

-- Example of Row Subquery 
-- Matches department and job title with a specific manager
SELECT employee_name
FROM employees
WHERE (department_id, job_title) = (SELECT department_id, job_title FROM managers WHERE manager_id = 1);技术支持

表子查询（派生表）

表子查询（或派生表）返回由多行多列组成的完整表。它们通常在 FROM 子句中作为查询内的临时表使用。

例如，下方子查询按部门计算平均薪资并生成派生表，外层查询随后用它来筛选平均薪资高于指定阈值的部门。

-- Example of Table Subquery 
-- Uses derived table for average department salary comparison
SELECT dept_avg.department_id, dept_avg.avg_salary
FROM 
    (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) AS dept_avg
WHERE dept_avg.avg_salary > 50000;技术支持

理解 SQL 子查询

从以上示例可见，子查询通常用圆括号括起。现在让我们探讨子查询的运作机制，包括其语法与执行顺序。

语法与结构

子查询的语法会随其在主 SQL 语句中的位置而变化，例如位于 SELECT、FROM 或 WHERE 子句中。子查询通常用括号 ( ) 包裹，表示其为一条独立查询。

以下示例展示了位于 WHERE 子句中的子查询，使我们能够基于嵌套查询的结果来过滤主查询中的数据。

-- Selects the main column to retrieve from the main table to query
SELECT column_name
FROM table_name
-- Applies a condition to filter rows based on the subquery result
WHERE column_name operator 
      -- Subquery retrieves data for comparison in the WHERE clause
      (SELECT column_name FROM table_name WHERE condition);  技术支持

执行顺序

子查询的执行顺序取决于其是否为相关子查询。

非相关子查询

非相关子查询独立于外层查询并先执行，其结果随后传递给外层查询。非相关子查询常用于标量或列级的计算与过滤。

下面的查询遵循如下执行顺序：

• 子查询 (SELECT AVG(salary) FROM employees) 先运行，计算平均薪资。

• 外层查询随后检索薪资高于该平均值的员工。

-- Retrieves names of employees with above-average salary
SELECT employee_name  
FROM employees
-- Subquery: calculates average salary across all employees
WHERE salary > (SELECT AVG(salary) FROM employees);  技术支持

我建议学习 DataCamp 的 Introduction to SQL Server 课程，进一步了解分组、数据聚合与表连接。

相关子查询

相关子查询依赖外层查询的部分数据，因此会对外层查询处理的每一行重新计算。

以下查询的执行顺序为：

• 对于 employees（别名 e1）中的每一行，子查询 (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id) 计算该部门的平均薪资。

• 外层查询将每位员工的薪资与部门平均薪资进行比较，仅保留更高者。

-- Retrieves names of employees with above-average salary in their department
SELECT e1.employee_name  
FROM employees e1
 -- Subquery: calculates average salary for each department
WHERE e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id); 技术支持

SQL 子查询的应用

SQL 子查询在数据检索与转换中十分重要，使您能够编写复杂查询并处理高级数据分析任务。以下是数据库管理中子查询的真实应用场景。

数据过滤

当需要基于动态条件进行过滤时，尤其是跨多表比较或执行计算时，子查询非常有用。

以下子查询检索“Product A”的 category_id，主查询则查找该类别中的所有产品。

-- Retrieves names of products in the same category as 'Product A'
SELECT product_name  
FROM products
 -- Subquery: finds category ID of 'Product A'
WHERE category_id = (SELECT category_id FROM products WHERE product_name = 'Product A'); 技术支持

数据聚合

子查询也可用于数据聚合，尤其适用于生成报告与分析所需的汇总统计或洞察。子查询 (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) 计算各部门的平均销售额。外层查询随后筛选平均销售额高于 50,000 的部门。

-- Retrieves department IDs and their average sales
-- Filters for departments with average sales over 50,000
SELECT department_id, avg_sales  
FROM (SELECT department_id, AVG(sales) AS avg_sales FROM sales GROUP BY department_id) AS dept_sales  -- Subquery: calculates average sales per department
WHERE avg_sales > 50000;  技术支持

性能考量与最佳实践

尽管子查询在编写复杂查询时非常强大，但在处理大型数据集时可能影响性能。为获得更佳表现，务必注意常见陷阱并遵循最佳实践。

优化子查询性能

优化子查询性能可提升查询执行时间与数据库响应速度。以下是优化子查询的方法：

• 为相关列建立索引： 为加快数据检索，确保在 WHERE、JOIN 子句以及比较操作中使用的列已建立索引。

• 减少相关子查询的使用： 在可能的情况下，用 JOIN 操作或 CTE 替代相关子查询，因为基于集合的处理通常比逐行处理更快。

• 精简子查询所选列： 在子查询中仅选择所需列，以减少数据检索量、降低内存占用，并便于数据库优化执行计划。

• 用 EXISTS 替代 IN： 如果子查询返回的数据集很大，使用 EXISTS 往往优于 IN。EXISTS 在找到匹配行时即可停止处理，而 IN 通常会评估完整的子查询结果。

避免常见陷阱

子查询编写不当会导致执行问题。下面来看如何规避这些陷阱。

• 避免不必要的相关子查询： 相关子查询资源消耗较高，若可用非相关子查询或连接实现相同结果，应尽量避免使用。

• 留意子查询中的 NULL 值： NULL 值可能导致意外结果，尤其是在使用 IN 或 = 等比较运算符的子查询中。为避免错误，可使用 COALESCE 处理空值，或确保用于比较的列不可为空。

• 避免在子查询中使用 SELECT *： SELECT * 会检索所有列，即使并不需要，增加内存使用并可能拖慢执行，尤其是在大型数据集上。

• 使用有意义的别名： 清晰命名表与子查询以提升可读性。

试试我们的 SQL Server Developer 职业路径课程，掌握使用 SQL Server 编写、排错与优化查询的技能。

更高级的 SQL 子查询技巧

虽然子查询能高效编写复杂查询，但在处理层级数据时还存在更高级的方法。下面我们来看看应用 SQL 子查询的进阶技巧与策略。

递归子查询

递归子查询（亦称递归公共表表达式，CTE）可用于检索层级数据，如组织结构、产品分类或基于图的关系，其中每个条目彼此关联。

假设您有一张 employees 表，包含 employee_id、manager_id 与 employee_name。您希望检索某位经理之下的员工层级。

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Query: Start with the specified manager
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
 -- Assuming the top-level manager has NULL as manager_id
    WHERE manager_id IS NULL 
    
    UNION ALL
    
    -- Recursive Query: Find employees who report to those in the previous level
    SELECT e.employee_id, e.manager_id, e.employee_name, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM EmployeeHierarchy;技术支持

在上述查询中：

• 所谓的“锚点查询”（Anchor Query）选择最高层经理（manager_id 为 NULL）。

• 递归查询将 employees 与 CTE 本身（EmployeeHierarchy）连接，找到汇报给前一层员工的下属。

• 递归持续，直到不再有员工汇报给已找到的员工为止。

将子查询与其他 SQL 特性结合

您可以将子查询与其他 SQL 特性结合使用，如窗口函数、CASE 表达式与分组函数。这些组合能够实现更高级的数据处理和更全面的报告。

子查询结合窗口函数

子查询可用于精炼窗口函数所作用的数据集，适用于排名、累计总计与移动平均等场景。假设您想在各区域内按销售额对产品进行排名。可以先用子查询选出相关数据，再应用窗口函数进行排名。

 -- Ranks products by sales within each region
SELECT region, product_id, sales, 
       RANK() OVER (PARTITION BY region ORDER BY sales DESC) AS sales_rank 
-- Subquery: calculates total sales per product in each region
FROM (SELECT region, product_id, SUM(sales) AS sales  
      FROM sales_data
      GROUP BY region, product_id) AS regional_sales;技术支持

子查询结合 CASE 表达式

将子查询与 CASE 表达式结合，可基于动态计算应用复杂条件。以下查询按产品相对于其类别平均销售额的表现，将其归类为“High”（高）、“Medium”（中）或“Low”（低）。

 -- Categorize above-average sales, average sales, and below-average sales
SELECT product_id, category_id, sales,
       CASE 
           WHEN sales > (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'High' 
           WHEN sales = (SELECT AVG(sales) FROM products WHERE category_id = p.category_id) THEN 'Medium' 
           ELSE 'Low'
       END AS performance
FROM products AS p;技术支持

结合聚合函数进行条件聚合的子查询

您也可以在聚合函数中使用子查询来计算条件聚合。假设您只想计算活跃客户带来的总营收。下例中，子查询检索所有活跃客户，主查询随后仅统计这些客户的订单，从而计算该群体的总营收。

 -- Calculates total revenue from active customers
SELECT SUM(order_total) AS active_customer_revenue 
FROM orders
  -- Subquery: retrieves IDs of active customers
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'Active'
);技术支持

SQL 子查询的使用场景

SQL 子查询在数据分析中的应用十分广泛，这也是它们被频繁使用的原因。以下是一些面向行业或数学场景的 SQL 子查询应用示例。为提出这些思路，我设想了需要多步处理的数据分析或数据工程工作——在我看来，这正是子查询真正发挥作用的地方。

行业示例

子查询在金融、医疗与零售行业能提供有用的解决方案。以下是一些想法：

• 贷款审批的风险评估（金融）： 我想象银行需要综合债收比、信用评分等指标。通过在子查询中嵌套这些指标，分析师能更好地梳理复杂的财务度量。比如，可用子查询计算特定收入区间客户的平均贷款额。

• 识别患者诊断模式（医疗）： 在医疗领域，管理患者数据是一项艰巨任务。子查询有助于化繁为简。我会用子查询来跟踪不同年龄段或风险因子下的诊断频率。

• 基于购买历史优化商品陈列（零售）： 零售商依赖对购买模式的理解而发展。子查询可呈现嵌套的购买数据，以识别常被一起购买的商品，从而帮助策略性摆放互补商品并提升销量。

数学关联

子查询也常用于识别数学与逻辑关联中的数据模式与趋势。以下是一些在数学场景中应用子查询的示例。

• 时间序列分析的移动平均： 在分析时间趋势时，子查询能简化移动平均的计算。我会用嵌套查询定义特定时间窗口，更容易平滑数据并识别趋势。

• 用标准差检测异常值： 发现异常值对诸多任务都很重要，包括欺诈检测等。子查询能直接在嵌套查询中计算诸如标准差等指标。

• 运用集合论概念： 我觉得子查询与 UNION、INTERSECT 等集合运算的相似性很有趣。这一能力非常适合客户留存分析等任务，理解客户群体之间的重叠与差异有助于制定更聪明的营销策略。

结语

掌握 SQL 子查询能显著提升您高效管理与分析数据的能力。通过理解其结构、应用与最佳实践，您可以优化 SQL 查询以获得更佳性能。此外，我想强调，熟练掌握子查询能让 SQL 编写更顺手，值得投入精力学习。

如果您希望成为一名熟练的数据分析师，欢迎查看我们的 Associate Data Analyst in SQL 职业路径，学习必备技能。若您想学习如何用 SQL 构建专业仪表板，Reporting in SQL 课程同样适合。最后，我建议获取 SQL Associate Certification 认证，以证明您在使用 SQL 进行数据分析方面的能力，并在数据从业者中脱颖而出。