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

91–100 of 137 posts

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

#91

Post by chrisjc »

jacques_chester wrote: post #83
WorldMaker wrote: post #65

Earlier quoted context omitted.

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.

Even neater. DuckDB has an implicit "SELECT *" if you just give it "FROM tablename", which fits your example.

But interestingly DuckDB doesn't support `SELECT 'FOO' AS BAR FROM DUAL;` or `SELECT 'FOO' AS BAR FROM 1;`.

Which I guess makes sense if it has an implicit `SELECT *`.

What would the implicit projection of `FROM DUAL;` be?

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

#92

Post by silvestrov »

derhuerst wrote: post #18

PRQL [1] is a compile-to-SQL relational querying language that puts FROM first. [1] https://prql-lang.org

It would be really nice if PostgreSQL and sqlite implemented this natively as it really feels how SQL should be (re)designed today.

It also makes writing complex queries easier as it would be possible to split out actions in multiple steps where you can see what columns and types you have created (and found) so far.

Complex SQL is like complex C++: works if you get it right, but no help whatsoever for figuring out what mistake you made when messing up. Step-by-step is such a great help.

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

#93

Post by fiddlerwoaroof »

dylan604 wrote: post #75
RodgerTheGreat wrote: post #61

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 o…

WTF is right-to-left evaluation's purpose? It breaks the most fundamental rules of arithmetic. As a human that has been taught those rules, I'd never come up with 9 as an answer. Why did humans create a programming language that would?

I remember having this question when learning J, and they pointed out that it makes the = operator behave correctly. After:

    a = 1 + 2
One expects `a` to equal 3. If you have a uniform left to right evaluation order, it will equal 1 and then the whole expression will be set to 3, so you’d need to write this to get the intuitive result:

    a = (1 + 2)

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

#94

Post by setr »

kivle wrote: post #88
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…

I can't remember where I read it, but I believe one of the main reasons why Linq moved the FROM clause to the front was for better intellisense. If you start writing SELECT x in an IDE, there really is no way of providing intellisense for x. However if you write FROM table SELECT x, the IDE can first provide intellisense for table names while you write your FROM clause. Then it can provide intellisense for the SELECT…

I believe LINQ maps to a chains of function calls on an enumerable, with the functions usually taking anonymous functions as input (and then the series of functions is rewritten into a SQL statement by EF to hit the DB). I don’t think you’d be able to support type analysis if you didn’t specify the enumerable upfront

Eg

Mylist.select(x => …) can determine the type of x, because it has the type of mylist —> List

Select(x => …).from(mylist) and you simply can’t determine x, because C# type analysis can’t go backwards.

So I think it’s less a UX question and more of an absolute requirement for the API to work at all. The alternative would be a SelectFrom(x => …, mylist) function so it can be submitted in one shot, essentially what SQL is doing, but that’s disgusting — who doesn’t love function chaining?

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

#95

Post by fiddlerwoaroof »

RodgerTheGreat wrote: post #78
dylan604 wrote: post #75

Earlier quoted context omitted.

WTF is right-to-left evaluation's purpose? It breaks the most fundamental rules of arithmetic. As a human that has been taught those rules, I'd never come up with 9 as an answer. Why did humans create a programming language that would?

Traditional mathematical notation has precedence rules optimized for expressing polynomials. Programming languages- especially in the APL family- tend to have a richer collection of primitive verbs, and an "ideal" tower of binding precedence for all of them would be extremely complex, difficult to remember, and often unhelpful. Unform evaluation order is much easier to remember and extend (no special cases!) and beco…

IMO, infix operators in math are a historical relic of function notation being developed after the basic operators became widespread. I’ve always thought uniform prefix notation is ideal, whether it’s lisp-style (+ 1 x y) or the more common +(1, x, y)

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

#96

Post by taeric »

dvlsg wrote: post #86
taeric wrote: post #60

I still largely reject the code completion complaint. Specifically, it is very common to know what columns you want before you know what tables have said columns. This is especially true on normalized schemas where you are almost certainly having to do some joins to get all that you want. So, does it somewhat complicate the logic? Of course. It is by no means impossible, though, and unless you are using super generic…

I'm not sure. I think it's fairly common for something like "Now was the column name on this table description_primary or primary_description? I can't remember... Oh well, I'll just SELECT * and figure it out later" to happen. Starting with FROM would at least eliminate that backtracking.

I can't say that you are wrong. I still find it hard to take as a complete argument. For one, you can almost certainly autocomplete all column names with an indicator for the table they come from. For two, you should be far more consistent in how you prefix things like that. :)

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

#97

Post by chrisjc »

nivertech wrote: post #10

Also SELECT should be the last clause (at least for row-oriented DBMS, for columnar databases it might come before WHERE, GROUP BY, ORDER BY to hint which columns we want to select to query on). Row-oriented: FROM table_name WHERE condition GROUP BY ... HAVING ... ORDER BY ... SELECT column1, column2, ...; Columnar: FROM table_name SELECT column1, column2, ... ORDER BY ... WHERE condition GROUP BY ... HAVING ...;

I'm interested to understand why you think it should differ between OLAP and OLTP?

Are you suggesting that columns closer to the top matters for OLAP bc OLAP is columnar? Ultimately, a query planner is going to figure out what happens first, so I can't imagine your point has anything to do with execution.

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

#98

Post by [deleted] »

em500 wrote: post #21
nivertech wrote: post #10

Also SELECT should be the last clause (at least for row-oriented DBMS, for columnar databases it might come before WHERE, GROUP BY, ORDER BY to hint which columns we want to select to query on). Row-oriented: FROM table_name WHERE condition GROUP BY ... HAVING ... ORDER BY ... SELECT column1, column2, ...; Columnar: FROM table_name SELECT column1, column2, ... ORDER BY ... WHERE condition GROUP BY ... HAVING ...;

No, it should come after WHERE, GROUP BY but before ORDER BY. From my favorite SQL tutorial[1]: The lexical ordering is: SELECT FROM WHERE GROUP BY HAVING UNION ORDER BY while the logical order is: FROM WHERE GROUP BY HAVING SELECT UNION ORDER BY [1] https://blog.jooq.org/10-easy-steps-to-a-complete-understand...

[deleted]

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

#99

Post by nivertech »

nivertech wrote: post #10

Also SELECT should be the last clause (at least for row-oriented DBMS, for columnar databases it might come before WHERE, GROUP BY, ORDER BY to hint which columns we want to select to query on). Row-oriented: FROM table_name WHERE condition GROUP BY ... HAVING ... ORDER BY ... SELECT column1, column2, ...; Columnar: FROM table_name SELECT column1, column2, ... ORDER BY ... WHERE condition GROUP BY ... HAVING ...;

Relevant infographics, probably inspired by this HN post:

https://x.com/alexxubyte/status/1750560163101315463?s=20

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

#100

Post by myk9001 »

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?

> If you started saying “from drawer select socks and pants”, wouldn’t that feel like a weird sentence structure to most people?

To a SQL Jedi order matters not.

Post reply on HN