🎮 SQL Battle – CTEs vs Subqueries

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.