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

51–60 of 137 posts

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

#51

Post by kreetx »

PaulHoule wrote: post #43
kreetx wrote: post #34

Earlier quoted context omitted.

Absolutely! I'm just generally saying that SQL could be more succinct and composable - it's all a combination of project, filter and other primitive operations from relational algebra. SQL is rather verbose, and the question of whether FROM or SELECT should come first is just paint on the object.

Lately I've been developing a system which represents something like a file tree in SQL tables and using https://www.postgresql.org/docs/current/queries-with.html#QU... and boy is it an awkward syntax. Circa 2008 I was getting interested in the "semantic web" and wasn't so happy with RDFS and OWL and thought Datalog would be a useful approach and it was an obscure topic then. 10 years later people struggling w/ SQL a…

I've been on the lookout for new languages as well (though haven't created anything myself - unlike you).

I'd say, getting the data (1) and triggers/procedures (2) are totally different domains with regarding to syntax. For select (i.e, 1), I have my ideas, but for (2) I've no clue. For (2), now that I think about it, I would say there are two additional levels of syntax that need to be solved: first, in addition to "getting data" you need also to modify it, so there need be syntax for that part (i.e the UPDATE part of SQL); and second, how to you connect these modifiers to events that happen, this is yet another domain of syntax IMO. (This latter feels like a general purpose programming language already, so maybe build it in to any of them which have great syntax already?)

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

#52

Post by jacques_chester »

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 alternatives for autocompletion. That is an annoyance.

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

#53

Post by ben7799 »

lupire wrote: post #5

"The syntax is meant to resemble English", obviously. The link to https://www.lib.umn.edu/collections/special?id=291 is dead, though?

This is the obvious answer assuming the designer of SQL was a native english speaker.

Most of these arguments that it should work some way logical to the DB make too many assumptions about how the internals of the database work and don't think about how the query optimizer/planner might work very differently than the way a query is organized.

SQL is really old now, 50+ years, assumptions about how it worked or work are probably not that relevant across its entire history.

I first learned it almost 30 years ago now, it was definitely taught back in the day that SQL was one of those odd languages that was designed assuming you "wouldn't need an engineer" to write it. Laughable but would explain why engineers might not find the syntax logical.

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

#54

Post by vincnetas »

There is a hammer. Some people are skilled and proficient when using the hammer, some new people sometimes hit their fingers with the hammer. Do not try to replace all hammers in the world with something better, make a better hammer and leave old hammer alone, then if it's good enough, proficient people will adopt it and use it and new people will save their fingers.

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

#55

Post by Izkata »

bigfudge wrote: post #15
doubled112 wrote: post #9

Earlier quoted context omitted.

"Select green apples from the refrigerator" vs "from the refrigerator, select green apples" The first is much more naturally spoken.

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

I propose Japanese:

Green apples, from the refrigerator, select.

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

#56

Post by Swizec »

lucumo wrote: post #50
Swizec wrote: post #45

Earlier quoted context omitted.

> 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?

Slightly weird, though grammatically acceptably, as far as I know. But the debate is actually coming from something else your example shows nicely. When you select something from a drawer, you select entities. Usually when we select from a relational table, we select properties. In SQL, the drawer is not a thing. A different way to keep the English-style would be to add a BUT-JUST-[THEIR] clause. SELECT FROM socks WH…

> Usually when we select from a relational table, we select properties. In SQL, the drawer is not a thing

Ah but that’s by convention! If we want to be super pedantic, they’re all just relations. The drawer table describes a relation between values. And SELECT just defines a new relation! You’re creating an ad-hoc “table”.

That’s why “SELECT socks FROM (SELECT socks, pants FROM drawer)” works just fine.

Ok yes my brain has definitely been broken by years of SQL. I’m not the right person to understand newbies anymore.

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

#57

Post by refset »

On page 4 of "A Critique of Modern SQL And A Proposal Towards A Simple and Expressive Query Language" [0] (recently published at CIDR 2024) there's a great diagram covering this and many other "semantical ordering" confusions with SQL. It's an interesting paper in general, by a couple of the biggest names in modern database research (...though admittedly perhaps not language design).

Something like 'SaneQL' (which the paper introduces) deserves to succeed outside of the lab. Source is here [1].

[0] https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf

[1] https://github.com/neumannt/saneql/

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

#58

Post by efitz »

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.

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

#59

Post by randomdata »

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…

The godfather of relational calculus languages, Alpha, put the relation context first, and then allowed you to send multiple commands that operate within that context. SQL eventually gave in and added WITH to soft of, kind of, try to get to the same place.

But, ultimately, SQL was designed around the idea discrete function calls rather than unit operations (with continued efforts to try and hack on the latter after the fact), so in that sense, the operator going first does make more sense.

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

#60

Post by taeric »

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 column names everywhere, the search space for what tables to suggest will be helped by knowing what columns you are looking for.

Post reply on HN