For me, it’s CTE’s. I find it amazing to complete a calculation with clear intermediate steps, and goes a long way towards convincing people to use SQL rather than Excel to perform calculations on large tables of data.
What construct do you like using on a daily basis?
Only thing cooler than CTEs are Recursive CTEs, but I struggle to find use cases where I can sneak one in.
I believe recursive CTEs are pretty cool for tree traversal. Anytime you’ve got a table with a foreign key on its own primary key they might come in handy.
I was skeptical of CTEs for a long time. I just used subqueries when I could in T SQL, and then I got a new job and my new company used Postgres. In the adaptation process I took a new look at CTEs and became a convert - it’s just nicer and easier to read the intermediate step than as a subquery
Subqueries in Subqueries in Subqueries
Yes, CTEs are awesome. Especially when you don’t force materialization and the optimizer can work its magic.
I’ve had a lot of fun with window functions as well.
CTEs are so helpful for me. They make complex queries much easier to construct and lets me ‘unit test’ the parts I’m working on.
Same here, CTEs were a game changer for me when I learned about them
CTEs can be useful, particularly in PostgreSQL, where there are writable CTEs, but a lot of the time, I prefer using temp tables over CTEs, as they often perform better for larger datasets. I think one of my favorite constructs is window functions. I’ve found many uses for them, over the years.
