CTE stands for Common Table Expressions in SQL. They are temporary result sets defined within a single query using the WITH clause, acting like named subqueries to improve readability and structure.
Great article, I always like to structure my queries with CTEs and I was (wrongly) assuming it all gets inlined at the end.
Sometimes it also gets complicated since these intermediate results can't be easily seen in a SQL editor. I was working on a UI to parse CTE queries and then execute them step by step to show the results of all the CTEs for easier understanding of the query (as part of this project https://github.com/sqg-dev/sqg/)
> Recursive CTEs use an iterative working-table mechanism. Despite the name, they aren't truly recursive. PostgreSQL doesn't "call itself" by creating a nested stack of unfinished queries.
If you want something that is more like actual recursion (I.e., depth-first), Oracle has CONNECT BY which does not require the same kind of tracking. It also comes with extra features to help with cycle detection, stack depth reflection, etc.
If your problem is aligned with the DFS model, the oracle technique can run circles around recursive CTEs. Anything with a deep hierarchy and early termination conditions is a compelling candidate.
I've always thought of CTEs as a code organisation tool, not an optimisation tool. The fact the some rdbms treats them as an optimisation fence was a bug, not a feature.
CTE stands for Common Table Expressions in SQL. They are temporary result sets defined within a single query using the WITH clause, acting like named subqueries to improve readability and structure.
I was morbidly curious what a "good CTE" could possibly be...
If you want something that is more like actual recursion (I.e., depth-first), Oracle has CONNECT BY which does not require the same kind of tracking. It also comes with extra features to help with cycle detection, stack depth reflection, etc.
If your problem is aligned with the DFS model, the oracle technique can run circles around recursive CTEs. Anything with a deep hierarchy and early termination conditions is a compelling candidate.