Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!
-
A volatile table persistents in the duration of the connection that creates it while a CTE is only accessible by the query following it. That is the scope of CTE is narrower and is safer.
-
If performance is a concern, use volatile (temp) tables.
-
Always use a CTE (with clause) instead of a sub query when applicable as a CTE is more flexible (can be recursive), is reusable, and is more readable.
-
A CTE can be recursive and is reusable.
References
https://www.alisa-in.tech/post/2019-10-02-ctes/
https://stackoverflow.com/questions/706972/difference-between-cte-and-subquery
https://learnsql.com/blog/sql-subquery-cte-difference/