This feels to me like a "in other news, water is wet" kind of story. Maybe it's just me, but based on the other comments here CTEs seem to be common knowledge. I went on a SQL trial by fire this year after landing in a role that required massive amounts of weird querying for ad-hoc reporting purposes. Before this my experience and knowledge stopped at different join types.
I naturally discovered CTEs over time after seeking solutions to my problems on the usual online resources. For me the use is two-fold. 1/ I use it to create ad-hoc lookups on data, where the lookups are not available in the DB in the first place. And 2/ In complex queries with lots and lots of conditional joins, I can use a CTE to basically build up my temporary base table, getting all my data in one place, then my main `SELECT` becomes a lot cleaner and easier to read.
I've not looked at recursive CTEs yet, supposedly they can be quite handy. Not sure for what though.
We recently migrated from MySQL to Postgres, and the data reporting went from direct-db querying to Snowflake. And CTEs work on for me on all 3 platforms, so this does not even seem like a Postgres only thing.
This feels to me like a "in other news, water is wet" kind of story. Maybe it's just me, but based on the other comments here CTEs seem to be common knowledge. I went on a SQL trial by fire this year after landing in a role that required massive amounts of weird querying for ad-hoc reporting purposes. Before this my experience and knowledge stopped at different join types.
I naturally discovered CTEs over time after seeking solutions to my problems on the usual online resources. For me the use is two-fold. 1/ I use it to create ad-hoc lookups on data, where the lookups are not available in the DB in the first place. And 2/ In complex queries with lots and lots of conditional joins, I can use a CTE to basically build up my temporary base table, getting all my data in one place, then my main `SELECT` becomes a lot cleaner and easier to read.
I've not looked at recursive CTEs yet, supposedly they can be quite handy. Not sure for what though.
We recently migrated from MySQL to Postgres, and the data reporting went from direct-db querying to Snowflake. And CTEs work on for me on all 3 platforms, so this does not even seem like a Postgres only thing.