3 ms·

You are right about recursive CTE - that's a functionality that's not possible without CTE However, I will argue that an equally great benefit is code readabil

You are right about recursive CTE - that's a functionality that's not possible without CTE However, I will argue that an equally great benefit is code readability. That allows you to give good names to pieces of SQL block is like having small functions in normal programming

larodi 2y ago
Indeed it is, although as cascade of create temp view statements within transaction works almost the same way for readability. besides, not everyone can count the open/closing braces for the CTE parts. CTEs also cannot have indexes on the intermediate results, while views/mviews can and this benefits great cascaded analysis in SQL. speaking from experience, where we had to optimize a particular network analysis for urban planning, which was initially impossible in qgis/argis, then first version ran for 3-10mins, and initially with intermediate indices the whole thing went down to 45sec-1.5min. this is not possible with CTE to my knowledge, even though I've searched for ways to tell it to build intermediate indices.