sql - CTE和TEMP表之间的主要区别是什么？

Question

使用CTE's(common table expressions)而不是temp tables有好处吗？

我经历了两者之间的性能测试，但是我发现两者之间没有太大区别。

使用CTE'S有哪些利弊？

Answer 1

CTE和临时表之间的最大区别可能是CTE的执行范围为单个 SELECT，INSERT，UPDATE，DELETE或CREATE VIEW语句。

本质上，您不能像临时表那样重复使用CTE。

从documentation

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:

1. Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

2. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

3. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

4. Reference the resulting table multiple times in the same statement.