Menu
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
773
Advantage of Using CTE CTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.
There is nothing magic about CTES that make them faster or slower than the equivalent query with subquery. There are use cases that are only possible efficiently with CTES such as recursion, but its severe edge case.
The performance of CTEs and subqueries should, in theory, be the same since both provide the same information to the query optimizer. One difference is that a CTE used more than once could be easily identified and calculated once. The results could then be stored and read multiple times.
If you are joining multiple tables with millions of rows of records in each, CTE will perform significantly worse than temporary tables. I've seen this from my own experience. CTE's perform significantly slower. CTE's also perform slower because the results are not cached.
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.
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
