Menu
Having been stuck with SQL2000 for far too long, I've not really had a lot of exposure to Common Table Expressions.
The answers I've given here (#4025380) and here (#4018793) have gone against the flow in that they didn't use a CTE.
I appreciate that for recursion they are the beez kneez, and there are a few queries that can be greatly simplified by their use, but at what point is their use just frivolous? Do they have a great performance benefit over a subquery or a join? Do they really simplify code and make it more maintainable?
In short, when is it good practice to use a CTE over a 'lesser' syntax.
674
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.
As for your question. 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.
A straightforward question deserves a straightforward answer: yes, you can. Now that you know how to use multiple CTEs, writing a CTE that references another CTE is just a variation of what you've learned.
You should generally use a CTE over a normal subquery if:
In short, yes they do make queries more readable when well-used.
191
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
