Subqueries haunted me for a long time. It did not worked for my mental model and I always had difficulties to create a concept with subqueries.
Mark Bosold helped me a lot by pointing out to simply think in tables. Each subquery is basically a virtual table you combine to one final table in the end.
I got into CTEs (Common Table Expression) when I started to follow dbt – they use it extensively in their tutorials. And I fell heavily in love.
So I use a lot WITH statements..
The reason for my love might be that CTEs have a more function-like structure which I know best from Python.
I still create “virtual” table but with CTEs in blocks instead of a nested structure.
It would interesting for me to learn if CTEs and Subqueries have significant different performances. Some web research showed there is not much, but I haven’t dived deeper yet.
What team are you in CTEs or Subqueries? If you use both regular, I would love to learn the use cases.