最佳做法：在CTE上选择* [英] Best Practice: Select * on CTE
问题描述
以下帖子具有令人信服的理由，通常避免在SQL中使用 select * 。
The following post had compelling reasons for generally avoiding the use of select * in SQL.
讨论中的示例是何时使用 select * 或不可接受的。但是，我没有看到有关公用表表达式（CTE）的讨论。在CTE中使用 select * 有什么缺点吗？
In the discussion was examples of when it was or wasn't acceptable to use select * However I did not see discussion on common table expression (CTE). Are there any drawbacks for using select * in CTEs?
示例：
WITH CTE1 AS
(
SELECT Doc, TotalDue
FROM ARInvoices
WHERE CustomerName = 'ABC'
UNION
SELECT Doc, - TotalDue
FROM ARInvoiceMemos
WHERE CustomerName = 'ABC'
)
select * from CTE1
UNION
Select 'Total' as Doc, sum(TotalDue)
FROM CTE1
推荐答案
由于您已经正确列出了 cte 中的 select * 不会有任何危害。
实际上，使用 select * 可能是正确的地方，因为没有必要列出两次列了。
除非，否则您无需使用 cte 返回的所有列。 （即，在查询中使用了 cte 中的列，但在 select 子句中未使用该列），我建议仅列出甚至您需要的列也指向 cte 。
Since you already properly listed the column names in the cte, I don't see any harm in using select * from the cte.
In fact, it might be just the right place to use select *, since there is no point of listing the columns twice.
Unless you don't need to use all the columns returned by the cte. (i.e a column in the cte is used on the query, but not in the select clause) In that case, I would suggest listing only the columns you need even of the from is pointing to a cte.
请注意，如果 cte 本身使用 select * ，然后您链接到的帖子中列出的所有缺点都将适用于它。
Note that if the cte itself uses select * then all of the drawbacks listed in the post you linked to applies to it.
我对 select * 的主要反对意见是，懒惰的开发人员通常使用它，而不考虑 * 。
My main objection to select * is that it's usually used by lazy developers that doesn't consider the consequences of the *.
注意：我在这里写的所有内容也适用于派生表
Note: Everything I've written here applies to derived tables as well.
这篇关于最佳做法：在CTE上选择*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
