SQL嵌入式CTE的应用场景探讨：是否仅需置于顶层？

之前我一直以为CTE必须放在主SELECT子句之前，实际测试发现CTE可以嵌入任意子查询中——下面这段代码已经在BigQuery、Redshift、MySQL、Postgres上验证过：

-- tested on BigQuery, Redshift, MySQL, Postgres
SELECT CONCAT(Odd.num, Even.num) FROM
  (WITH Numbers AS (SELECT 1 AS num UNION ALL SELECT 3 UNION ALL SELECT 5) SELECT * FROM Numbers) Odd,
  (WITH Numbers AS (SELECT 2 AS num UNION ALL SELECT 4 UNION ALL SELECT 6) SELECT * FROM Numbers) Even

那这种嵌入式CTE有没有实际用处？还是必须像变量声明一样放在顶层？

嵌入式CTE的实际使用场景 #

• 隔离局部逻辑，避免命名冲突：如果某个子查询有自己独立的复杂逻辑，而且不需要和其他子查询共享，用嵌入式CTE可以把这部分逻辑封装在子查询内部。比如示例里两个子查询都用了Numbers作为CTE名称，要是放在顶层就会因为命名重复报错，嵌入式就完全没问题。
• 简化模块化调试：写复杂查询时，可以先单独把某个子查询的逻辑用CTE写好测试，确认没问题后直接嵌入主查询，不用特意把CTE移到顶层，减少调整代码的麻烦。
• 局部复用中间数据：如果某个子查询内部需要多次用到同一个中间数据集，嵌入式CTE能在这个子查询范围内实现复用，不用把这个中间数据暴露到整个查询的顶层，让主查询结构更简洁。

什么时候更适合用顶层CTE #

• 当多个子查询或者主查询的不同部分需要复用同一个CTE数据集时，放在顶层更高效，避免重复定义相同的逻辑，也方便后续维护。
• 如果CTE的逻辑比较通用，或者需要让其他开发者一眼看懂整个查询的依赖关系，顶层CTE的结构会更清晰。

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