Nonsense, Scala is arguably leading the way in the statically typed composable query dsl department; see Slick [1] and Quill [2] among others.
Outside of Haskell's Esqueleto I am not aware of any statically typed query dsl that comes remotely close to the aforementioned. LINQ to SQL/Objects provides static query generation but doesn't compose; beyond that, what is there?
[1] https://github.com/slick/slick [2] https://github.com/getquill/quill
How do you mean? The result of a LINQ query is a sequence that can be used as the input of another LINQ query.
If you have an example of .NET based query composition to share I'd like to see it; last I checked there was no such functionality available.
It's only after you force it to materialize (I.e. call ToList) that you can't further "compose" it.
http://people.csail.mit.edu/matei/papers/2015/sigmod_spark_s...
I find subselects are more predictable, but I wouldn't say they're generally more performant. The explain plan's tree maps more closely to the subquery dependency tree with selects, so someone tweaking their query and staring at the explain plan to get it how they want will have an easier time with the subselect strategy.
Be careful of CTEs if you try that, though. A long chain of CTEs with the transformation I gave is almost guaranteed to give a bad plan(unless you want seq scans all the way down).
Want a filter clause? Got it.
Need a weighted distinct count? Now it's part of the language.
Also, you can rephrase a lot of these SQL features as subqueries. It's surprising how many database bugs you can find when you do it. Not so much in Postgres, but I probably found a dozen in Redshift. I mean rephrasing this:
as this(where t is either a permanent table, view, or CTE as appropriate): Though for Postgres the filter will likely have better performance.