为什么SQL优化器会选择执行表自连接？结合CTE查询执行计划分析

问题1：第6-8步类自连接操作的原因 #

你观察到的并不是PROJECT表的真实自连接，这是优化器做字段投影裁剪的中间步骤：

• 你的SQL中CTE逻辑仅需要PROJECT表的ID（用于关联TASK表）、TITLE（用于分组和最终返回）两个字段，其余字段完全不需要参与计算
• 优化器在查询重写阶段，会先全量扫描PROJECT表，随后裁剪掉所有无用字段，将裁剪后的结果存储为临时视图
• 整个过程没有添加任何行过滤条件，也没有执行关联匹配逻辑，因此临时视图的行数、占用字节和原PROJECT表完全一致，仅保留的列更少，可以提升后续关联、分组步骤的计算效率。

问题2：第1、2步作用的猜测验证 #

你的猜测完全正确：

• 第2步是CTE物化操作：优化器检测到查询中会两次用到Project_UnfinishedCount的结果（外层查询取项目名称、子查询求最大未完成任务数），因此会先计算一次CTE的统计结果（每个项目的title、对应未完成任务数cnt），存储为临时结果集，避免重复两次执行PROJECT和TASK的关联、分组逻辑，大幅提升执行效率
• 第1步是外层查询的谓词过滤操作：将CTE物化结果和子查询返回的最大未完成任务数做等值匹配，筛选出cnt等于最大值的项目title返回。

内容的提问来源于stack exchange，提问作者user13353974