SQL入门：深入解析子查询-从基础到高级应用

SQL子查询详解：从基础到高级应用

原创于 2025-10-15 21:23:04 发布 · 1k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #etl工程师 #sql #数据库开发 #数据仓库 #etl #大数据

SQL基础专栏收录该内容

31 篇文章

订阅专栏

子查询（Subquery）是 SQL 中嵌套在其他 SQL 语句（如SELECT、INSERT、UPDATE、DELETE）中的查询，核心作用是 “用一个查询的结果作为另一个查询的条件或数据源”，实现复杂数据筛选、统计与关联。标准 SQL 支持多层子查询，按用途可分为标量子查询、列子查询、行子查询、表子查询四类，广泛用于数据分析、报表生成、数据清洗等场景。本文从基础概念到高级应用，全面解析子查询的用法、规则及最佳实践。

一、子查询的核心概念与基本语法

1. 定义与本质

子查询是 “嵌套在主查询中的辅助查询”，本质是 “先执行子查询获取结果，再将结果传递给主查询参与计算”。例如：“找出订单金额超过平均订单金额的订单”，需先通过子查询计算 “平均订单金额”，再作为主查询的筛选条件。

2. 基本语法结构

子查询可嵌套在主查询的SELECT、FROM、WHERE、HAVING等子句中，基本结构如下：

-- 主查询
SELECT 主查询字段
FROM 主查询表
WHERE 主查询条件 [子查询结果]; -- 子查询嵌套在WHERE中

示例：基础子查询（筛选高于平均金额的订单）

-- 主查询：筛选金额>平均金额的订单
SELECT order_id, amount
FROM orders
-- 子查询：计算所有订单的平均金额（作为筛选条件）
WHERE amount > (SELECT AVG(amount) FROM orders);

执行顺序：先执行子查询(SELECT AVG(amount) FROM orders)得到平均金额，再执行主查询筛选金额高于该值的订单。

3. 关键规则

执行顺序：子查询优先于主查询执行，主查询依赖子查询的结果；
括号包裹：子查询必须用()包裹，明确区分主查询与子查询；
单行 / 多行兼容：子查询返回结果的行数需与主查询的使用场景匹配（如WHERE后用单行结果，FROM后用多行结果）；
字段匹配：子查询返回的字段类型需与主查询的使用场景兼容（如数值型字段用于数值比较，字符串型用于模糊匹配）。

二、子查询的四大类型（按返回结果分类）

根据子查询返回结果的形态（单行单列、多行单列、单行多列、多行多列），标准 SQL 将其分为四类，适用场景差异显著：

1. 标量子查询：返回 “单行单列” 结果

标量子查询是返回单个值（1 行 1 列）的子查询，常用于WHERE子句的比较条件（如=、>、<）或SELECT子句的字段值。

语法示例（嵌套在 WHERE 中）

-- 需求：找出“2024年下单次数最多的用户”的所有订单
SELECT order_id, user_id
FROM orders
WHERE user_id = (
  -- 子查询：返回2024年下单次数最多的用户ID（单行单列）
  SELECT user_id
  FROM orders
  WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'
  GROUP BY user_id
  ORDER BY COUNT(order_id) DESC
  LIMIT 1 -- 确保返回单行
);

语法示例（嵌套在 SELECT 中）

-- 需求：查询每个订单的金额及“该金额占总销售额的百分比”
SELECT 
  order_id,
  amount,
  -- 子查询：返回总销售额（单行单列），用于计算百分比
  ROUND(amount / (SELECT SUM(amount) FROM orders) * 100, 2) AS sales_ratio
FROM orders;

关键注意：

标量子查询必须返回唯一值（若返回多行，会报 “单行子查询返回多行” 错误），需用LIMIT 1或聚合函数（如AVG、MAX）确保单行。

2. 列子查询：返回 “多行单列” 结果

列子查询是返回多个值（多行 1 列）的子查询，常用于WHERE子句的IN、NOT IN、ANY、ALL等条件中，筛选 “主查询字段值在子查询结果集中” 的记录。

语法示例 1：配合 IN 使用（筛选特定用户的订单）

-- 需求：找出“VIP用户”的所有订单
SELECT order_id, user_id, amount
FROM orders
WHERE user_id IN (
  -- 子查询：返回所有VIP用户的ID（多行单列）
  SELECT user_id
  FROM users
  WHERE user_level = 'VIP'
);

语法示例 2：配合 ANY 使用（筛选金额大于 “任意一个目标金额” 的订单）

-- 需求：找出“金额大于任意一个促销商品单价”的订单
SELECT order_id, amount
FROM orders
WHERE amount > ANY (
  -- 子查询：返回所有促销商品的单价（多行单列）
  SELECT price
  FROM products
  WHERE is_promotion = 1
);

ANY逻辑：主查询字段值 “大于 / 小于 / 等于” 子查询结果中的任意一个值即满足条件；
类似的ALL：主查询字段值需 “大于 / 小于 / 等于” 子查询结果中的所有值（如amount > ALL (...)表示金额大于所有促销商品单价）。

关键注意：

NOT IN需谨慎使用：若子查询结果包含NULL，NOT IN会返回空结果（因NULL与任何值比较都为UNKNOWN），建议用NOT EXISTS替代。

3. 行子查询：返回 “单行多列” 结果

行子查询是返回单个记录的多个字段（1 行多列）的子查询，常用于WHERE子句的行比较（如匹配 “用户 ID + 订单月份” 组合），适用于 “多字段联合筛选” 场景。

语法示例：匹配 “用户 ID + 月份” 组合

-- 需求：找出“用户101在2024年5月”的订单
SELECT order_id, user_id, create_time
FROM orders
WHERE (user_id, DATE_FORMAT(create_time, '%Y-%m')) = (
  -- 子查询：返回目标“用户ID+月份”（单行多列）
  SELECT 101, '2024-05'
  FROM DUAL -- DUAL是虚拟表，用于无实际表时返回固定值（MySQL/Oracle支持）
);

逻辑：主查询的(user_id, 月份)组合与子查询返回的(101, '2024-05')组合完全匹配时，筛选该订单。

关键注意：

行子查询的 “列数” 需与主查询比较的 “字段数” 完全一致（如上例均为 2 列），且对应字段类型兼容。

4. 表子查询：返回 “多行多列” 结果

表子查询是返回多个记录的多个字段（多行多列）的子查询，本质是 “将子查询结果作为一张临时表”，嵌套在主查询的FROM子句中，用于复杂关联或统计。表子查询也称为 “派生表”（Derived Table），需为其指定别名（否则报错）。

语法示例 1：子查询作为临时表关联

-- 需求：查询“每个用户的最新订单”详情
SELECT o.order_id, o.user_id, o.amount
FROM orders o
-- 子查询：返回每个用户的最新订单ID（多行多列，作为临时表）
JOIN (
  SELECT 
    user_id, 
    MAX(create_time) AS latest_time -- 每个用户的最新下单时间
  FROM orders
  GROUP BY user_id
) AS user_latest ON o.user_id = user_latest.user_id 
                 AND o.create_time = user_latest.latest_time;

语法示例 2：子查询作为统计数据源

-- 需求：统计“各用户的订单数和总消费”，并筛选总消费>1000的用户
SELECT 
  user_id,
  order_count,
  total_amount
FROM (
  -- 子查询：先计算每个用户的订单数和总消费（多行多列）
  SELECT 
    user_id,
    COUNT(order_id) AS order_count,
    SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
) AS user_stats -- 表子查询必须指定别名
WHERE total_amount > 1000; -- 主查询筛选总消费>1000的用户

关键注意：

表子查询必须指定别名（如AS user_stats），否则数据库无法识别临时表；
临时表的字段名需唯一，避免与主查询表的字段名冲突。

三、子查询的嵌套层级：单层与多层

标准 SQL 支持子查询的多层嵌套（理论上无层数限制），即 “子查询中再嵌套子查询”，用于处理更复杂的逻辑。但多层嵌套会降低代码可读性和执行效率，建议嵌套不超过 3 层，复杂场景优先用WITH子句（CTE）拆分。

示例：双层嵌套（统计 “VIP 用户的平均订单金额”）

-- 需求：计算“VIP用户”的平均订单金额
SELECT AVG(amount) AS vip_avg_amount
FROM orders
WHERE user_id IN (
  -- 第一层子查询：返回VIP用户ID
  SELECT user_id
  FROM users
  WHERE user_level = 'VIP'
    AND register_time > (
      -- 第二层子查询：返回“2024年1月1日”（筛选2024年后注册的VIP用户）
      SELECT '2024-01-01' FROM DUAL
    )
);

执行顺序：先执行最内层子查询（获取'2024-01-01'）→ 再执行第一层子查询（筛选 2024 年后注册的 VIP 用户 ID）→ 最后执行主查询（计算这些用户的平均订单金额）。

四、子查询与`EXISTS`：判断存在性

EXISTS是用于判断 “子查询是否返回结果” 的关键字，常与子查询配合使用，核心逻辑是 “若子查询返回至少一行记录，则EXISTS为TRUE，否则为FALSE”。EXISTS不关心子查询返回的具体值，仅关心 “是否存在符合条件的记录”，效率通常高于IN（尤其大数据量场景）。

语法示例 1：`EXISTS`替代`IN`（筛选 VIP 用户的订单）

-- 需求：找出“VIP用户”的所有订单（用EXISTS替代IN）
SELECT order_id, user_id, amount
FROM orders o
WHERE EXISTS (
  -- 子查询：判断当前订单的用户是否为VIP（返回至少一行则为TRUE）
  SELECT 1 -- 子查询返回任意值均可，通常用1简化
  FROM users u
  WHERE u.user_id = o.user_id -- 关联主查询表（ correlated subquery，关联子查询）
    AND u.user_level = 'VIP'
);

关联子查询：子查询中引用了主查询的字段（如o.user_id），会逐行匹配主查询记录，判断子查询是否存在对应结果。

语法示例 2：`NOT EXISTS`替代`NOT IN`（筛选未下单的用户）

-- 需求：找出“未下单的注册用户”（避免NOT IN的NULL问题）
SELECT user_id, username
FROM users u
WHERE NOT EXISTS (
  -- 子查询：判断用户是否有订单（无订单则NOT EXISTS为TRUE）
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.user_id
);

`EXISTS`的优势：

不依赖子查询的具体结果，仅判断存在性，效率更高；
避免IN/NOT IN的NULL问题（NOT EXISTS在子查询含NULL时仍正常返回结果）。

五、子查询的常见误区与避坑指南

1. 误区 1：标量子查询返回多行

问题：标量子查询未限制返回行数，导致主查询报错（如 “单行子查询返回多行”）。

-- 错误示例：子查询返回多个用户ID（多行），无法用=匹配
SELECT order_id
FROM orders
WHERE user_id = (SELECT user_id FROM users WHERE user_level = 'VIP'); -- 报错

解决：确保标量子查询返回单行，用LIMIT 1或聚合函数（如MAX）：

SELECT order_id
FROM orders
WHERE user_id = (SELECT MAX(user_id) FROM users WHERE user_level = 'VIP'); -- 正确（单行）

2. 误区 2：`NOT IN`子查询包含`NULL`

问题：子查询结果含NULL，NOT IN返回空结果（因NULL与任何值比较都为UNKNOWN）。

-- 错误示例：子查询中的user_id含NULL，NOT IN返回空
SELECT user_id
FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders); -- 若orders.user_id有NULL，结果为空

解决：用NOT EXISTS替代NOT IN，或先过滤子查询的NULL：

-- 方案1：用NOT EXISTS（推荐）
SELECT u.user_id
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

-- 方案2：过滤子查询的NULL
SELECT user_id
FROM users
WHERE user_id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL);

3. 误区 3：表子查询未指定别名

问题：表子查询（派生表）未指定别名，数据库无法识别，导致语法错误。

-- 错误示例：表子查询无别名
SELECT user_id, order_count
FROM (SELECT user_id, COUNT(order_id) AS order_count FROM orders GROUP BY user_id); -- 报错

解决：为表子查询指定别名（如AS user_stats）：

SELECT user_id, order_count
FROM (SELECT user_id, COUNT(order_id) AS order_count FROM orders GROUP BY user_id) AS user_stats; -- 正确

4. 误区 4：多层嵌套导致性能低下

问题：子查询嵌套超过 3 层，代码可读性差，且数据库优化器难以生成高效执行计划，导致查询缓慢。

解决：用WITH子句（CTE）拆分多层嵌套，将复杂逻辑拆分为多个临时结果集，提升可读性和效率：

-- 优化前：双层嵌套
SELECT o.order_id
FROM orders o
WHERE o.user_id IN (
  SELECT u.user_id
  FROM users u
  WHERE u.register_time > (SELECT '2024-01-01' FROM DUAL)
);

-- 优化后：用WITH拆分
WITH valid_users AS (
  SELECT user_id FROM users WHERE register_time > '2024-01-01'
)
SELECT order_id FROM orders WHERE user_id IN (SELECT user_id FROM valid_users);

六、子查询的性能优化技巧

优先用EXISTS替代IN（大数据量场景）IN需将子查询结果全部加载到内存，再与主查询匹配；EXISTS仅判断存在性，找到匹配记录后立即终止子查询，效率更高（尤其子查询结果量大时）。
避免在子查询中使用DISTINCT和ORDER BY子查询的DISTINCT（去重）和ORDER BY（排序）会增加额外计算，若无需去重或排序，应删除这些关键字（如IN子查询无需排序，EXISTS子查询无需去重）。
为子查询的关联字段建立索引关联子查询（如EXISTS中的o.user_id = u.user_id）依赖主查询与子查询的字段关联，为这些字段（如orders.user_id、users.user_id）建立索引，可显著提升匹配效率。
用WITH子句拆分复杂子查询WITH子句（CTE）会将临时结果集缓存，避免重复计算（如多层嵌套中多次使用同一子查询结果），同时提升代码可读性。
避免在子查询中使用聚合函数嵌套聚合函数嵌套（如SUM(AVG(amount))）会强制数据库多次扫描数据，应拆分为多个步骤（如先计算AVG，再计算SUM）。

七、实战场景：子查询的综合应用

场景 1：找出 “连续 3 天下单的用户”

需求：识别 “在 2024 年中至少连续 3 天有下单记录” 的用户 ID。

WITH user_daily_orders AS (
  -- 步骤1：子查询1：获取每个用户的每日下单日期（去重，避免同一用户一天多单）
  SELECT DISTINCT user_id, DATE(create_time) AS order_date
  FROM orders
  WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31'
),
user_date_diff AS (
  -- 步骤2：子查询2：计算每个用户相邻下单日期的差值
  SELECT 
    user_id,
    order_date,
    -- 窗口函数：获取用户的上一次下单日期
    LAG(order_date, 1) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_date
  FROM user_daily_orders
),
user_continuous_days AS (
  -- 步骤3：子查询3：标记连续日期（差值=1为连续）
  SELECT 
    user_id,
    order_date,
    CASE WHEN DATEDIFF(order_date, prev_date) = 1 THEN 1 ELSE 0 END AS is_continuous
  FROM user_date_diff
)
-- 主查询：筛选连续3天及以上的用户
SELECT DISTINCT user_id
FROM (
  -- 子查询4：计算每个用户的连续天数累计
  SELECT 
    user_id,
    SUM(is_continuous) OVER (PARTITION BY user_id ORDER BY order_date) AS continuous_count
  FROM user_continuous_days
) AS t
WHERE continuous_count >= 2; -- 连续3天需累计2次“连续”（如Day1-Day2:1次，Day2-Day3:2次）

场景 2：更新 “用户的最新订单时间”

需求：将users表的latest_order_time字段更新为该用户的最新下单时间（无下单记录则设为NULL）。

UPDATE users u
SET latest_order_time = (
  -- 子查询：返回用户的最新下单时间（无订单则返回NULL）
  SELECT MAX(create_time)
  FROM orders o
  WHERE o.user_id = u.user_id -- 关联子查询，逐行更新
);

场景 3：删除 “重复的订单记录”（保留最新一条）

需求：删除orders表中 “用户 ID + 商品 ID” 相同的重复订单，仅保留最新创建的一条。

DELETE FROM orders
WHERE (user_id, product_id, create_time) NOT IN (
  -- 子查询：返回每个“用户ID+商品ID”组合的最新订单记录（多行多列）
  SELECT user_id, product_id, MAX(create_time) AS latest_time
  FROM orders
  GROUP BY user_id, product_id
);

八、总结

子查询是 SQL 实现复杂逻辑的核心工具，其核心要点可归纳为：

子查询类型	返回结果形态	常用场景	关键关键字 / 操作
标量子查询	单行单列	WHERE 比较、SELECT 字段值	=、>、<、AVG、MAX
列子查询	多行单列	WHERE 筛选（是否在集合中）	IN、NOT IN、ANY、ALL
行子查询	单行多列	WHERE 多字段联合匹配	=（行比较）
表子查询	多行多列	FROM 临时表、JOIN 关联	派生表别名、JOIN