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

31–40 of 137 posts

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

#31

Post by mikece »

I have always appreciated how Linq's syntax sets the data context first, then the filtering/grouping, and FINALLY selecting what columns should be in the result set. Not only does this make IDE hinting much easier but it's also more logical: "From the set of all left-handed NBA players who are from Europe and shorter than 201 cm, give me their month of birth."

I am surprised that SQL Server hasn't offered Linq syntax as an option for writing queries and stored procedures; could always start such queries with `Using Linq:` prefix so the query engine knows it's not using T-SQL...

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

#32

Post by kuriho »

SAP ABAP's OpenSQL allowes you to write both:

     SELECT FROM scarr
       FIELDS carrid, carrname
       ORDER BY carrid
       INTO TABLE @DATA(result1).
and

     SELECT carrid, carrname
       FROM scarr
       ORDER BY carrid
       INTO TABLE @DATA(result2).
https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/a...

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

#34

Post by kreetx »

PaulHoule wrote: post #11
kreetx wrote: post #6

Earlier quoted context omitted.

How about `table.attribute` entirely, instead of `SELECT attribute FROM table`?

You need some way to put an alias on tables so you can join a table more than once though I guess that can be optional, say SELECT table.attribute or FROM table SELECT attribute or FROM table as a,table as b WHERE a.x=b.y SELECT a.z

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.

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

#35

Post by happymellon »

em500 wrote: post #25
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?

What SQL shares with English (and most other natural languages) is that the rules are sometimes confusing/inconsistent/illogical, but evolution is very slow so most of us are probably stuck with the status quo during our lifetimes (career time for SQL) due to the overwhelming network effects.

The problem with English is that it isn't really a single language but a mashup of Latin/French/German/Scandinavian due to the number of invasions and each invader not really covering the whole country.

A simple way to demonstrate this is how we ended up with cities that have massively different pronunciation even though the spelling is similar.

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

#36

Post by b86754 »

Grazester wrote: post #16

I would like coffee from Starbucks. I don't say, from Starbucks I would like coffee

Right but that’s not how SQl executes unfortunately. You go to Starbucks then ask for what you need. SQL does a Cartesian product of all the tables you need, then filters with the ON clause. Only then can you “order” what you want in SELECT.

In standard SQL syntax you’re essentially asking for what you want at your house then driving to a Starbucks.

More readable but doesn’t follow the execution order at all.

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

#37

Post by Apreche »

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 difficult for the parser also. Even hairier when it comes to subqueries.

I do however, strongly agree that the most common scenario for user workflow is to choose tables first, then choose columns from those tables. I don't know if changing the language is really the answer. Intelligent tooling can and does already solve this. What I typically do is start all my queries by selecting *, then I go back and fill in the columns last.

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

#38

Post by dave4420 »

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…

Although WITH clauses come first.

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

#39

Post by gigatexal »

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…

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

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

#40

Post by randomdata »

doubled112 wrote: post #9
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?

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

Until you encounter a more complex example. Now it is "Select green apples from the refrigerator, insert more milk into the refrigerator, and remove any expired items from the refrigerator" which is less natural.

In contrast, the father of SQL, Alpha, allowed: "From the refrigerator, select the green apples, insert more milk, and remove any expired items."

Post reply on HN