DEV Community

[Comment from a deleted post]
Collapse
 
rhymes profile image
rhymes

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!

Collapse
 
helenanders26 profile image
Helen Anderson

Thanks for your response, glad you found it useful.

Great to hear you are working with a data scientist too. :)

Collapse
 
rrampage profile image
Raunak Ramakrishnan • Edited

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.