CoderPath

子查询：WHERE、FROM 与 SELECT 中的子查询 ​

什么是子查询 ​

子查询（Subquery）是嵌套在另一个查询中的 SELECT 语句。

基本形式：

sql
SELECT column
FROM table
WHERE column = (SELECT ...);

子查询用括号包裹，可以出现在 WHERE、FROM、SELECT、HAVING 等位置。

WHERE 中的标量子查询 ​

标量子查询返回单个值（一行一列）。

示例 1：查询高于平均金额的订单 ​

sql
SELECT product, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

结果：

text
 product | amount
---------+---------
 Laptop  | 1200.00
 Monitor |  300.00

工作原理：

  1. 子查询计算平均金额：(SELECT AVG(amount) FROM orders) → 331
  2. 主查询使用这个值：WHERE amount > 331

示例 2：查询特定用户的所有订单 ​

sql
SELECT product, amount
FROM orders
WHERE user_id = (SELECT id FROM users WHERE name = 'Alice');

结果：

text
  product  | amount
-----------+---------
 Laptop    | 1200.00
 Keyboard  |   80.00

标量子查询的要求 ​

子查询必须返回单个值，否则报错：

sql
-- 错误：子查询返回多行
SELECT * FROM orders
WHERE user_id = (SELECT id FROM users WHERE name LIKE 'A%');

错误信息：

text
ERROR:  more than one row returned by a subquery used as an expression

解决方法：使用 IN（下一节）或限制子查询结果。

WHERE 中的 IN 子查询 ​

IN 子查询可以返回多行（一列）。

sql
-- 查询名字以 'A' 开头的用户的所有订单
SELECT product, amount
FROM orders
WHERE user_id IN (SELECT id FROM users WHERE name LIKE 'A%');

结果：

text
  product  | amount
-----------+---------
 Laptop    | 1200.00
 Keyboard  |   80.00

等价于 JOIN：

sql
SELECT o.product, o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.name LIKE 'A%';

何时用子查询，何时用 JOIN？

  • 子查询：逻辑更清晰，适合简单过滤
  • JOIN：性能通常更好，适合需要多表列的情况

FROM 中的子查询（派生表） ​

子查询可以作为临时表使用：

sql
SELECT customer, total
FROM (
    SELECT u.name AS customer, SUM(o.amount) AS total
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    GROUP BY u.name
) AS user_totals
WHERE total > 100;

结果：

text
 customer |  total
----------+---------
 Alice    | 1280.00
 Charlie  |  300.00

说明：

  • 内部子查询生成临时表 user_totals
  • 外部查询从这个临时表中筛选

必须给派生表起别名（AS user_totals），否则报错。

派生表的用途 ​

解决"不能在 WHERE 中使用聚合函数"的问题：

sql
-- 错误：WHERE 不能用 HAVING
SELECT u.name, SUM(o.amount) AS total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name
WHERE SUM(o.amount) > 100;  -- 错误！

正确写法（用派生表）：

sql
SELECT customer, total
FROM (
    SELECT u.name AS customer, SUM(o.amount) AS total
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    GROUP BY u.name
) AS t
WHERE total > 100;

或者用 HAVING（更简洁）：

sql
SELECT u.name, SUM(o.amount) AS total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name
HAVING SUM(o.amount) > 100;

SELECT 中的子查询 ​

可以在 SELECT 列表中使用子查询：

sql
SELECT 
    name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count,
    (SELECT SUM(amount) FROM orders WHERE user_id = users.id) AS total_spent
FROM users;

结果：

text
  name   | order_count | total_spent
---------+-------------+-------------
 Alice   |           2 |     1280.00
 Bob     |           2 |       75.00
 Charlie |           1 |      300.00
 Diana   |           0 |

这是相关子查询（Correlated Subquery）：子查询引用了外部查询的列（users.id）。

等价于 LEFT JOIN + GROUP BY：

sql
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

JOIN 版本通常性能更好。

相关子查询 vs 非相关子查询 ​

非相关子查询 ​

子查询独立执行，不依赖外部查询：

sql
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

执行过程：

  1. 执行子查询一次：SELECT AVG(amount) FROM orders → 331
  2. 用结果执行主查询

相关子查询 ​

子查询依赖外部查询，对每行重复执行：

sql
SELECT name,
    (SELECT COUNT(*) FROM orders WHERE user_id = users.id)
FROM users;

执行过程：

  1. 对 users 的每一行
  2. 执行子查询，传入当前行的 users.id
  3. 返回结果

性能影响：相关子查询可能很慢（对每行重复执行）。

子查询与比较运算符 ​

可以用 >, <, >=, <= 与子查询结合：

sql
-- 查询金额大于所有 Bob 订单的订单
SELECT product, amount
FROM orders
WHERE amount > ALL (SELECT amount FROM orders WHERE user_id = 2);

ALL：大于所有值（即大于最大值）

sql
-- 查询金额大于任意一个 Bob 订单的订单
SELECT product, amount
FROM orders
WHERE amount > ANY (SELECT amount FROM orders WHERE user_id = 2);

ANY：大于任意值（即大于最小值）

等价写法：

sql
-- ALL
WHERE amount > (SELECT MAX(amount) FROM orders WHERE user_id = 2)

-- ANY
WHERE amount > (SELECT MIN(amount) FROM orders WHERE user_id = 2)

嵌套子查询 ​

子查询可以嵌套多层：

sql
SELECT name
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE amount > (
        SELECT AVG(amount)
        FROM orders
    )
);

建议：避免过深嵌套（影响可读性和性能），考虑用 JOIN 或 CTE（后续章节）。

子查询的常见用途 ​

1. 动态过滤条件 ​

sql
-- 查询销售额前 20% 的产品
SELECT product
FROM orders
WHERE amount >= (SELECT PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY amount) FROM orders);

2. 复杂聚合 ​

sql
-- 每个用户消费占总消费的百分比
SELECT 
    u.name,
    SUM(o.amount) AS spent,
    ROUND(SUM(o.amount) / (SELECT SUM(amount) FROM orders) * 100, 2) AS percentage
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

3. 数据完整性检查 ​

sql
-- 查询没有任何订单的用户
SELECT name
FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

或用 NOT EXISTS（更高效）：

sql
SELECT name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

子查询 vs JOIN ​

特性子查询JOIN
可读性逻辑清晰需要理解连接
性能相关子查询可能慢通常更快
功能可嵌套，灵活受限于表关系
优化取决于数据库优化器擅长

经验法则：

  • 简单过滤：子查询
  • 需要多表列：JOIN
  • 复杂逻辑：根据可读性选择

练习 ​

  1. 标量子查询：查询金额高于平均值的订单，显示产品和金额。

  2. IN 子查询：查询在 2026-01-17 之后下过单的用户名。

  3. 派生表：先计算每个用户的订单数，再查询订单数 >= 2 的用户。

  4. SELECT 子查询：查询每个用户的姓名和订单数（使用 SELECT 中的子查询）。

  5. 相关子查询：查询每个用户最贵的订单产品。

  6. NOT IN：查询没有下过单的用户。

  7. ANY/ALL：查询金额大于所有 Electronics 类别订单的订单。

  8. 嵌套子查询：查询购买了最贵产品的用户名。

完成练习后，打开本章答案页核对。

下一步 ​

学会了子查询。下一节学习 EXISTS 和 IN 的深入对比，以及如何高效检查存在性。

回到第三章概览 · 返回SQL 学习路线

用心记录每一步成长

Copyright © 2026-present Twiink