7 ms·

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

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.

hot_gril 3y ago
Your query can also start with "WITH", which isn't a command.
- deleted 3y ago
  [deleted]
dave4420 3y ago
Although WITH clauses come first.
gigatexal 3y ago
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
- Swizec 3y ago
  > 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?
  - lucumo 3y ago
    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 WHERE size = 45 BUT JUST [THEIR] color All fairly tongue in cheek of course. SQL isn't going to change, so there's really no point in worrying too much about it :-)
    - Swizec 3y ago
      > 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.
    - KittenPassingBy 3y ago
      Things are records / rows. Collections of the same things are entities (rubber band/ folders). Groups of entities are tables (Drawers). A star schema usually contains one table to one entity mapping. Dynamic tables for custom field on the other hand are usually multi entity tables. In this case, it would be a large junk drawer with various unrelated folders stuffed inside with everything from report cards, to keys to toys, to take out menus. It would have the toys entities, take out menu entities, keys entities, etc inside a mixed table.
jacques_chester 3y ago
> 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.
- WorldMaker 3y ago
  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.
  - jacques_chester 3y ago
    Even neater. DuckDB has an implicit "SELECT *" if you just give it "FROM tablename", which fits your example.
    - chrisjc 3y ago
      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?
      - WorldMaker 3y ago
        From my experience of Oracle `FROM DUAL;` projection would give you on a coin flip either an empty table or a stacktrace error from some Java library.
randomdata 3y ago
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.
gpderetta 3y ago
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.
cryptonector 3y ago
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.
willvarfar 3y ago
An SQL parser has no problem supporting both forms as there is no parsing ambiguity. (SQL is full of corner cases e.g. EXTRACT(WEEKDAY FROM field) etc. Putting the FROM first, or supporting multiple chained WHERE clauses or allowing WHERE before JOIN and applying to the preceding projection etc is all possible in an SQL parser that chooses to allow some relaxations. Personally, I am really irritated that I can't have HAVING without GROUP or QUALIFY without window functions etc, as I often construct queries programmatically.)
- deepsun 3y ago
  Nitpick -- SQL was not designed to be used programmatically. Same as shell commands, it's for humans to write manually. Proper API could have way more concise, and much more efficient format to parse. And would avoid a bunch of security issues along the way, same as for shell.
  - rangerelf 3y ago
    Exactly. I still remember about this "revolutionary" querying language being designed, that would allow for non-technical people to perform complex queries and reports from databases. People: stop trying to mangle SQL "because it would be better..."; nah, SQL is supposed to be the "better" already. The idiosyncrasies it has is because it was designed to be kinda-sorta conversational, modeled as an english language query.
    - randomdata 3y ago
      > SQL is supposed to be the "better" already. For non-technical people. It is not supposed to be the "better" for the technical people who end up using SQL in practice. QUEL was the "better" for us, being much closer to Codd's vision. But, alas, Oracle won with the business people and Postgres lost.
  - The_Colonel 3y ago
    This specific issue is a problem for humans, not for APIs. I have a very smart SQL IDE with great intellisense, but when I type "SELECT", it can't help me because it has no idea what I want. Being able to type "FROM table SELECT" would be way friendlier for humans, because IDEs could offer me immediately columns I'm very likely interested in.
mwigdahl 3y ago
You could pretty easily achieve this with: SELECT [FROM <from-expr> COLUMNS] <column-expr> "SELECT 1" would still be valid, and you'd still have the commands first, but you could also get the benefits of IDE autocomplete for columns by specifying the table before the columns. A little wordier though.
rhplus 3y ago
FROM ℝ SELECT 1 ? :-)
- hn92726819 3y ago
  I wonder if there is a concept of infinite length SQL tables. Not sure what would happen with: ORDER DESC COUNT(*) I can't find anything after a quick search except recursive views that must terminate (therefore aren't infinite)
  - jasomill 3y ago
    COUNT is easy: > SELECT COUNT(*) FROM ℤ; ℵ₀ > SELECT COUNT(*) FROM ℝ; POWER(2,ℵ₀) > EXEC sp_configure 'continuum hypothesis','1'; > RECONFIGURE; > SELECT COUNT(*) FROM ℝ; ℵ₁ While non-termination is probably best left implementation-defined (to allow caller-terminated streams of infinite results where they make sense), ORDER BY would clearly be an error where no well-defined "first" or "next" result exists.
    - wlindley 3y ago
      How fun it would be to receive a Cantor result in which as many elements from the original set as there were have been removed, leaving a set drawn from what you started with which still contains as many elements!
karmakaze 3y ago
The poster's problem is not recognizing that SQL is not procedural. The SQL query is declarative, though some databases are more literal in turning these into query plans than others.

hot_gril 3y ago

Your query can also start with "WITH", which isn't a command.

deleted 3y ago
[deleted]

dave4420 3y ago

Although WITH clauses come first.

gigatexal 3y ago

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

Swizec 3y ago
> 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?
- lucumo 3y ago
  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 WHERE size = 45 BUT JUST [THEIR] color All fairly tongue in cheek of course. SQL isn't going to change, so there's really no point in worrying too much about it :-)
  - Swizec 3y ago
    > 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.
  - KittenPassingBy 3y ago
    Things are records / rows. Collections of the same things are entities (rubber band/ folders). Groups of entities are tables (Drawers). A star schema usually contains one table to one entity mapping. Dynamic tables for custom field on the other hand are usually multi entity tables. In this case, it would be a large junk drawer with various unrelated folders stuffed inside with everything from report cards, to keys to toys, to take out menus. It would have the toys entities, take out menu entities, keys entities, etc inside a mixed table.

jacques_chester 3y ago

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

WorldMaker 3y ago
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.
- jacques_chester 3y ago
  Even neater. DuckDB has an implicit "SELECT *" if you just give it "FROM tablename", which fits your example.
  - chrisjc 3y ago
    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?
    - WorldMaker 3y ago
      From my experience of Oracle `FROM DUAL;` projection would give you on a coin flip either an empty table or a stacktrace error from some Java library.

randomdata 3y ago

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.

gpderetta 3y ago

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.

cryptonector 3y ago

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.

willvarfar 3y ago

An SQL parser has no problem supporting both forms as there is no parsing ambiguity. (SQL is full of corner cases e.g. EXTRACT(WEEKDAY FROM field) etc. Putting the FROM first, or supporting multiple chained WHERE clauses or allowing WHERE before JOIN and applying to the preceding projection etc is all possible in an SQL parser that chooses to allow some relaxations. Personally, I am really irritated that I can't have HAVING without GROUP or QUALIFY without window functions etc, as I often construct queries programmatically.)

deepsun 3y ago
Nitpick -- SQL was not designed to be used programmatically. Same as shell commands, it's for humans to write manually. Proper API could have way more concise, and much more efficient format to parse. And would avoid a bunch of security issues along the way, same as for shell.
- rangerelf 3y ago
  Exactly. I still remember about this "revolutionary" querying language being designed, that would allow for non-technical people to perform complex queries and reports from databases. People: stop trying to mangle SQL "because it would be better..."; nah, SQL is supposed to be the "better" already. The idiosyncrasies it has is because it was designed to be kinda-sorta conversational, modeled as an english language query.
  - randomdata 3y ago
    > SQL is supposed to be the "better" already. For non-technical people. It is not supposed to be the "better" for the technical people who end up using SQL in practice. QUEL was the "better" for us, being much closer to Codd's vision. But, alas, Oracle won with the business people and Postgres lost.
- The_Colonel 3y ago
  This specific issue is a problem for humans, not for APIs. I have a very smart SQL IDE with great intellisense, but when I type "SELECT", it can't help me because it has no idea what I want. Being able to type "FROM table SELECT" would be way friendlier for humans, because IDEs could offer me immediately columns I'm very likely interested in.

mwigdahl 3y ago

You could pretty easily achieve this with: SELECT [FROM <from-expr> COLUMNS] <column-expr> "SELECT 1" would still be valid, and you'd still have the commands first, but you could also get the benefits of IDE autocomplete for columns by specifying the table before the columns. A little wordier though.

rhplus 3y ago

FROM ℝ SELECT 1 ? :-)

hn92726819 3y ago
I wonder if there is a concept of infinite length SQL tables. Not sure what would happen with: ORDER DESC COUNT(*) I can't find anything after a quick search except recursive views that must terminate (therefore aren't infinite)
- jasomill 3y ago
  COUNT is easy: > SELECT COUNT(*) FROM ℤ; ℵ₀ > SELECT COUNT(*) FROM ℝ; POWER(2,ℵ₀) > EXEC sp_configure 'continuum hypothesis','1'; > RECONFIGURE; > SELECT COUNT(*) FROM ℝ; ℵ₁ While non-termination is probably best left implementation-defined (to allow caller-terminated streams of infinite results where they make sense), ORDER BY would clearly be an error where no well-defined "first" or "next" result exists.
  - wlindley 3y ago
    How fun it would be to receive a Cantor result in which as many elements from the original set as there were have been removed, leaving a set drawn from what you started with which still contains as many elements!

karmakaze 3y ago

The poster's problem is not recognizing that SQL is not procedural. The SQL query is declarative, though some databases are more literal in turning these into query plans than others.