]
07-31-2023, 09:58 AM
1. With a CTE, you can use recursion.
2. With a CTE, you only need to write it once, but you can reference it in multiple places within the query. It can therefore allow you to avoid repeating yourself, and might also make the query easier to read and interpret (even in cases where the query only references it once).
3. A CTE appears to provide metadata about itself to the query optimiser, such that if a CTE is referenced more than once in the same query (for example, if it joins to itself), the query optimiser could potentially use that metadata to improve the overall query execution plan (this does not appear to occur with subqueries).
So, in summary, if you want to use recursion, or you think it would make your code more presentable and easier to interpret, or you're using the same subquery more than once, use a CTE.
2. With a CTE, you only need to write it once, but you can reference it in multiple places within the query. It can therefore allow you to avoid repeating yourself, and might also make the query easier to read and interpret (even in cases where the query only references it once).
3. A CTE appears to provide metadata about itself to the query optimiser, such that if a CTE is referenced more than once in the same query (for example, if it joins to itself), the query optimiser could potentially use that metadata to improve the overall query execution plan (this does not appear to occur with subqueries).
So, in summary, if you want to use recursion, or you think it would make your code more presentable and easier to interpret, or you're using the same subquery more than once, use a CTE.