首页 文章

CTE和SubQuery之间的区别？

提问于
浏览 次
120

来自这篇文章How to use ROW_NUMBER in the following procedure?

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

那么，使用 CTE (Common Table Expression) 而不是s ub-query 的优势是什么（因此，更多 readable 查询实际上在做什么）

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

sql sql-server tsql subquery common-table-expression

9 回答

  • 10

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

    回复于
  • 2

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

    回复于
  • 8

    Common Table Expression（当不用于recursive queries时）的主要优点是封装，而不是必须在您希望使用它的每个地方声明子查询，您可以定义一次，但有多个引用它 .

    但是，这并不意味着它只执行一次（根据previous iterations of this answer，感谢所有评论过的人） . 如果多次引用，查询肯定有多次执行;查询优化器最终决定如何解释CTE .

    回复于
  • 75

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

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

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

    回复于
  • 5

    提示:( MAXRECURSION n）

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

    例如，您可以尝试：

    OPTION 
      (MAXRECURSION 150)

GO
    回复于
  • 5

    除非我遗漏了某些内容，否则您可以轻松地命名CTE和子查询 .

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

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

    回复于
  • 84

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

    回复于
  • 15

    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 是完全相同的 .

    回复于
  • 4

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

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

    回复于

相关问题