一条查询可以嵌套在另一条查询中,并把结果交给外层查询继续处理,这种查询叫作子查询。使用前要先判断外层需要的是单个值、一组值,还是“是否存在”这个布尔结果。
例如,先计算所有未归档任务的平均优先级,再找出高于平均值的任务:
1SELECT id, title, priority 2FROM tasks 3WHERE archived_at IS NULL 4 AND priority > ( 5 SELECT AVG(priority) 6 FROM tasks 7 WHERE archived_at IS NULL 8 ) 9ORDER BY priority DESC, id;
括号中的 AVG() 只返回一行一列,因此可以作为一个值参与 > 比较,这类子查询叫作标量子查询。普通标量子查询如果返回多行或多列,PostgreSQL 会报错;如果一行也没有,则把结果视为 NULL。
AVG()
>
NULL
这里的聚合查询即使没有匹配行也会返回一行,只是 AVG() 的结果为 NULL。任何优先级与 NULL 进行 > 比较都不会得到 true,因此外层查询不会返回任务。如果当前只有一条未归档任务,它与自己的平均值相等,结果为空也是正常现象。
true
子查询也可以与外层当前行相关。找出至少有一条高优先级任务的项目:
1SELECT p.id, p.name 2FROM projects AS p 3WHERE 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,不需要返回真实列。
p.id
EXISTS
SELECT 1
从 SQL 的逻辑上看,子查询会针对外层项目判断一次;实际执行时,优化器可能把它改写成其他等价计划,不应据此断定数据库一定会逐行执行子查询。
子查询层级过深会让逻辑难以追踪。能够用清晰的 JOIN 或 CTE 表达时,应优先选择更容易读懂的写法。
CTE 使用 WITH 给一个查询片段命名。它适合把复杂查询拆成几个有意义的步骤:
WITH
01WITH 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) 10SELECT 11 p.id, 12 p.name, 13 COALESCE(stats.total_count, 0) AS total_count, 14 COALESCE(stats.done_count, 0) AS done_count 15FROM projects AS p 16LEFT JOIN task_stats AS stats ON stats.project_id = p.id 17ORDER BY p.id;
task_stats 不是永久表,只在这条 SQL 的执行期间存在。外层查询可以像使用表一样引用它。
task_stats
CTE 的价值主要是组织逻辑,不应默认把它当作性能优化。对于非递归、无副作用的 CTE,PostgreSQL 通常会把只引用一次的 CTE 合并进外层查询共同优化;同一个 CTE 被引用多次时,通常会先物化结果。可以用 AS MATERIALIZED 强制物化,也可以用 AS NOT MATERIALIZED 请求合并,但后者可能造成重复计算。最终仍要通过执行计划验证性能,不能只看 SQL 形式猜测。
AS MATERIALIZED
AS NOT MATERIALIZED
一条语句可以定义多个 CTE,后面的 CTE 还能引用前面的结果。命名应表达业务步骤,例如 active_tasks、project_stats,不要使用 temp1、temp2。
active_tasks
project_stats
temp1
temp2
此段落已有评论,按 Enter 查看评论及回复列表。
连接 GitHub 后查看和发表评论,也可在 GitHub 查看公开讨论。
1. 子查询
一条查询可以嵌套在另一条查询中,并把结果交给外层查询继续处理,这种查询叫作子查询。使用前要先判断外层需要的是单个值、一组值,还是“是否存在”这个布尔结果。
例如,先计算所有未归档任务的平均优先级,再找出高于平均值的任务:
括号中的
AVG()只返回一行一列,因此可以作为一个值参与>比较,这类子查询叫作标量子查询。普通标量子查询如果返回多行或多列,PostgreSQL 会报错;如果一行也没有,则把结果视为NULL。这里的聚合查询即使没有匹配行也会返回一行,只是
AVG()的结果为NULL。任何优先级与NULL进行>比较都不会得到true,因此外层查询不会返回任务。如果当前只有一条未归档任务,它与自己的平均值相等,结果为空也是正常现象。子查询也可以与外层当前行相关。找出至少有一条高优先级任务的项目:
子查询中的
p.id来自外层当前项目,因此这是一个关联子查询。EXISTS只关心是否至少找到一行,通常找到第一行后就可以停止,所以内部惯例写作SELECT 1,不需要返回真实列。从 SQL 的逻辑上看,子查询会针对外层项目判断一次;实际执行时,优化器可能把它改写成其他等价计划,不应据此断定数据库一定会逐行执行子查询。
子查询层级过深会让逻辑难以追踪。能够用清晰的 JOIN 或 CTE 表达时,应优先选择更容易读懂的写法。
2. CTE
CTE 使用
WITH给一个查询片段命名。它适合把复杂查询拆成几个有意义的步骤:task_stats不是永久表,只在这条 SQL 的执行期间存在。外层查询可以像使用表一样引用它。CTE 的价值主要是组织逻辑,不应默认把它当作性能优化。对于非递归、无副作用的 CTE,PostgreSQL 通常会把只引用一次的 CTE 合并进外层查询共同优化;同一个 CTE 被引用多次时,通常会先物化结果。可以用
AS MATERIALIZED强制物化,也可以用AS NOT MATERIALIZED请求合并,但后者可能造成重复计算。最终仍要通过执行计划验证性能,不能只看 SQL 形式猜测。一条语句可以定义多个 CTE,后面的 CTE 还能引用前面的结果。命名应表达业务步骤,例如
active_tasks、project_stats,不要使用temp1、temp2。