CTE 和 SubQuery 的区别?

来自这篇文章如何在以下程序?

有两种版本的答案,一种使用 sub-query,另一种使用 CTE 来解决相同的问题.

There are two versions of answers where one uses a sub-query and the other uses a CTE to solve the same problem.

那么,使用 CTE(通用表表达式) 比子查询"有什么优势(因此,更可读strong> 查询实际上在做什么)

Now then, what is the advantage of using a CTE (Common Table Expression) over a 'sub-query`(thus, more readable what the query is actually doing)

使用 CTE 而不是 sub-select 的唯一优点是我实际上可以命名子查询.当 CTE 用作简单(非递归)CTE 时,这两个 之间还有其他区别吗?

The only advantage of using a CTE over sub-select is that I can actually name the sub-query. Are there any other differences between those two when a CTE is used as a simple (non-recursive) CTE?

推荐答案

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

In the sub-query vs simple (non-recursive) CTE versions, they are probably very similar. You would have to use the profiler and actual execution plan to spot any differences, and that would be specific to your setup (so we can't tell you the answer in full).

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

In general; A CTE can be used recursively; a sub-query cannot. This makes them especially well suited to tree structures.

相关文章