I think CTEs are not that used, at least by web devs, because they don't fit well the ORM fairy tale in which data is mapped to objects.
I understood their power better by working side to side with data scientists using Redshift, kudos a #dataenthusiast like yourself!
Subqueries are still valuable if the relationship between the two queries is simple and the subquery is short but CTE are way better! Giving a name to a query can make all the difference.
The same goes for views I guess. I suspect that Heroku's Dataclips are built with something like that behind the scenes!
Another reason why CTEs are not that popular is that MySQL did not support recursive CTEs till recently (version 8).
Using CTEs on Postgres made a lot of complex queries easy to maintain and explain to team members.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Thank you for this post Helen!
I think CTEs are not that used, at least by web devs, because they don't fit well the ORM fairy tale in which data is mapped to objects.
I understood their power better by working side to side with data scientists using Redshift, kudos a #dataenthusiast like yourself!
Subqueries are still valuable if the relationship between the two queries is simple and the subquery is short but CTE are way better! Giving a name to a query can make all the difference.
The same goes for views I guess. I suspect that Heroku's Dataclips are built with something like that behind the scenes!
Thanks for your response, glad you found it useful.
Great to hear you are working with a data scientist too. :)
Another reason why CTEs are not that popular is that MySQL did not support recursive CTEs till recently (version 8).
Using CTEs on Postgres made a lot of complex queries easy to maintain and explain to team members.