5 ms·

I would love if sql would support a slight syntax change of accepting From table select col; as an optional alternative to select col from table; This woul

I would love if sql would support a slight syntax change of accepting From table select col; as an optional alternative to select col from table; This would allow autocompleting col names in editors. Other than that I quite like sql being the standard db query language.

masklinn 3y ago
That only fixes trivial selects. But you still have issues with e.g. GROUP BY, especially since the dependency is circular: - barring extensions you can only select grouping expressions or aggregates - but instead of repeating grouping expressions you can refer to a select expression (by index, some databases also allow the alias) The "spec" order of evaluation for queries is WITH, FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, merge, ORDER BY, LIMIT. Although databases might decide to move SELECT after ORDER BY and LIMIT if they can, in order to avoid unnecessary evaluations.
- hosteur 3y ago
  > That only fixes trivial selects Absolutely true. But a huge amount of queries are in fact trivial selects. And this change alone would make autocompleting them easy for various editors/IDEs. Perfect is the enemy of the good, etc.
- marcosdumay 3y ago
  You can easily change it to make `group by` behave much better, and `having` redundant with `where`, like it should always have been if you just evaluate from start to end, without any hidden reordering.
  - masklinn 3y ago
    > You can easily change it to make `group by` behave much better Change what? Make "group by" behave better how? > `having` redundant with `where` They filter different things, how do you make `where` perform both jobs? > like it should always have been if you just evaluate from start to end, without any hidden reordering. The only "hidden reordering" is an optimisation.
amai 3y ago
Have a look at https://prql-lang.org/ https://prql-lang.org/. They got this right.
- hosteur 3y ago
  > Have a look at https://prql-lang.org/ https://prql-lang.org/. They got this right. Yes, they absolutely got this right. But I agree with the author of TFA in that I don't want another query language. I just want this specific change to SQL. Maybe others as well. But I do not want another query language. PRQL is yet another query language.

masklinn 3y ago

That only fixes trivial selects. But you still have issues with e.g. GROUP BY, especially since the dependency is circular: - barring extensions you can only select grouping expressions or aggregates - but instead of repeating grouping expressions you can refer to a select expression (by index, some databases also allow the alias) The "spec" order of evaluation for queries is WITH, FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, merge, ORDER BY, LIMIT. Although databases might decide to move SELECT after ORDER BY and LIMIT if they can, in order to avoid unnecessary evaluations.

hosteur 3y ago
> That only fixes trivial selects Absolutely true. But a huge amount of queries are in fact trivial selects. And this change alone would make autocompleting them easy for various editors/IDEs. Perfect is the enemy of the good, etc.
marcosdumay 3y ago
You can easily change it to make `group by` behave much better, and `having` redundant with `where`, like it should always have been if you just evaluate from start to end, without any hidden reordering.
- masklinn 3y ago
  > You can easily change it to make `group by` behave much better Change what? Make "group by" behave better how? > `having` redundant with `where` They filter different things, how do you make `where` perform both jobs? > like it should always have been if you just evaluate from start to end, without any hidden reordering. The only "hidden reordering" is an optimisation.

amai 3y ago

Have a look at https://prql-lang.org/ https://prql-lang.org/. They got this right.

hosteur 3y ago
> Have a look at https://prql-lang.org/ https://prql-lang.org/. They got this right. Yes, they absolutely got this right. But I agree with the author of TFA in that I don't want another query language. I just want this specific change to SQL. Maybe others as well. But I do not want another query language. PRQL is yet another query language.