第2课：使用 JOIN、子查询和 CTE 进行高级查询

首先完成“第 1 课：PostgreSQL 基础与关系型设计”即可解锁本课程。转到上一课

介绍

在实际后端项目中使用 PostgreSQL 时，你很少只查询单张表。数据通常会被规范化，通过外键关联到多张表，你需要将它们组合起来才能返回有意义的信息。本课重点介绍三种帮助你清晰、高效编写复杂查询的重要技术：用于连接表的 JOIN、用于嵌套查询的 subquery，以及用于结构化查询逻辑的 CTE。

我们将使用一个模拟销售系统的示例 schema，包含四张主表：customers、orders、products 和 order_items。customers 表存储客户信息，orders 记录客户下的订单，products 包含产品目录，order_items 是中间表，存储每个订单中的产品明细。这是你会经常遇到的嵌套一对多关系。

跨多表的 JOIN

JOIN 是在同一个查询中合并多表数据的最常用方式。PostgreSQL 支持 INNER JOIN（仅返回两张表中都匹配的记录）、LEFT JOIN（返回左表所有记录，即使右表没有匹配），以及其他变体如 RIGHT JOIN、FULL OUTER JOIN。在实际后端开发中，你最常用的是 INNER JOIN 和 LEFT JOIN。

下面的示例查询订单列表及其对应的客户姓名和总金额：

sql

SELECT o.id AS order_id, c.name AS customer_name, o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 10;

当需要同时连接多张表时，只需依次追加 JOIN 子句。例如，查询每个订单中各产品的明细，包括产品名称和数量：

sql

SELECT o.id AS order_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE o.customer_id = 42;

一个常见错误是 join 表时忘记写 ON 条件，从而产生笛卡尔积，导致查询运行非常慢。务必检查每个 JOIN 是否都有明确的主键匹配条件。

customers、orders、order_items、products 四张表之间的关系示意图，以及外键连线

子查询与关联子查询

子查询（Subquery）是嵌套在另一个查询中的 SELECT 查询。你可以在 WHERE 子句中使用子查询来过滤数据，在 SELECT 中使用它来计算值，或者在 FROM 中使用它来创建临时表。一种特殊形式是关联子查询（correlated subquery），其中子查询引用了外部查询的列，这意味着它会针对结果集中的每一行重新执行。

示例：使用简单子查询查找至少有一个订单的客户：

sql

SELECT name, email
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total_amount > 500);

示例：关联子查询为列表中显示的每个客户统计订单数量：

sql

SELECT c.name, c.email,
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c
WHERE c.created_at > '2024-01-01';

由于子查询引用了外部的 c.id，PostgreSQL 必须为每个客户重新执行它。对于大表来说，关联子查询可能会较慢，因此如果性能是问题，请考虑改用 JOIN 或 CTE。

使用 WITH 的公共表表达式

CTE（Common Table Expression，公共表表达式）允许你在同一条语句中定义命名的临时查询，使复杂的逻辑更易于阅读。CTE 以关键字 WITH 开头，后跟命名的查询列表，然后是使用这些查询的主查询。

示例：计算 2024 年每位客户的总收入，然后筛选出收入超过 1000 的客户：

sql

WITH customer_revenue AS (
  SELECT o.customer_id, SUM(o.total_amount) AS revenue
  FROM orders o
  WHERE o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'
  GROUP BY o.customer_id
)
SELECT c.name, cr.revenue
FROM customer_revenue cr
INNER JOIN customers c ON c.id = cr.customer_id
WHERE cr.revenue > 1000
ORDER BY cr.revenue DESC;

CTE 还支持递归形式（recursive CTE），使用关键字 WITH RECURSIVE，对于遍历层次结构（如公司组织架构或多级产品分类）非常有用。此外，PostgreSQL 12 及以上版本允许使用 WITH ... AS MATERIALIZED 进行物化（materialized），在某些情况下可以提升查询速度。

常见错误与实战技巧

一个常见的错误是在使用 IN 或 EXISTS 的 WHERE 子句中混淆 JOIN 与子查询。在许多场景下两者能得到等价的结果，但当你只需要检查存在性而非获取具体值时，EXISTS 通常更快。示例：查找曾经下过订单的客户：

sql

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

编写 CTE 时，请使用有意义的名称，如 customer_revenue 或 top_products，以便读者轻松理解每一步的目的。避免在简单 JOIN 足够用时滥用 CTE，因为在某些 PostgreSQL 版本中，CTE 可能会带来额外的优化开销。最后，当复杂查询运行缓慢时，请始终使用 EXPLAIN ANALYZE 检查执行计划，因为有时 correlated subquery 可以替换为 JOIN，从而显著提升速度。

总结

在本课中，你学习了如何使用 JOIN 组合多个表、如何编写 subquery 和 correlated subquery 进行筛选与计算，以及如何使用带 WITH 的 CTE 将复杂查询结构化为易于阅读的代码块。这三种技术构成了你在实际后端应用中处理大多数查询需求的基础——从获取带详情的订单列表到计算汇总报表。在下一课中，你将进一步了解 indexing 以及如何为你刚刚编写的复杂查询优化性能。