PostgreSQL 子查询（保姆级教程）

什么是 PostgreSQL 子查询？从“嵌套”说起

在日常的数据库操作中，我们常需要从多个表中获取数据，或者根据某些条件筛选出特定结果。这时候，如果只靠简单的 SELECT 语句，往往难以满足复杂需求。这时候，PostgreSQL 子查询 就成了我们解决问题的“瑞士军刀”。

想象一下：你正在整理一个图书管理系统，想找出“借阅次数超过平均值”的图书。你不能直接用一个查询完成，因为“平均值”本身就需要先计算。这时，你得先算出平均值，再拿它去对比每本书的借阅次数。这种“先做一件事，再用结果做另一件事”的模式，就是子查询的精髓。

在 PostgreSQL 中，子查询就是一条嵌套在另一条 SQL 语句内部的查询。它能作为表达式、条件、或表来使用。它的灵活性和强大性，让它在数据分析、报表生成、权限控制等场景中不可或缺。

💡 小贴士：子查询也叫“内查询”，外部的查询叫“外查询”。两者像俄罗斯套娃一样，一层套一层。

子查询的基本语法与执行顺序

PostgreSQL 子查询 的核心语法非常直观。你只需把一个 SELECT 语句放在另一个 SQL 语句的括号里，就可以实现嵌套。

SELECT column_name
FROM table_name
WHERE column_name operator (SELECT column_name FROM another_table WHERE condition);

这里的关键是理解执行顺序：子查询会先执行，然后将结果返回给外层查询使用。

举个例子，假设我们有两张表：books（图书表）和 borrow_records（借阅记录表）。我们想查出所有“借阅次数大于平均值”的图书。

-- 查询借阅次数大于平均值的图书
SELECT b.title, b.author, b.borrow_count
FROM books b
WHERE b.borrow_count > (
    -- 子查询：计算所有图书的平均借阅次数
    SELECT AVG(borrow_count)
    FROM books
);

📌 代码注释：

• 外层查询：从 books 表中选出 title、author 和 borrow_count 字段。
• WHERE 条件：borrow_count 必须大于子查询返回的值。
• 子查询部分：SELECT AVG(borrow_count) 计算所有图书的平均借阅次数。
• 注意括号 () 是必须的，表示这是一个子查询表达式。

这个例子中，子查询先执行，返回一个数值（比如 5.3），然后外层查询用这个值进行比较。整个过程就像“先算出平均分，再看谁高于平均”。

子查询的三种常见用法

PostgreSQL 子查询 有三种典型应用场景，掌握它们，你就能应对大多数复杂查询需求。

作为条件表达式（标量子查询）

这是最常见的一种用法。子查询返回一个单一值（比如 AVG、MAX、COUNT），用于与外层条件比较。

-- 找出借阅次数最高的图书
SELECT title, borrow_count
FROM books
WHERE borrow_count = (
    SELECT MAX(borrow_count)
    FROM books
);

📌 注释：

• 子查询 SELECT MAX(borrow_count) 返回最高的借阅次数。
• 外层查询筛选出 borrow_count 等于这个最大值的图书。
• 注意：如果有多本书借阅次数相同且都是最高，结果会返回多行。

作为列表（IN 子查询）

当子查询返回多个值时，可以用 IN 来判断某个值是否在结果集中。

-- 找出借阅记录中，作者是“张三”的所有图书
SELECT title, author
FROM books
WHERE author IN (
    -- 子查询：找出所有作者为“张三”的图书
    SELECT DISTINCT author
    FROM borrow_records
    WHERE author = '张三'
);

📌 注释：

• 子查询返回“张三”这个作者名（多个记录可能重复，用 DISTINCT 去重）。
• 外层查询用 IN 检查 books 表中的 author 是否在该列表中。
• 这种方式比 JOIN 更直观，适用于“从一组结果中筛选”的场景。

作为临时表（FROM 子查询）

子查询可以像表一样用在 FROM 子句中，这称为“派生表”（Derived Table）。

-- 计算每个作者的平均借阅次数，并筛选出平均值高于 5 的作者
SELECT author, avg_borrow
FROM (
    -- 子查询：按作者分组，计算平均借阅次数
    SELECT author, AVG(borrow_count) AS avg_borrow
    FROM books
    GROUP BY author
) AS author_stats
WHERE avg_borrow > 5;

📌 注释：

• 内层子查询对 books 表按 author 分组，计算每组的平均借阅次数。
• 外层查询对这个“临时表”（author_stats）进行筛选。
• 用 AS author_stats 给子查询起一个别名，就像给临时表命名。
• 这种方式适合复杂聚合分析，比如“按部门统计平均薪资”、“按城市统计订单数”。

子查询的性能优化与常见陷阱

虽然 PostgreSQL 子查询 功能强大，但用不好也可能导致性能下降。以下是几个关键点：

1. 避免重复执行子查询

PostgreSQL 会为每个外层行执行一次子查询（除非优化器能重写）。如果外层有 1000 行，子查询执行 1000 次，效率极低。

优化建议：尽量把重复计算的逻辑提取到 WITH 子句中（CTE），实现一次计算，多次复用。

-- 使用 CTE 优化：避免重复计算平均值
WITH avg_borrow AS (
    SELECT AVG(borrow_count) AS avg_count
    FROM books
)
SELECT b.title, b.borrow_count
FROM books b, avg_borrow ab
WHERE b.borrow_count > ab.avg_count;

📌 注释：

• WITH avg_borrow 定义了一个临时的“平均值”表。
• 外层查询从 books 和 avg_borrow 两个来源取数据。
• 无论 books 有多少行，avg_borrow 只计算一次，性能更优。

2. 注意子查询返回结果的类型

• 如果子查询返回多行，却用 = 比较，会报错。
• 如果子查询返回空值（NULL），比较结果是 UNKNOWN，可能导致意外结果。

-- 错误示例：子查询返回多行，用 = 比较
SELECT title
FROM books
WHERE borrow_count = (SELECT borrow_count FROM books WHERE author = '李四');
-- 如果“李四”有多本书，子查询返回多个值，会报错！

-- 正确做法：用 IN
SELECT title
FROM books
WHERE borrow_count IN (SELECT borrow_count FROM books WHERE author = '李四');

📌 注释：

• IN 可以接受多个值。
• = 只能用于标量（单个值）比较。

实际应用案例：用户行为分析

我们来做一个真实场景：分析电商平台用户的购买行为。

假设我们有两张表：

• users：用户信息（user_id, name, city）
• orders：订单信息（order_id, user_id, amount, order_date）

目标：找出“在 2024 年 1 月 1 日之后下单金额高于平均值”的用户。

-- 查询高价值用户：下单金额高于平均值
SELECT u.name, u.city, o.amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.amount > (
    -- 子查询：计算 2024 年 1 月 1 日后订单的平均金额
    SELECT AVG(amount)
    FROM orders
    WHERE order_date >= '2024-01-01'
)
AND o.order_date >= '2024-01-01'
ORDER BY o.amount DESC;

📌 注释：

• 外层查询连接 users 和 orders，获取用户信息和订单金额。
• WHERE 条件：金额大于“2024 年 1 月 1 日后”的平均金额。
• 子查询只计算特定时间范围的平均值，避免整体数据干扰。
• 最后按金额降序排列，结果清晰直观。

这个例子展示了 PostgreSQL 子查询 在真实业务场景中的价值：将复杂逻辑分解为可读的嵌套结构。

子查询的高级技巧：相关子查询

相关子查询是指子查询中引用了外层查询的列，因此它会对外层的每一行重新执行一次。

-- 找出每本书的借阅次数，与它所在作者的平均借阅次数比较
SELECT b1.title, b1.borrow_count,
       (SELECT AVG(b2.borrow_count)
        FROM books b2
        WHERE b2.author = b1.author) AS author_avg
FROM books b1;

📌 注释：

• 子查询中 b2.author = b1.author 依赖外层的 b1.author。
• 对于每一本书，都会计算它作者的平均借阅次数。
• 这种“逐行对比”的方式，常用于“相对排名”、“百分位分析”等场景。

⚠️ 警告：相关子查询性能较差，如果数据量大，建议改用 JOIN + GROUP BY 优化。

总结：PostgreSQL 子查询 的核心价值

通过本文，我们系统地学习了 PostgreSQL 子查询 的核心概念、语法、用法和实战技巧。

• 它让你能“先算出结果，再用结果做判断”，逻辑清晰。
• 它支持标量、列表、派生表三种模式，覆盖大部分复杂查询场景。
• 它是构建报表、分析、权限控制等系统的重要工具。

在数据驱动的时代，掌握 PostgreSQL 子查询，就像掌握了一把打开数据宝藏的钥匙。它不复杂，但非常实用。只要你愿意多写几行代码，就能让数据库真正“为你所用”。

别再只用 SELECT * FROM table 了。从今天起，试着用子查询，让 SQL 变得更有智慧。