Hacker News Reader

Comment by Apreche | Hacker News Reader

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.

View on HN

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.

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.

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

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.

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.

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

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.

Why is a very smart SQL IDE not able to work around that limitation with smart UI/UX? I can see why baby's first SQL IDE would benefit from the language being amenable to IDEs, but a very smart one should be able to take things further, no?

Alright, I start typing out "SELECT" in my super smart IDE, how does it know which table to suggest columns from?

Given that it is a very smart IDE, the column suggestion UI integrates table selection in a smart way. Once the IDE knows which table(s) you are referencing, it automatically fills in the FROM clause for you.

Looks like my IDE isn't that smart. So I will have to endure the unfriendliness of the SQL towards humans.

Or just use a better language. SQL was designed for non-technical people, using statements that resemble the English they already know. It was not designed for tech-minded people who are able to grasp higher level technical concepts.

I usually write "SELECT FROM a" and then "Ctrl-Left-Left" to go back to columns. Starting from FROM would be better of course.

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

    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.

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?

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.

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.

Although WITH clauses come first.

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.

  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.

    FROM ℝ SELECT 1

? :-)

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)

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.

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!

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?

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 :-)

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

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.

Trouble is that it falls apart when you have more than one operation. "select socks and pants from drawer, and delete money from drawer" is the weird sentence structure. Whereas if 'from' comes first: "from drawer, select socks and pants, and delete money". Which, granted, is still weird, but less so, and fixable with better word choice. Perhaps: "open drawer, select socks and pants, and remove money.

Codd's original vision would have seen that expressed as something like:

    RANGE DRAWER D
    GET W (D#,SOCKS,PANTS)
    DELETE D:(D.MONEY=1)

The only thing really missing in that from an English sentence structure perspective is generally we want just one more article in it and maybe a comma: from the drawer, select socks and pants.

But similarly I've written a lot of C# LINQ statements at this point and its from-first pattern seems to flow naturally enough that I don't see anything wrong with it (and a lot right, C# definitely has a great autocomplete experience under LINQ).

Both seem equivalent to me. And given how SQL is executed I like the latter approach tbh.

But generally SQL is often very nicely translatable to English at least for me and so long as the SQL isn’t too overly complex.

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

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

Reply on news.ycombinator.com