SQL CTE 和 SubQuery 的区别？

Difference between CTE and SubQuery?

提问by dance2die

From this post How to use ROW_NUMBER in the following procedure?

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

There are two versions of answers where one uses a sub-queryand the other uses a CTEto solve the same problem.

有两种版本的答案，一种使用 a sub-query，另一种使用 aCTE来解决同一问题。

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

那么，CTE (Common Table Expression)在“子查询”上使用 a 的优势是什么（因此，查询实际上在做什么更具可读性）

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

使用的唯一的优点CTE了sub-select，是我可以实际命名的sub-query。当 CTE 用作简单（非递归）CTE 时，这两者之间还有其他区别吗？

采纳答案by Marc Gravell

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.

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

回答by casperOne

The main advantage of the Common Table Expression(when not using it for recursive queries) is encapsulation, instead of having to declare the sub-query in every place you wish to use it, you are able to define it once, but have multiple references to it.

公共表表达式的主要优点（当不将其用于递归查询时）是封装，不必在您希望使用它的每个地方都声明子查询，您可以定义它一次，但有多个引用到它。

However, this does notmean that it is executed only once (as per previous iterations of this very answer, thank you to all those that have commented). The query definitely has the potential to be executed multiple times if referenced multiple times; the query optimizer ultimately makes the decision as to howthe CTE should be interpreted.

但是，这并不能意味着它只执行一次（按照这个非常的答案以前的迭代，感谢所有那些评论）。如果多次引用，该查询肯定有可能被多次执行；查询优化器最终决定如何解释 CTE。

回答by Quassnoi

CTE's are most useful for recursion:

CTE对递归最有用：

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

will return @nrows (up to 101). Useful for calendars, dummy rowsets etc.

将返回@n行（最多101）。对日历、虚拟行集等有用。

They are also more readable (in my opinion).

它们也更具可读性（在我看来）。

Apart from this, CTE's and subqueriesare identical.

除此之外，CTE's 和subqueries是相同的。

回答by user340140

One difference that hasn't been mentioned is a single CTE can be referenced in the several parts of a union

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

回答by AlexCuse

Unless I'm missing something, you can name CTE's and subqueries just as easily.

除非我遗漏了什么，否则您可以同样轻松地命名 CTE 和子查询。

I guess the main difference is readability (I find the CTE more readable because it defines your subquery up front rather than in the middle).

我想主要区别在于可读性（我发现 CTE 更具可读性，因为它在前面而不是中间定义了您的子查询）。

And if you need to do anything with recursion, you are going to have a bit of trouble doing that with a subquery ;)

如果你需要用递归做任何事情，你会在使用子查询时遇到一些麻烦；)

回答by Ajax

One important fact that nobody has mentioned is that (at least in postgres), CTEs are optimization fences:

一个没有人提到的重要事实是（至少在 postgres 中），CTE 是优化栅栏：

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

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

That is, they will be treated as their own atomic query, rather than folded into the whole query plan. I lack the expertise to give a better explanation, but you should check the semantics for the version of sql you are using; for advanced users, being able to create an optimization fence can help performance if you are expert level in controlling query planner; in 99% of cases, however, you should avoid trying to tell the query planner what to do, because what you think will be faster is likely worse than what it thinks will be faster. :-)

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

回答by A-K

Adding to others' answers, if you have one and the same subquery used several times, you can replace all these subqueries with one CTE. This allows you to reuse your code better.

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

回答by HLGEM

One thing that you need to understand also is that in older versions of SQL Server (yes many people still need to support SQL Server 2000 databases), CTEs are not allowed and then the derived table is your best solution.

您还需要了解的一件事是，在旧版本的 SQL Server 中（是的，许多人仍然需要支持 SQL Server 2000 数据库），不允许使用 CTE，因此派生表是您最好的解决方案。

回答by Basic_

HINT: (MAXRECURSION n)

提示：（最大递归n）

you can limit the number of recursion levels allowed for a specific statement by using the MAXRECURSIONhint and a value between 0and 32,767in the OPTIONclause

您可以通过在子句中使用MAXRECURSION提示和0到 32,767之间的值来限制特定语句允许的递归级别数OPTION

For example, you could try:

例如，您可以尝试：

OPTION 
      (MAXRECURSION 150)

GO