PostgreSQL: PostgreSQL子查询与CTE

Q: CTE 和子查询性能有区别吗？

PostgreSQL 12+ 会自动决定 CTE 是否内联。简单 CTE 通常被内联，复杂 CTE 可能物化。可用 MATERIALIZED / NOT MATERIALIZED 手动控制。

Q: 递归 CTE 会不会无限循环？

可能。如果数据有环（如 A→B→A），递归不会停止。防范方法：加 level 限制，或追踪 path 避免重复访问。

Q: NOT IN 遇到 NULL 为什么返回空？

x NOT IN (a, NULL) 等价于 x<>a AND x<>NULL ， x<>NULL 为 UNKNOWN，AND 链中 UNKNOWN 使整行为 FALSE。用 NOT EXISTS 替代更安全。

最后更新：2026-08-26

1. 你将学到

标量子查询 / 列子查询 / 行子查询 / 表子查询
EXISTS / NOT EXISTS
ANY / ALL 操作符
子查询在 FROM / WHERE / SELECT 中的位置
CTE（WITH 子句，PostgreSQL 特色）
递归 CTE（WITH RECURSIVE 树形查询）
CTE vs 子查询 vs 临时表对比

2. 故事

Alice 是一家 SaaS 公司的 HR 系统开发者。公司有 500 多人，组织架构是树形的——CEO 在顶层，下面是 VP，VP 下面是 Director，Director 下面是 Manager，Manager 下面是员工。

产品经理要求：从任意一个节点出发，查出该节点及其所有下级（含多级），并用缩进显示层级。

普通查询只能查一层。Alice 学会了 WITH RECURSIVE 递归 CTE，一条 SQL 就搞定了整棵子树。

3. Concept

(1) 子查询分类

类型	返回	可出现位置	示例
标量子查询	单行单值	SELECT, WHERE, HAVING	`(SELECT MAX(salary) ...)`
列子查询	单列多行	WHERE + IN/ANY/ALL	`WHERE id IN (SELECT ...)`
行子查询	单行多列	WHERE	`WHERE (a,b) = (SELECT x,y ...)`
表子查询	多行多列	FROM	`FROM (SELECT ...) AS t`

▶ 示例：标量子查询在 SELECT

SQL

SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees) AS company_avg,
  salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees
WHERE department_id = 5;

TEXT 📖 仅展示

 name    | salary | company_avg | diff
---------+--------+-------------+-------
 Alice   |  95000 |    72000.00 | 23000
 Bob     |  88000 |    72000.00 | 16000

▶ 示例：标量子查询在 WHERE

SQL

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

▶ 示例：列子查询 + IN

SQL

SELECT order_id, amount
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE region = 'NA'
);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：行子查询

SQL

SELECT name, department_id, salary
FROM employees
WHERE (department_id, salary) = (
  SELECT department_id, MAX(salary)
  FROM employees
  GROUP BY department_id
  HAVING department_id = employees.department_id
);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) EXISTS / NOT EXISTS

EXISTS 检查子查询是否有返回行，不关心具体值，只关心"存在与否"。

▶ 示例：EXISTS 找有订单的用户

SQL

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

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

方式	语法	停止时机	NULL 友好
IN	`WHERE id IN (SELECT ...)`	全部扫描	有 NULL 时需注意
EXISTS	`WHERE EXISTS (SELECT 1 ...)`	找到一行即停	NULL 无影响
JOIN	`JOIN ...`	全部匹配	取决于 JOIN 类型

▶ 示例：NOT EXISTS 找无订单用户

SQL

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

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NOT EXISTS 比 NOT IN 更安全：NOT IN 遇到子查询结果含 NULL 时，整个结果为空。

▶ 示例：NOT IN 的 NULL 陷阱

SQL

SELECT name FROM customers
WHERE region NOT IN ('NA', 'EU', NULL);

TEXT 📖 仅展示

(0 rows)

因为 x NOT IN (a, b, NULL) 等价于 x <> a AND x <> b AND x <> NULL，而 x <> NULL 为 UNKNOWN，整体为 FALSE。

(3) ANY / ALL

▶ 示例：ANY 找薪资高于某部门任一员工的人

SQL

SELECT name, salary
FROM employees
WHERE salary > ANY (
  SELECT salary FROM employees WHERE department_id = 3
);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

等价于 > MIN(子查询结果)。

▶ 示例：ALL 找薪资高于某部门所有员工的人

SQL

SELECT name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department_id = 3
);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

等价于 > MAX(子查询结果)。

操作符	含义	等价
`> ANY (...)`	大于任一个	`> MIN(...)`
`> ALL (...)`	大于所有	`> MAX(...)`
`= ANY (...)`	等于任一个	`IN (...)`

▶ 示例：表子查询在 FROM

SQL

SELECT
  department_id,
  avg_salary,
  count
FROM (
  SELECT
    department_id,
    AVG(salary) AS avg_salary,
    COUNT(*)    AS count
  FROM employees
  GROUP BY department_id
) AS dept_stats
WHERE avg_salary > 70000;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

4. Key Points

(1) CTE（WITH 子句）

CTE（Common Table Expression）用 WITH 定义命名临时结果集，可多次引用。

▶ 示例：CTE 简化多层嵌套

SQL

WITH regional_sales AS (
  SELECT
    region,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
),
top_regions AS (
  SELECT region
  FROM regional_sales
  WHERE total_sales > (SELECT AVG(total_sales) FROM regional_sales)
)
SELECT
  o.order_id,
  o.amount,
  o.region
FROM orders o
WHERE o.region IN (SELECT region FROM top_regions)
ORDER BY o.amount DESC;

TEXT 📖 仅展示

  result  
----------
   42.50
(1 row)

方式	可读性	可复用	优化器内联	物化
嵌套子查询	差	否	是	—
CTE	优	是	PG 12+ 自动决定	可手动 MATERIALIZED
临时表	中	是	否	写磁盘

▶ 示例：CTE MATERIALIZED 强制物化

SQL

WITH expensive_calc AS MATERIALIZED (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM expensive_calc
UNION ALL
SELECT * FROM expensive_calc;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

MATERIALIZED 强制计算一次并缓存，适合多次引用且计算量大的 CTE。

▶ 示例：CTE NOT MATERIALIZED 强制内联

SQL

WITH simple_filter AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE region = 'NA'
)
SELECT * FROM simple_filter WHERE amount > 50000;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

NOT MATERIALIZED 让优化器内联展开，适合简单谓词下推场景。

(2) 递归 CTE（WITH RECURSIVE）

递归 CTE 是处理树形和图形数据的标准 SQL 方案。

语法结构：

SQL

WITH RECURSIVE cte_name AS (
  base_query        -- anchor: non-recursive seed
  UNION ALL
  recursive_query   -- references cte_name itself
)
SELECT * FROM cte_name;

▶ 示例：从 CEO 查所有下属

SQL

WITH RECURSIVE subordinates AS (
  SELECT
    employee_id,
    name,
    manager_id,
    1 AS level,
    name::text AS path
  FROM employees
  WHERE manager_id IS NULL
    AND name = 'David'
  UNION ALL
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    s.level + 1,
    s.path || ' > ' || e.name
  FROM employees e
  INNER JOIN subordinates s ON e.manager_id = s.employee_id
)
SELECT
  level,
  REPEAT('  ', level - 1) || name AS org_chart,
  path
FROM subordinates
ORDER BY path;

TEXT 📖 仅展示

 level |       org_chart        |              path
-------+------------------------+--------------------------------
     1 | David                  | David
     2 |   Alice                | David > Alice
     3 |     Bob                | David > Alice > Bob
     3 |     Charlie            | David > Alice > Charlie
     2 |   Eve                  | David > Eve
     3 |     Frank              | David > Eve > Frank

▶ 示例：限定递归深度（防无限循环）

SQL

WITH RECURSIVE subordinates AS (
  SELECT
    employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT
    e.employee_id, e.name, e.manager_id, s.level + 1
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.employee_id
  WHERE s.level < 5
)
SELECT * FROM subordinates;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

WHERE s.level < 5 限制递归最多 5 层。

▶ 示例：生成日期序列

SQL

WITH RECURSIVE date_series AS (
  SELECT '2025-01-01'::date AS dt
  UNION ALL
  SELECT dt + INTERVAL '1 day'
  FROM date_series
  WHERE dt < '2025-12-31'
)
SELECT dt FROM date_series;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Practice

▶ 示例：找出每个部门薪资最高的员工

SQL

SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary = (
  SELECT MAX(salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
ORDER BY e.department_id;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：CTE 计算客户 RFM 分值

SQL

WITH customer_orders AS (
  SELECT
    customer_id,
    MAX(created_at)                           AS last_order_date,
    COUNT(*)                                  AS frequency,
    SUM(amount)                               AS monetary
  FROM orders
  GROUP BY customer_id
)
SELECT
  customer_id,
  frequency,
  monetary,
  NTILE(4) OVER (ORDER BY monetary DESC) AS m_quartile
FROM customer_orders;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

▶ 示例：递归 CTE 查产品类别树

SQL

WITH RECURSIVE category_tree AS (
  SELECT
    category_id, parent_id, name, 0 AS depth
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT
    c.category_id, c.parent_id, c.name, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT
  depth,
  REPEAT('──', depth) || name AS tree_view
FROM category_tree
ORDER BY depth, name;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：EXISTS 找被所有区域都订购的产品

SQL

SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM regions r
  WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE oi.product_id = p.product_id
      AND o.region = r.region_code
  )
);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：CTE + 窗口函数求 Top-N

SQL

WITH ranked_orders AS (
  SELECT
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer_id, order_id, amount
FROM ranked_orders
WHERE rn <= 3;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. Comprehensive Example

Alice 的组织架构查询——从任意经理出发查全部下属，带层级缩进、路径、人数统计：

SQL

WITH RECURSIVE org_tree AS (
  SELECT
    employee_id,
    name,
    manager_id,
    1          AS level,
    name::text AS path,
    ARRAY[employee_id] AS subtree_ids
  FROM employees
  WHERE name = 'Alice'
  UNION ALL
  SELECT
    e.employee_id,
    e.name,
    e.manager_id,
    o.level + 1,
    o.path || ' > ' || e.name,
    o.subtree_ids || e.employee_id
  FROM employees e
  JOIN org_tree o ON e.manager_id = o.employee_id
)
SELECT
  o.level,
  REPEAT('    ', o.level - 1) || o.name AS org_chart,
  o.path,
  ARRAY_LENGTH(o.subtree_ids, 1)       AS team_size
FROM org_tree o
ORDER BY o.path;

TEXT 📖 仅展示

 level |         org_chart          |              path               | team_size
-------+----------------------------+---------------------------------+-----------
     1 | Alice                      | Alice                           |         1
     2 |     Bob                    | Alice > Bob                     |         2
     3 |         Diana              | Alice > Bob > Diana             |         3
     3 |         Eve                | Alice > Bob > Eve               |         4
     2 |     Charlie                | Alice > Charlie                 |         5
     3 |         Frank              | Alice > Charlie > Frank         |         6

7. 递归 CTE 执行流程

flowchart TD
    A["Anchor Query<br/>(non-recursive seed)"] --> B["Working Table T₀"]
    B --> C["Recursive Query<br/>(JOIN with T₀)"]
    C --> D{"New rows<br/>produced?"}
    D -->|Yes| E["Working Table T₁"]
    E --> F["Append to result"]
    F --> C
    D -->|No| G["Final Result<br/>(all iterations UNION ALL)"]

    style A fill:#e1f5fe
    style C fill:#fff9c4
    style G fill:#c8e6c9

步骤	操作	说明
1	执行锚点查询	非递归种子，生成初始行
2	放入工作表	T₀ = 锚点结果
3	递归查询	将工作表与原表 JOIN
4	检查新行	有新行则继续，无则终止
5	合并结果	UNION ALL 所有迭代结果

❓ 常见问题

Q CTE 和子查询性能有区别吗？

A PostgreSQL 12+ 会自动决定 CTE 是否内联。简单 CTE 通常被内联，复杂 CTE 可能物化。可用 MATERIALIZED / NOT MATERIALIZED 手动控制。

Q 递归 CTE 会不会无限循环？

A 可能。如果数据有环（如 A→B→A），递归不会停止。防范方法：加 level 限制，或追踪 path 避免重复访问。

Q NOT IN 遇到 NULL 为什么返回空？

A x NOT IN (a, NULL) 等价于 x<>a AND x<>NULL，x<>NULL 为 UNKNOWN，AND 链中 UNKNOWN 使整行为 FALSE。用 NOT EXISTS 替代更安全。

Q EXISTS 和 IN 哪个快？

A 取决于数据和索引。通常子查询结果集小时 IN 更快，外层表小时 EXISTS 更快。PostgreSQL 优化器会自动改写，多数场景性能接近。

Q 递归 CTE 能处理图结构吗？

A 可以，但需额外防环逻辑。在递归部分追踪已访问节点（用 ARRAY 或 path 字符串），避免重复访问。

Q CTE 中能否引用前面定义的 CTE？

A 可以。WITH a AS (...), b AS (SELECT ... FROM a) 中，b 可以引用 a，按定义顺序向下引用。

📖 小节

子查询分标量/列/行/表四类，各有适用位置
EXISTS/NOT EXISTS 比IN/NOT IN 更安全，不受 NULL 影响
ANY 等价"大于最小值"，ALL 等价"大于最大值"
CTE 用 WITH 定义命名临时结果，可读性远优于嵌套子查询
PostgreSQL 12+ 自动决定 CTE 内联或物化，可手动控制
WITH RECURSIVE 处理树形/图形数据，是 SQL 标准方案
递归 CTE 必须有锚点 + 递归部分，用 UNION ALL 连接
防递归死循环：加 level 限制或追踪 path

📝 作业

⭐ 用标量子查询找出薪资高于公司平均值的员工
⭐ 用 NOT EXISTS 找出从未下过单的客户
⭐⭐ 用 CTE 重写以下嵌套子查询：找出订单金额高于其客户平均订单金额的订单
⭐⭐ 用 WITH RECURSIVE 从 employees 表中查询指定经理的 3 层下属，输出层级缩进
⭐⭐⭐ 用递归 CTE + 聚合实现：从 CEO 出发，统计每个经理的直接下属人数和整个子树的人数，一条 SQL 完成

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL子查询与CTE

1. 你将学到

2. 故事

3. Concept

(1) 子查询分类

▶ 示例：标量子查询在 SELECT

▶ 示例：标量子查询在 WHERE

▶ 示例：列子查询 + IN

▶ 示例：行子查询

(2) EXISTS / NOT EXISTS

▶ 示例：EXISTS 找有订单的用户

▶ 示例：NOT EXISTS 找无订单用户

▶ 示例：NOT IN 的 NULL 陷阱

(3) ANY / ALL

▶ 示例：ANY 找薪资高于某部门任一员工的人

▶ 示例：ALL 找薪资高于某部门所有员工的人

▶ 示例：表子查询在 FROM

4. Key Points

(1) CTE（WITH 子句）

▶ 示例：CTE 简化多层嵌套

▶ 示例：CTE MATERIALIZED 强制物化

▶ 示例：CTE NOT MATERIALIZED 强制内联

(2) 递归 CTE（WITH RECURSIVE）

▶ 示例：从 CEO 查所有下属

▶ 示例：限定递归深度（防无限循环）

▶ 示例：生成日期序列

5. Practice

▶ 示例：找出每个部门薪资最高的员工

▶ 示例：CTE 计算客户 RFM 分值

▶ 示例：递归 CTE 查产品类别树

▶ 示例：EXISTS 找被所有区域都订购的产品

▶ 示例：CTE + 窗口函数求 Top-N

6. Comprehensive Example

7. 递归 CTE 执行流程

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好