I have begun reading about Common table expression and cannot think of a use case where I would need to use them. They would seem to be redundant as the same can be done with derived tables. Is there something I am missing or not understanding well? Can someone give me a simple example of limitations with regular select, derived or temp table queries to make the case of CTE? Any simple examples would be highly appreciated.
One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.
An example of self referencing is recursion: Recursive Queries Using CTE
For exciting Microsoft definitions Taken from Books Online:
A CTE can be used to:
Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.