1. 子查询

一条查询可以嵌套在另一条查询中，并把结果交给外层查询继续处理，这种查询叫作子查询。使用前要先判断外层需要的是单个值、一组值，还是“是否存在”这个布尔结果。

例如，先计算所有未归档任务的平均优先级，再找出高于平均值的任务：

1
SELECT id, title, priority
2
FROM tasks
3
WHERE archived_at IS NULL
4
  AND priority > (
5
      SELECT AVG(priority)
6
      FROM tasks
7
      WHERE archived_at IS NULL
8
  )
9
ORDER BY priority DESC, id;

括号中的 AVG() 只返回一行一列，因此可以作为一个值参与 > 比较，这类子查询叫作标量子查询。普通标量子查询如果返回多行或多列，PostgreSQL 会报错；如果一行也没有，则把结果视为 NULL。

这里的聚合查询即使没有匹配行也会返回一行，只是 AVG() 的结果为 NULL。任何优先级与 NULL 进行 > 比较都不会得到 true，因此外层查询不会返回任务。如果当前只有一条未归档任务，它与自己的平均值相等，结果为空也是正常现象。

子查询也可以与外层当前行相关。找出至少有一条高优先级任务的项目：

1
SELECT p.id, p.name
2
FROM projects AS p
3
WHERE EXISTS (
4
    SELECT 1
5
    FROM tasks AS t
6
    WHERE t.project_id = p.id
7
      AND t.priority = 3
8
      AND t.archived_at IS NULL
9
);

子查询中的 p.id 来自外层当前项目，因此这是一个关联子查询。EXISTS 只关心是否至少找到一行，通常找到第一行后就可以停止，所以内部惯例写作 SELECT 1，不需要返回真实列。

从 SQL 的逻辑上看，子查询会针对外层项目判断一次；实际执行时，优化器可能把它改写成其他等价计划，不应据此断定数据库一定会逐行执行子查询。

子查询层级过深会让逻辑难以追踪。能够用清晰的 JOIN 或 CTE 表达时，应优先选择更容易读懂的写法。

2. CTE

CTE 使用 WITH 给一个查询片段命名。它适合把复杂查询拆成几个有意义的步骤：

01
WITH task_stats AS (
02
    SELECT
03
        project_id,
04
        COUNT(*) AS total_count,
05
        COUNT(*) FILTER (WHERE status = 'done') AS done_count
06
    FROM tasks
07
    WHERE archived_at IS NULL
08
    GROUP BY project_id
09
)
10
SELECT
11
    p.id,
12
    p.name,
13
    COALESCE(stats.total_count, 0) AS total_count,
14
    COALESCE(stats.done_count, 0) AS done_count
15
FROM projects AS p
16
LEFT JOIN task_stats AS stats ON stats.project_id = p.id
17
ORDER BY p.id;

task_stats 不是永久表，只在这条 SQL 的执行期间存在。外层查询可以像使用表一样引用它。

CTE 的价值主要是组织逻辑，不应默认把它当作性能优化。对于非递归、无副作用的 CTE，PostgreSQL 通常会把只引用一次的 CTE 合并进外层查询共同优化；同一个 CTE 被引用多次时，通常会先物化结果。可以用 AS MATERIALIZED 强制物化，也可以用 AS NOT MATERIALIZED 请求合并，但后者可能造成重复计算。最终仍要通过执行计划验证性能，不能只看 SQL 形式猜测。

一条语句可以定义多个 CTE，后面的 CTE 还能引用前面的结果。命名应表达业务步骤，例如 active_tasks、project_stats，不要使用 temp1、temp2。