异常教程

PostgreSQL 子查询（千字长文）

更新时间:

💡一则或许对你有用的小广告

欢迎加入小哈的星球 ，你将获得：专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17...，点击查看项目介绍 ;
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2 期已完结，演示链接： http://116.62.199.48/ ;

截止目前， 星球 内专栏累计输出 82w+ 字，讲解图 3441+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，权限管理，Spring Cloud Alibaba 微服务等等，已有 2900+ 小伙伴加入学习 ，欢迎点击围观

前言：为什么需要理解子查询？

在数据库查询中，子查询（Subquery）如同编程中的嵌套函数，它允许我们将一个查询的结果作为另一个查询的输入。对于 PostgreSQL 开发者而言，掌握子查询的使用技巧，不仅能简化复杂查询的编写流程，还能显著提升数据处理的灵活性与效率。无论是筛选特定条件的记录、动态生成查询参数，还是实现多表关联的高级操作，子查询都是不可或缺的工具。

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

1.1 子查询的定义与比喻

子查询是嵌套在另一个查询中的独立查询语句，其结果会被外层查询直接引用。我们可以将其想象为“快递包裹中的包裹”：外层查询是快递箱，而子查询则是箱内封装的物品。例如，假设我们要查询“订单金额高于平均值的订单”，平均值的计算过程就可以通过子查询完成。

基本语法结构：

SELECT column(s)
FROM table
WHERE condition
    [OPERATOR] (子查询);

1.2 子查询的执行顺序

PostgreSQL 会先执行子查询（内层查询），再将结果传递给外层查询。这种“先内后外”的执行顺序，类似于数学中的括号运算规则。例如：

SELECT * FROM orders
WHERE order_total > (SELECT AVG(order_total) FROM orders);

这里，内层子查询 (SELECT AVG(order_total) FROM orders) 先计算平均值，外层查询再筛选出高于该值的订单。

二、子查询的分类与应用场景

2.1 标量子查询（Scalar Subquery）

当子查询返回单个值时，称为标量子查询。它常用于单值比较，例如：

-- 查找销售额最高的员工信息
SELECT employee_id, name, total_sales
FROM employees
WHERE total_sales = (
    SELECT MAX(total_sales) 
    FROM employees
);

形象比喻：标量子查询如同从快递包裹中取出一个物品，外层查询直接使用该物品的值。

2.2 行子查询（Row Subquery）

当子查询返回一行多列数据时，可以用 IN、= ALL 等操作符进行比较。例如：

-- 查询与某个部门经理同薪资级别的员工
SELECT * FROM employees
WHERE (department_id, salary) = (
    SELECT department_id, salary 
    FROM employees 
    WHERE role = 'Manager'
);

2.3 表子查询（Table Subquery）

子查询返回多行多列数据时，可以作为临时表供外层查询使用。这种场景常与 FROM 子句结合：

-- 计算各部门的平均销售额与总销售额对比
SELECT department, AVG(sales) AS avg_dept_sales, total_company_sales
FROM (
    SELECT department, sales,
        (SELECT SUM(sales) FROM sales_data) AS total_company_sales
    FROM sales_data
) AS subquery_table
GROUP BY department, total_company_sales;

2.4 相关子查询（Correlated Subquery）

当子查询依赖外层查询的值时，称为相关子查询。例如：

-- 查询每个员工的入职时间是否早于其部门成立时间
SELECT e.name, e.hire_date, d.founded_date
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE e.hire_date < (
    SELECT founded_date 
    FROM departments 
    WHERE id = e.department_id
);

关键点：内层子查询的 WHERE 条件引用了外层查询的字段（e.department_id），因此每次外层迭代都会重新执行子查询。

三、子查询的进阶技巧与性能优化

3.1 子查询 vs 连接查询（JOIN）

在某些场景下，子查询和 JOIN 可以互换，但性能可能不同。例如：

-- 子查询写法
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers 
    WHERE region = 'North'
);

-- 等效的 JOIN 写法
SELECT o.* 
FROM orders o
INNER JOIN customers c 
    ON o.customer_id = c.id 
    AND c.region = 'North';

选择建议：对于小数据量或简单查询，子查询更直观；大数据量时，JOIN 可能更高效。

3.2 子查询的性能优化

3.2.1 避免重复计算

-- 低效写法（每次循环都计算平均值）
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products)
AND stock > (SELECT AVG(stock) FROM products);

-- 改进写法（将子查询结果缓存）
WITH avg_values AS (
    SELECT AVG(price) AS avg_price, AVG(stock) AS avg_stock
    FROM products
)
SELECT p.* 
FROM products p, avg_values av
WHERE p.price > av.avg_price 
AND p.stock > av.avg_stock;

3.2.2 使用 EXISTS 替代 IN

当子查询可能返回大量数据时，EXISTS 通常比 IN 更快：

-- IN 写法
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT id FROM customers 
    WHERE active = TRUE
);

-- EXISTS 写法（推荐）
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 
    FROM customers c 
    WHERE c.id = o.customer_id 
    AND c.active = TRUE
);

四、子查询的典型应用场景

4.1 动态阈值计算

-- 查询销售额超过部门平均值的员工
SELECT e.name, e.sales 
FROM employees e
WHERE e.sales > (
    SELECT AVG(e2.sales) 
    FROM employees e2 
    WHERE e2.department = e.department
);

4.2 数据去重与过滤

-- 获取订单号不重复的客户列表
SELECT DISTINCT customer_id 
FROM orders 
WHERE order_date > (
    SELECT MAX(report_date) 
    FROM audit_logs
);

4.3 跨表复杂条件查询

-- 查询未完成且创建时间超过30天的订单
SELECT * FROM orders 
WHERE status = 'Pending' 
AND created_at < NOW() - INTERVAL '30 days'
AND customer_id NOT IN (
    SELECT id FROM customers 
    WHERE status = 'Inactive'
);

五、常见问题与解决方案

5.1 子查询返回多行时的错误

当标量子查询返回多行数据，会触发 more than one row returned by a subquery used as an expression 错误。解决方案包括：

  • 使用聚合函数（如 MAX()）限定为单值
  • 改用 IN 或 ANY 等多值操作符

5.2 相关子查询的性能瓶颈

若相关子查询执行效率低下，可通过以下方式优化：

  1. 确保关联字段有索引
  2. 将子查询结果缓存到临时表
  3. 使用窗口函数替代

结论：善用子查询提升数据库开发效率

PostgreSQL 子查询是构建复杂查询的利器，其灵活的嵌套能力能显著减少代码冗余，提升逻辑清晰度。通过本文的案例与技巧，开发者可以：

  • 理解子查询的分类与执行逻辑
  • 掌握标量、行、表、相关子查询的适用场景
  • 优化子查询性能，避免常见陷阱

在实际开发中，建议根据数据量与业务需求，合理选择子查询或 JOIN 的实现方式。随着对 PostgreSQL 子查询的深入掌握，开发者将能更高效地应对各类复杂数据处理挑战。

最新发布

查看更多文章