Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Shouldn't FROM come before SELECT in SQL? (2011)

stackoverflow.com

Post reply on HN
Print view View original

61–70 of 137 posts

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#61

Post by RodgerTheGreat »

The Lil[0] scripting language, like most APL-derived languages, has uniform operator precedence; expressions evaluate right-to-left unless you introduce parens:

     3 * 2 + 1
    9
     (3 * 2) + 1
    7
Lil includes an integrated query syntax which loosely resembles SQL. Queries begin with a command (select, update, extract), contain intermediate clauses in any order (where, orderby, by), and conclude with "from":

    select key value orderby value desc from x
The order of evaluation of clauses matches precedence of other operators: "from" executes, then every intermediate clause, right-to-left, then finally the command and any column expressions or aggregations. Since queries are expressions, rather than statements (like all control structures in lil), having "from" last means that you can chain "subqueries" without nesting them:

    extract key orderby value desc from
      select key:first value value:count value by value from a
I think this approach is nicely internally-consistent; the only real downside is that, as with SQL, this syntax is not ideal for IDE-driven auto-completion.

[0] https://beyondloom.com/tools/trylil.html

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#62

Post by taeric »

snarfy wrote: post #26
bigfudge wrote: post #15

Earlier quoted context omitted.

In English. I think other languages would have different word orderings. German for one, and I think maybe Russian too.

The second version is how you would say it in Spanish.

I'm actually assuming most of the time you would leave off the "from" entirely in spoken language. It is implied or completely obviated by lack of choices, no?

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#63

Post by zerocrates »

jjice wrote: post #3

Linq does this [0], maybe partially because it's SQL-like but built for working with enumerables instead so they could do what they wanted. One other quirk of SQL ordering that always gets me is that SET comes before WHERE in UPDATE. I always get terrified that I'll run without the WHERE or without selecting the WHERE. Thankfully a good database tool like DataGrip will yell at you if you try to do a modifying operati…

In terms of actually writing queries live, like if you're in the CLI client, the order of UPDATE is definitely terrifying.

The workarounds of writing it out of order or as a SELECT first are fine... I'd almost like to see a mode the interactive client sets that just rejects any UPDATE without a WHERE, and you'd have to do WHERE 1 or similar to get an "UPDATE everything."

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#64

Post by gpderetta »

Apreche wrote: post #37

FROM is not a command, it's a parameter, and an optional one at that. This is valid SQL. SELECT 1; The SQL commands are SELECT, UPDATE, INSERT, etc. Therefore, those commands should be the first thing in an instruction. If you have a file full of SQL, you probably want all the lines to start with those commands. Gonna be pretty weird to read if you have both SELECT and UPDATE lines that start with FROM. Probably diff…

  from values() select 1
or whatever it is your literal table syntax. But then again, while we are changing the syntax we should rename select into project.

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#65

Post by WorldMaker »

jacques_chester wrote: post #52
Apreche wrote: post #37

FROM is not a command, it's a parameter, and an optional one at that. This is valid SQL. SELECT 1; The SQL commands are SELECT, UPDATE, INSERT, etc. Therefore, those commands should be the first thing in an instruction. If you have a file full of SQL, you probably want all the lines to start with those commands. Gonna be pretty weird to read if you have both SELECT and UPDATE lines that start with FROM. Probably diff…

> This is valid SQL. > SELECT 1; Something like Oracle's "dual" table would handle this corner case. Or just selecting a value from itself: FROM 1 SELECT 1; or FROM ::integer SELECT 1; Or whathaveyou. In any case, the SELECT 1 case isn't fatal to putting FROM first, merely annoying. > Intelligent tooling can and does already solve this. It can progressively guess as you type, but it can't provide an upfront list of a…

Given how often `SELECT ` is already the developer "default" especially in debugging and REPL work there's a good case that `SELECT ` can truly just be the implied default case and something like `FROM 1` makes perfect sense as a standalone statement.

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#66

Post by WorldMaker »

Swizec wrote: post #45
gigatexal wrote: post #39

Earlier quoted context omitted.

that may very well be the case but perhaps there could be an evolution of the language because for newcomers the idea that the from clause is evaluated/executed/defined first is a pain point. Newbies I help sometimes wonder why things they reference in the SELECT part aren't visible/available in the FROM clause and that's because one is selecting the result of FROM ... JOIN ... WHERE etc anyway

> he idea that the from clause is evaluated/executed/defined first is a pain point Maybe my brain is broken by years of SQL and from learning English as a second language. But isn’t this supposed to follow a fairly mundane English sentence structure? “Select socks and pants from drawer”. If you started saying “from drawer select socks and pants”, wouldn’t that feel like a weird sentence structure to most people?

The only thing really missing in that from an English sentence structure perspective is generally we want just one more article in it and maybe a comma: from the drawer, select socks and pants.

But similarly I've written a lot of C# LINQ statements at this point and its from-first pattern seems to flow naturally enough that I don't see anything wrong with it (and a lot right, C# definitely has a great autocomplete experience under LINQ).

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#67

Post by cryptonector »

Apreche wrote: post #37

FROM is not a command, it's a parameter, and an optional one at that. This is valid SQL. SELECT 1; The SQL commands are SELECT, UPDATE, INSERT, etc. Therefore, those commands should be the first thing in an instruction. If you have a file full of SQL, you probably want all the lines to start with those commands. Gonna be pretty weird to read if you have both SELECT and UPDATE lines that start with FROM. Probably diff…

Nothing stops one from building a SQL parser that allows FROM to come first, really.

`SELECT 1;` would still be written `SELECT 1;` since there are no table sources there.

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#68

Post by [deleted] »

efitz wrote: post #58

Is the purpose of the syntax to maximize human readability or to make parsing efficient? SELECT/DELETE/INSERT etc. are commands, it makes sense to me that if I were writing a parser I would start with the imperative that will determine the rest of the path through the parser. I'm speculating, but I think I'm right, given this is late 70's/early 80's technology and resources were much less abundant.

[deleted]

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#69

Post by [deleted] »

noSyncCloud wrote: post #49
SoftTalker wrote: post #42

Earlier quoted context omitted.

I'll usually write it as a SELECT, to be sure my WHERE is correct, then I'll convert it to an UPDATE. And if you screw up, well that's what ROLLBACK is for.

If you have to use TSQL like me, you can use BEGIN/ROLLBACK TRAN with an OUTPUT clause to easily confirm, then just change it to COMMIT. I actually like this workflow quite a lot

[deleted]

Re: Shouldn't FROM come before SELECT in SQL? (2011)

#70

Post by foreigner »

Fradow wrote: post #28
jjice wrote: post #3

Linq does this [0], maybe partially because it's SQL-like but built for working with enumerables instead so they could do what they wanted. One other quirk of SQL ordering that always gets me is that SET comes before WHERE in UPDATE. I always get terrified that I'll run without the WHERE or without selecting the WHERE. Thankfully a good database tool like DataGrip will yell at you if you try to do a modifying operati…

This terrifies me as well. The workaround is to write the WHERE clause before the SET clause. If you inadvertently submit the query partway, it will be invalid and it's not a big deal.

I do this too
Post reply on HN