On: Half a century of SQL

[kccqzy]

I think subqueries are a useful feature that doesn't get taught often to beginners. Using subqueries it's much easier to think clearly and intuitively. Even imperative programmers who don't have a ton of functional programming experience still understand nested function calls `f(g(h()))`. With this you don't even have to know when the different parts of a SELECT are applied, and you don't even have to know things like HAVING.

[NortySpock → kccqzy]

I have found Common Table Expressions -- which are slightly easier for the developer to re-order in the query -- to be even more readable than subqueries.

[cwbriscoe → NortySpock]

Yes, CTE's are a much more readable than subqueries IMO. I can't really explain it but it just makes queries easier to build and read to me.

[fifilura → cwbriscoe]

Two benefits:

1) The flow is clearer: take this, then transform this, then filter this

2) CTEs makes it easy to debug parts. You just change what you select from on the last line.

Number 2 is the kicker IMO

[disgruntledphd2 → fifilura]

And this is why you should always, always have a `final` CTE and the last line should be `select * from final` so that you can easily change this.

We use a LOT of SQL at $current_dayjob and I almost always end up adding this to the queries. Clearly I need to do a better job of evangelising my approach.