4 ms·
Let me reference fields as I create them: select xxxxx as a , a * 2 as b
by elygre 10mo ago
Let me reference fields as I create them:
select xxxxx as a
, a * 2 as b
- zX41ZdbW 10mo agoThis will be great! One of the things ClickHouse has had since 2016.
- deleted 10mo ago[deleted]
- cyberax 10mo agoSQL needs to have `select` as the _last_ part, not the first. LINQ has had this for 2 decades by now: "from table_a as a, table_b as b where ... select a.blah, b.duh".
- cryptonector 10mo agoThis is not relevant to GP's point. This is a separate topic, which... I don't really care, but I know a lot of people want to be able to write SQL as you suggest, and it's not hard to implement, so, sure. Though, I think it might have to be table sources, then `SELECT`, then `WHERE`, then ... because you might want to refer to output columns in the `WHERE` clause.
- cyberax 10mo agoIdeally, it needs to be "from", then arbitrary number of something like `let` statements that can introduce new variables, maybe interspersed with where-s, and then finally "select". "select" can also be replaced with annotations, something like: `from table_1 t1 let t1.column_1 as @output_1 where ...` and then just collect all the @-annotated variables. I need to write a lot of SQL, and it's so clumsy. Every time I need a CTE, I have to look into the documentation for the exact syntax.
- 1718627440 10mo ago> Ideally, it needs to be "from", then arbitrary number of something like `let` statements Isn't that what a CTE is?
- tracker1 10mo agoThat was kind of my first thought...
- cryptonector 10mo agoNot quite. u/cyberax wants scalar bindings, not table-valued bindings. Something like FROM foo LET a = (x + y) * z SELECT a; whereas CTEs are... Common Table Expressions.
- snuxoll 10mo agoWHERE clauses are pushed down into the query planner before the SELECT list is processed, that’s why HAVING exists. The logical order, in full, is: FROM WHERE/JOIN (you can join using WHERE clauses and do FROM a,b still) SELECT HAVING
- 1718627440 10mo agoThat's the order in which the processing happens, but this doesn't need to be reflected in the language. The language has this ordering so it sounds like a natural language which SQL was invented for.
- cryptonector 10mo agoSee 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.
- snuxoll 10mo agoI 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.
- agnosticmantis 10mo agoThe Pipe Query Syntax in GoogleSQL implements this elegantly as well: https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax https://docs.cloud.google.com/bigquery/docs/reference/standa...
- jiggawatts 10mo agoAlso in the Kusto Query Language (KQL) as used by Azure Log Analytics.
- viraptor 10mo agohttps://prql-lang.org/ https://prql-lang.org/ and compile to SQL.
- cyberax 10mo agoThank you! This is indeed close to what I want from SQL!