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

21–30 of 137 posts

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

#21

Post by em500 »

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

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

#22

Post by marcosdumay »

x2bool wrote: post #12

Yes, it should. Before you know what you can SELECT you should know FROM where does the data come. Except maybe cases where there is no data source: SELECT 1; or SELECT NOW();

> Except maybe cases where there is no data source: SELECT 1; or SELECT NOW();

I don't think the standard SQL allows those. Even though I do personally prefer this form to the standard one.

Anyway, if we are serious about maintaining SQLness, it would be something like:

SELECT FROM people COLUMNS id, name

And that reduced form would become SELECT COLUMNS 1

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

#23

Post by vaibhavkul »

nemacol wrote: post #7

Does not seem weird to me that you would start with the type of query you are writing. Select, insert, update, delete, merge is the first word and says what you are doing. If the keyword was in the middle of the query somewhere it would be harder to read.

You have a good point, but the problem that not knowing the table to select beforehand does seem valid. What if the FROM clause is called SELECT and the SELECT clause is called PROJECT? (So it will read something like SELECT PROJECT ?)

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

#25

Post by em500 »

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.

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

#26

Post by snarfy »

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.

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

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

#27

Post by Someone »

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…

SQL could also have done what they wanted, for example allowing both

  FROM foo
  WHERE baz = quux
  SELECT bar
and even (the equivalent linq wont compile because ‘baz’ isn’t there anymore when the ‘where’ runs)

  FROM foo
  SELECT bar
  WHERE baz = quux
However, SQL predates smart code completion being an expected language feature, so they went for the feature “looks like normal American English” (“from the kitchen, can you get me the scales?” is less common than “can you get me the scales from the kitchen?” or “can you get me the scales? They’re in the kitchen”) instead of “make a grammar where smart code completion works well”.

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

#28

Post by Fradow »

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.

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

#29

Post by systems »

well, FROM should not be needed unless you are aliasing table names So ideally it would be

   SELECT Employee.Name, Address.Street 
   WHERE ...
   GROUP BY
   HAVING
   ORDER BY
or

   FROM Employee AS EMP , Address as A
   SELECT EMP.Name, A.Street
   WHERE ...
   GROUP BY
   HAVING
   ORDER BY
We should always use fully qualified names is the select And optionally ommit them in further down clauses such as WHERE or ORDER BY if the names are unambigious
Post reply on HN