See u/cyberax's comment below. It would be nice to be able to create scalar (as opposed to table-valued) bindings that can be referred to in a WHERE (or JOIN) clause. Currently it's SELECT that establishes such bindings, and... well, it's not terribly clear where they can be used (certainly in HAVING, but first you have to GROUP BY, no?). u/cyberax's idea is to have a LET for this that can come before WHERE and before SELECT.
I mean, I get it, but the big problem is, again, the different phases of execution. The projections you perform with a select can be absolutely arbitrary and do crazy ass things (like do more subqueries that return scalar values, and query planners are notoriously bad at pushing these down), which is why I was trying to say SELECT before WHERE (project before filtering) may be linguistically intuitive, but full of foot guns.
Something like a ‘let’ binding after the FROM/JOIN list would make sense, though - from the query planners perspective it’s nothing more than a token substitution and everything would compile the same.
