CTE和SubQuery之间的区别？

从这篇文章如何在以下过程中使用ROW_NUMBER？

有两个版本的答案,其中一个使用a SubQuery,另一个使用a CTE来解决相同的问题.

那么,使用CTE (Common Table Expression)over as 的优点是什么ub-query(因此,查询实际上更具可读性)

使用CTEover sub select 的唯一优点是我可以实际命名子查询.当CTE用作简单(非递归)CTE时,这两者之间是否存在其他差异？

1> Marc Gravell..：

---

在子查询与简单(非递归)CTE版本中,它们可能非常相似.您必须使用分析器和实际执行计划来发现任何差异,这将特定于您的设置(因此我们无法完整地告诉您答案).

在一般 ; CTE可以递归使用; 子查询不能.这使它们特别适合树形结构.

---

@Marc Gravell:我们可以做的不仅仅是因为探测器的行为不能保证,而是CTE的行为(就评估而言).

2> casperOne..：

---

公用表表达式的主要优点(当不用于递归查询时)是封装,而不是必须在您希望使用它的每个地方声明子查询,您可以定义一次,但有多个引用它.

但是,这并不能意味着它是只执行一次(按照这个答案的以前的迭代,感谢所有那些评论).如果多次引用,查询肯定有多次执行; 查询优化器最终决定如何解释CTE.

---

这个答案非常误导.CTE不是临时表; 将CTE视为仅针对当前查询定义的视图.就像一个视图一样,CTE被扩展并折叠成整体查询计划.全局优化仍将发生,但不要仅仅因为您使用CTE而只考虑执行一次查询.这是一个适合这个空间的简单示例:WITH vw AS(SELECT COUNT(*)c FROM Person)SELECT ac,bc FROM vw a,vw b; 查询计划将清楚地显示两个扫描/聚合和连接,而不是仅两次投射相同的结果.

---

@IanC @Michael Petito @AlexCuse - 我根据您的所有输入重新修改了答案,谢谢您的贡献.

3> Quassnoi..：

---

CTE对递归最有用:

WITH hier(cnt) AS (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt < @n
        )
SELECT  cnt
FROM    hier

将返回@n行(最多101).适用于日历,虚拟行集等.

它们也更具可读性(在我看来).

除此之外CTE,subqueries它们是相同的.

---

@ObinnaNnenanya：仅当它不是批处理中的第一条语句时。无论如何，用分号终止语句是一个好主意，即使SQL Server不在`WITH`，`MERGE`和类似版本之前的当前版本中也不执行它

4> user340140..：

---

未提及的一个区别是单个CTE可以在联合的几个部分中引用

5> AlexCuse..：

---

除非我遗漏了什么,否则你可以轻松地命名CTE和子查询.

我猜主要区别在于可读性(我发现CTE更具可读性,因为它在前面而不是在中间定义了子查询).

如果你需要对递归做任何事情,那么使用子查询做这件事会有点麻烦;)

---

你可以*命名*CTE,但你只能*别名*子查询.不同的是,您可以重复使用具有多个别名的CTE(参见@Michael Petito在他对casperOne的评论中的示例).我不知道用子查询做任何方法.

6> Ajax..：

---

没人提到的一个重要事实是(至少在postgres中),CTE是优化范围:

https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/

也就是说,它们将被视为自己的原子查询,而不是折叠到整个查询计划中.我缺乏提供更好解释的专业知识,但你应该检查你正在使用的sql版本的语义; 对于高级用户,如果您是控制查询计划程序的专家级别,则能够创建优化围栏可以帮助提高性能; 但是,在99%的情况下,您应该避免尝试告诉查询计划员该做什么,因为您认为更快的可能比它认为更快的更糟糕.:-)

7> A-K..：

---

添加到其他人的答案,如果你有多次使用同一个子查询,你可以用一个CTE替换所有这些子查询.这使您可以更好地重用代码.