Best Practice: Select * on CTE

Question

The following post had compelling reasons for generally avoiding the use of select * in SQL.

Why is SELECT * considered harmful?

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?

Example:

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

Answer 1

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.

Note that if the cte itself uses select * then all of the drawbacks listed in the post you linked to applies to it.

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.