10

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
abaldwin99
  • 903
  • 1
  • 8
  • 26
  • I believe the same case applies for CTEs for the same reasons stated in the question you linked. I can't seem to find any documents to prove this though. – John Odom May 19 '15 at 15:03
  • 4
    There is no drawback here. The same applies to derived tables `select * from (select a,b,c from some_table) as t` –  May 19 '15 at 15:05

2 Answers2

7

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.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
3

In theory the rule of thumb that select * is ill advised always applies.

In practice though, if you are a developer who considers things like design and general good programming practice as important as functionality, your CTE will most likely be coded to only return the columns which are actually needed, so select * from CTE1 might not be so bad.

amcdermott
  • 1,565
  • 15
  • 23